MySQL, time() and usinx dates Unix Style

[Sims]

Hi,

I have a field in my DB that saves the date/time as a integer.
I get the time on my server using the time() function.

now, moving away from php and looking at (My)SQL only.
If I use functions like DATE() or NOW(), I don't get a Unix value, (but
rather a formatted date).
So I cannot use those functions to check against my saved value.

How can I run a query that would get, say, 1 hour before the current time?

Many thanks in advance.

Simon

[Daniel Tryba]

Re: MySQL, time() and usinx dates Unix Style

Sims <siminfrance@ho tmail.com> wrote:[color=blue]
> now, moving away from php and looking at (My)SQL only.
> If I use functions like DATE() or NOW(), I don't get a Unix value, (but
> rather a formatted date).
> So I cannot use those functions to check against my saved value.
>
> How can I run a query that would get, say, 1 hour before the current time?[/color]

Mysql had a fine online manual... There is a section dedicated to
date/time functions, you might want to read it.

--

Daniel Tryba

[Sims]

Re: MySQL, time() and usinx dates Unix Style

[color=blue][color=green]
> > now, moving away from php and looking at (My)SQL only.
> > If I use functions like DATE() or NOW(), I don't get a Unix value, (but
> > rather a formatted date).
> > So I cannot use those functions to check against my saved value.
> >
> > How can I run a query that would get, say, 1 hour before the current[/color][/color]
time?[color=blue]
>
> Mysql had a fine online manual... There is a section dedicated to
> date/time functions, you might want to read it.
>[/color]

Lets start small first, shall we?

Re-read my post first, the problem is in the time format that I would like
to use and not the manual.
BTW, Mysqql still has a fine online manual.

Thanks,

Simon

[Daniel Tryba]

Re: MySQL, time() and usinx dates Unix Style

Sims <siminfrance@ho tmail.com> wrote:[color=blue][color=green]
>> Mysql had a fine online manual... There is a section dedicated to
>> date/time functions, you might want to read it.
>>[/color]
>
> Lets start small first, shall we?
>
> Re-read my post first, the problem is in the time format that I would like
> to use and not the manual.[/color]

So you didn't bother to take a look at the date/time functions for
mysql!

I'll do your homework for you by providing this link:

Section 13.5 Date and Time Functions
<http://dev.mysql.com/doc/mysql/en/Date_and_time_f unctions.html>

Search for 'unix' and you'll find a couple of nice functions to
translate mysql dates to what you think you need.

--

Daniel Tryba

[Sims]

Re: MySQL, time() and usinx dates Unix Style

>[color=blue]
> I'll do your homework for you by providing this link:
>
> Section 13.5 Date and Time Functions
> <http://dev.mysql.com/doc/mysql/en/Date_and_time_f unctions.html>
>
> Search for 'unix' and you'll find a couple of nice functions to
> translate mysql dates to what you think you need.
>[/color]

