MySQL, time() and usinx dates Unix Style

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

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

Sims <siminfrance@hotmail.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

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

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

Sims <siminfrance@hotmail.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_functions.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

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_functions.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

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

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

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.

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

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

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/Da...functions.html

Micha

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(unixtime) as ut,
from_unixtime(unixtime)-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
:)

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=ht...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.

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

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.

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

Sims wrote:
<much snippage>[color=blue]
> 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.[/color]

"date function" found it on the MySQL site for me... "date time unix" would
also have found it.
[color=blue]
>
> 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=ht...donations.html
> But what do I know really.[/color]

....nor did I say I was a web developer.

Did the information on the query I provided help you solve your issue?

I have been using PHP and MySQL for less than 6 months... My primary profession
is Systems (OpenVMS - preferred and Unix/Linux/NT if I have too..) and Database
(Oracle/RDBMS and OracleRdb) Admin.

MySQL is almost a database. I still would not bet my livelyhood or my business
on it. It is okay for a "hobbyist" type system or writing "proof-of-concept"
apps, but would choose to deploy it on a real database engine.
[color=blue]
>
> Thanks for all that replied. I will spend the next year or so reading the
> whole MySQL doc as wisely advised.
>[/color]

You're welcome.
[color=blue]
> Sims.[/color]

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

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

>[color=blue]
> "date function" found it on the MySQL site for me...[/color]

Yes, it found it along with 953 documents.
[color=blue]
> "date time unix" would[/color]

as well as 1403 docs.
[color=blue]
> also have found it.[/color]

I might be difficult but I hope you see my point.
[color=blue][color=green]
> > Last but not least, where did I say that my profession had anything to[/color][/color]
do[color=blue][color=green]
> > with MySQL? or PHP? or even with computers for that matter? sorry, is it[/color][/color]
a[color=blue][color=green]
> > 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/[/color][/color]
or[color=blue][color=green]
> > maybe
> >[/color][/color]
http://validator.w3.org/check?uri=ht...donations.html[color=blue][color=green]
> > But what do I know really.[/color]
>
> ...nor did I say I was a web developer.[/color]

grand, so we both agree that we can use this forum even if it is not our
profession.
[color=blue]
>
> Did the information on the query I provided help you solve your issue?[/color]

Are you referring to the SELECT statement? It was helpful but as you said
yourself I had already been pointed in the right direction.
If you are referring to the search string above I am afraid that +/-1000
results is not as helpful I would hope.
[color=blue]
> I have been using PHP and MySQL for less than 6 months... My primary[/color]
profession[color=blue]
> is Systems (OpenVMS - preferred and Unix/Linux/NT if I have too..) and[/color]
Database[color=blue]
> (Oracle/RDBMS and OracleRdb) Admin.
>
> MySQL is almost a database. I still would not bet my livelyhood or my[/color]
business[color=blue]
> on it. It is okay for a "hobbyist" type system or writing[/color]
"proof-of-concept"[color=blue]
> apps, but would choose to deploy it on a real database engine.[/color]

Ok, I'll take your word for it. I am not familiar enough with any databases
to compare, (hence the reason I ask questions here from time to time).

But I have worked with all the big ones, (<read> Oracle, Sybase etc..), and
MySQL does the same job as the others.
But seen that I am not betting my life or even my job on it I don't really
mind what db I have.

Sims.

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

Sims wrote:
[color=blue]
> 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...[/color]

Depends on what you mean by "mind". Naturally, I don't mind that you
felt free to express yourself. I disagree with your statement.
[color=blue]
> 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]

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

Email is about as private as you will get. Public postings are most
definitely not.
[color=blue]
> This is not what I wish to use this public forum for.[/color]

Irrelevant to how I want to use them.
[color=blue]
> So I will refrain from
> replying to your repeated attempts to ... what ever your secret mission is I
> guess.[/color]

I have no secret agenda. My agenda is quite obvious. I thought you
behaved like an ass towards the entire group and to one person in
particular. I thought your behavior was bad enough that you should
apologize.

Anything else I need to spell out for you?



Brian Rodenborn

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

> I have no secret agenda. My agenda is quite obvious. I thought you[color=blue]
> behaved like an ass towards the entire group and to one person in
> particular. I thought your behavior was bad enough that you should
> apologize.
>
> Anything else I need to spell out for you?[/color]

Insulted the group? behaved like an arse?
hush little girl you're getting a nose bleed, sorry I was nasty to your
special "friend".
[color=blue]
>
>
> Brian Rodenborn[/color]

Sims

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

It seems to be a bit offtop

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

Sims wrote:[color=blue]
>[color=green]
> > I have no secret agenda. My agenda is quite obvious. I thought you
> > behaved like an ass towards the entire group and to one person in
> > particular. I thought your behavior was bad enough that you should
> > apologize.
> >
> > Anything else I need to spell out for you?[/color]
>
> Insulted the group? behaved like an arse?[/color]

I said ass. As in donkey.
[color=blue]
> hush little girl you're getting a nose bleed, sorry I was nasty to your
> special "friend".[/color]


Awwww, he thinks he knows some insults. He thinks by calling me a girl
or implying I'm gay that I'll be offended. That seems to indicate that
he thinks either of those two types of person are somehow lesser beings.

As I don't, the "insults" ring pretty hollow. Do however, continue
demonstrating to the rest of the group that you aren't the sort of
person who deserved help.




Brian Rodenborn