diff dates in mysql
Moderator: General Moderators
diff dates in mysql
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?
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
What do you get back from: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?
SELECT now() - date_field FROM somewhwre
Is it a usable value? (Does it even compile?)
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
In the query.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?
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
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
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 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.
-
TheBentinel.com
- Forum Contributor
- Posts: 282
- Joined: Wed Mar 10, 2004 1:52 pm
- Location: Columbus, Ohio
Tht's awfully close to .25 seconds per count. (The difference between those times is 1696 seconds, if my math is right.)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
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.