I did look at the manual, (http://dev.mysql.com/doc/mysql/en/datetime.html),
but maybe, (just maybe), I did not find that particular page.

I don't know what I did wrong to you but maybe helping others is not your
forte.
Don't bother replying, obviously you will throw some form of insult and I am
too old for those games, (or to do homework for that matter).

Thanks for the help.

Simon

[Michael Fesser]

Re: MySQL, time() and usinx dates Unix Style

.oO(Sims)
[color=blue]
>I have a field in my DB that saves the date/time as a integer.[/color]

You should use the more reliable DATE or DATETIME instead. These are
native types and store dates in ISO format instead of Unix timestamps,
which allows

* to store much more dates (Unix timestamps are restricted in range)
* to use internal MySQL functions to work with these dates

Micha

[Default User]

Re: MySQL, time() and usinx dates Unix Style

Sims wrote:
[color=blue]
> I don't know what I did wrong to you but maybe helping others is not your
> forte.[/color]


What you did was fail to RTFM even after being told that your solution
was in it, and what section to look in.

That demonstrates laziness and a desire for others to do your work for
you. That doesn't generally rally the troops to help you out.

You should apologize to Daniel.



Brian Rodenborn

[Sims]

Re: MySQL, time() and usinx dates Unix Style

[color=blue]
>
>
> What you did was fail to RTFM even after being told that your solution
> was in it, and what section to look in.[/color]

No, what I failed to do was to read the _whole_ manual. Sorry if I am
neither a MySQL developer nor a DBA in general.
I was under the impression that the section I looked at was enough.

Re-read the thread, you seem to have misunderstood some/all of it so far.

The problem with search words like 'Date' 'Time' is that that they tend to
send you in many useless directions.
I could not find a answer in the doc so I asked here. Been told to read the
whole manual is a comical answer at best.
[color=blue]
>
> That demonstrates laziness and a desire for others to do your work for
> you. That doesn't generally rally the troops to help you out.
>
> You should apologize to Daniel.[/color]

Maybe I should, or maybe you are been a bit over-dramatic. But maybe you
should mind your own business.
He gave me a reply that I thought was not very useful and borderline rude,
if he took offence to my reply he can tell me about it himself.

But as I said it has nothing to do with you.
[color=blue]
> Brian Rodenborn[/color]

Simon.

[Sims]

Re: MySQL, time() and usinx dates Unix Style

[color=blue]
>[color=green]
> >I have a field in my DB that saves the date/time as a integer.[/color]
>
> You should use the more reliable DATE or DATETIME instead. These are
> native types and store dates in ISO format instead of Unix timestamps,
> which allows
>
> * to store much more dates (Unix timestamps are restricted in range)
> * to use internal MySQL functions to work with these dates
>[/color]

Thanks, I'll have a look at it. I never really thought about it. I am used
to the C version of time and it's limitations.

When you say 'more reliable' are you talking about the restricted range of
the date or simply that it is less prone to errors in the database itself?

Sims

[Michael Fesser]

Re: MySQL, time() and usinx dates Unix Style

.oO(Sims)
[color=blue]
>When you say 'more reliable' are you talking about the restricted range of
>the date [...][/color]

Yep. Unix timestamps are restricted to 1970-2038 or something like that
on most systems. It may be enough in some cases, but the native date
types of MySQL also allow you to do all different kinds of calculations
and modifications directly in the db/query without having to convert
from/to timestamps.

OTOH Unix timestamps are useful from time to time for some calculations
as well, but you have to take care of some issues like daylight savings
for example.

Micha

[Michael Fesser]

Re: MySQL, time() and usinx dates Unix Style

.oO(Sims)
[color=blue]
>Thanks, I'll have a look at it. I never really thought about it. I am used
>to the C version of time and it's limitations.[/color]

In your case have a look at DATE_ADD()/DATE_SUB() and the INTERVAL
keyword.

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

Micha

[Michael Austin]

Re: MySQL, time() and usinx dates Unix Style

Michael Fesser wrote:
[color=blue]
> .oO(Sims)
>
>[color=green]
>>Thanks, I'll have a look at it. I never really thought about it. I am used
>>to the C version of time and it's limitations.[/color]
>
>
> In your case have a look at DATE_ADD()/DATE_SUB() and the INTERVAL
> keyword.
>
> http://dev.mysql.com/doc/mysql/en/Da...functions.html
>
> Micha[/color]

I guess one of the things newbies have to learn is how to use search engines
within a particular site or WWW wide. Daniel correctly pointed him to this
page, but apparently did not grasp the concept. So, here is a real life example
of the MANY ways to point the OP in the right direction.

mysql> select unixtime,
from_unixtime(u nixtime) as ut,
from_unixtime(u nixtime)-interval 1 hour as nt
from mytable;
+------------+---------------------+---------------------+
| unixtime | ut | nt |
+------------+---------------------+---------------------+
| 1090722138 | 2004-07-24 21:22:18 | 2004-07-24 20:22:18 |
| 1090715410 | 2004-07-24 19:30:10 | 2004-07-24 18:30:10 |
.....
+------------+---------------------+---------------------+

All of us are willing to help, but if someone has extreme difficulty in coming
up with a solution, even after being given a pointer to the appropriate docs, he
should begin to question whether or not he is in the right profession.

--
Michael Austin.
Consultant - Not Available.
Donations still welcomed. Http://www.firstdbasource.com/donations.html
:)

[Sims]

Re: MySQL, time() and usinx dates Unix Style

