diff dates in mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

diff dates in mysql

Post 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?
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Re: diff dates in mysql

Post 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?)
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

I get a number which is not minutes.
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Post 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.
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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?
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Post 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
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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.
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Post 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.
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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
TheBentinel.com
Forum Contributor
Posts: 282
Joined: Wed Mar 10, 2004 1:52 pm
Location: Columbus, Ohio

Post 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.
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post 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 :)
yaron
Forum Contributor
Posts: 157
Joined: Fri Aug 22, 2003 8:40 am

Post by yaron »

got it (i think)

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

thank you for your help
Post Reply