Page 1 of 1

diff dates in mysql

Posted: Fri Mar 12, 2004 11:26 am
by yaron
Hello all,

I need to build an sql query that selects the diff between the current time and the time in a field stored in one of the fields.

I tried using:

SELECT minute(now()) - minute(date_field) FROM somewhwre

but it only diffs the minutes and i need to diff the whole date.
e.g. if the current date is 2004-03-12 17:30:22
and the date stored is 2003-03-11 18:35:33
it should come up with the number of minutes that the now() date is bigger than the field date and not just the minutes field.

any ideas?

Re: diff dates in mysql

Posted: Fri Mar 12, 2004 11:35 am
by TheBentinel.com
yaron wrote:SELECT minute(now()) - minute(date_field) FROM somewhwre

but it only diffs the minutes and i need to diff the whole date.

any ideas?
What do you get back from:

SELECT now() - date_field FROM somewhwre

Is it a usable value? (Does it even compile?)

Posted: Fri Mar 12, 2004 11:48 am
by yaron
I get a number which is not minutes.

Posted: Fri Mar 12, 2004 11:56 am
by TheBentinel.com
yaron wrote:I get a number which is not minutes.
But does it convert to minutes? If it's seconds, or days, or something convert-able then you're set.

Posted: Fri Mar 12, 2004 11:58 am
by yaron
hmm... I have no idea what is the number i get.

By saying 'convert', what do u mean?
on the php code or in the mysql query?

Posted: Fri Mar 12, 2004 12:00 pm
by TheBentinel.com
yaron wrote:hmm... I have no idea what is the number i get.

By saying 'convert', what do u mean?
on the php code or in the mysql query?
In the query.

If the number is the number of seconds between the two dates, then you could convert that to minutes by dividing it by 60.

SELECT (now() - date_field) / 60 FROM somewhwre

Posted: Fri Mar 12, 2004 12:04 pm
by yaron
Tried that.
The number I get is not seconds as well (it's larger than the seconds diff).

I have no idea what is that number....
Tried to look at the mysql man but couldn't find the answer for that there.

Posted: Fri Mar 12, 2004 12:11 pm
by TheBentinel.com
yaron wrote:Tried that.
The number I get is not seconds as well (it's larger than the seconds diff).

I have no idea what is that number....
Tried to look at the mysql man but couldn't find the answer for that there.
How about this? Tell me what the database field contains, what now() is when you ran it, and what number it returns. The number has to mean something, and if it relates to time somehow, then we ought to be able to bend it around to what you need.

Posted: Fri Mar 12, 2004 12:14 pm
by yaron
ok here is an example.

current date was: 2004-03-12 18:14:13
stored date was: 2004-03-12 17:45:57
the number I got was:6856

Posted: Fri Mar 12, 2004 12:21 pm
by TheBentinel.com
yaron wrote:ok here is an example.

current date was: 2004-03-12 18:14:13
stored date was: 2004-03-12 17:45:57
the number I got was:6856
Tht's awfully close to .25 seconds per count. (The difference between those times is 1696 seconds, if my math is right.)

Using that, can you get reliable numbers? In other words, if you divide the number you get back by 240, do you get the number of minutes?

I might have my factors off here, since I gotta dash and I'm not double-checking, but it's worth looking into.

Posted: Fri Mar 12, 2004 12:23 pm
by yaron
I guess I can do that but I'm sure that there is a way to make a minutes diff using mysql.
I just have to find how :)

Posted: Fri Mar 12, 2004 12:53 pm
by yaron
got it (i think)

(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(date_field))/60

thank you for your help