> >>Thanks, I'll have a look at it. I never really thought about it. I am
used[color=blue][color=green][color=darkred]
> >>to the C version of time and it's limitations.[/color]
> >
> >
> > In your case have a look at DATE_ADD()/DATE_SUB() and the INTERVAL
> > keyword.
> >
> > http://dev.mysql.com/doc/mysql/en/Da...functions.html
> >
> > Micha[/color]
>
> I guess one of the things newbies have to learn is how to use search[/color]
engines[color=blue]
> within a particular site or WWW wide. Daniel correctly pointed him to[/color]
this[color=blue]
> page, but apparently did not grasp the concept. So, here is a real life[/color]
example[color=blue]
> of the MANY ways to point the OP in the right direction.
>[/color]

Thanks for your valuable input.

BTW, I am not a newbie. I might be, in your vast, un-swimmable, pool of
knowledge, but... a newbie in general I am not.
I am not sure what I did wrong here but I am enough not of a newbie to know
that I cannot get out of it. I am in one of those 'witch hunt threads'.
Sorry I asked.

But for the record, the link, (eventually), given by Daniel was not about
the reliability of UNIX timestamps vs. MySQL Dates raised by Michael
(Fesser).
I just replied to Michael (Fesser) because he had a good point that I never
really noticed. Sorry I did not read the manual on that point either.
I simply did not know that the 'OP'<me> should acknowledge, while replying
to a message, that a wise guru had pointed him in the right direction.

And as I said somewhere else in that thread, the words "Date()" and "Time()"
are not, IMH(newbie?)O, really easy search words to use. But of course, I am
certain you can provide us with some better search words that would prove me
wrong in my easy,(silly?), example.

Last but not least, where did I say that my profession had anything to do
with MySQL? or PHP? or even with computers for that matter? sorry, is it a
Guru only NG? If it is please accept my deepest apologies.

Talking about profession... I wonder...,
http://validator.w3.org/check?uri=ht...dbasource.com/ or
maybe

http://validator.w3.org/check?uri=http%3A%2F%2Fwww.firstdbasource.com/donations.html

But what do I know really.

Thanks for all that replied. I will spend the next year or so reading the
whole MySQL doc as wisely advised.

Sims.

[Default User]

Re: MySQL, time() and usinx dates Unix Style

Sims wrote:[color=blue]
>[color=green]
> >
> >
> > What you did was fail to RTFM even after being told that your solution
> > was in it, and what section to look in.[/color]
>
> No, what I failed to do was to read the _whole_ manual. Sorry if I am
> neither a MySQL developer nor a DBA in general.
> I was under the impression that the section I looked at was enough.[/color]

Why didn't you check the section he told you to?

"There is a section dedicated to date/time functions, you might want to
read it."

[color=blue]
> He gave me a reply that I thought was not very useful and borderline rude,
> if he took offence to my reply he can tell me about it himself.
>
> But as I said it has nothing to do with you.[/color]

This is a public forum, it's just as much my business when people behave
badly, as you did, as anyone else's. You have no say whether I do or do
not reply to your messages.

If you want to have private conversations with people, there's email.



Brian Rodenborn

[Sims]

Re: MySQL, time() and usinx dates Unix Style

> Why didn't you check the section he told you to?[color=blue]
>
> "There is a section dedicated to date/time functions, you might want to
> read it."[/color]

And further down the thread you can see something along the line of

"I did look at the manual, <link>, but maybe, (just maybe), I did not find
that particular page."

In the contexts it means a lot.
[color=blue][color=green]
> >
> > But as I said it has nothing to do with you.[/color]
>
> This is a public forum, it's just as much my business when people behave
> badly, as you did, as anyone else's. You have no say whether I do or do
> not reply to your messages.[/color]

And, if you do not mind, I will use this very public forum to tell you that
you do not know when a person should apologise neither do you know when
another should mind their own business or get involved.
But I somehow suspect you do mind...

And I could also try and explain how silly your defence "This is a public
forum..." followed by "You have no say whether I do..." sound but that would
be out of the scope of this thread, I think.
[color=blue]
>
> If you want to have private conversations with people, there's email.[/color]

No, there's face to face. Anything else is not private.
Google around for some very good psychological reports on that matter.
[color=blue]
>
> Brian Rodenborn[/color]

This is not what I wish to use this public forum for. So I will refrain from
replying to your repeated attempts to ... what ever your secret mission is I
guess.

Sims.