DATETIME was how many minutes ago?

[sir pannels]

Hello everyone,

Not sure if this is possible, if it is not I will attempt the task in PHP. However assume that if MySQL can do it, it be easier...

I have a DATETIME stamp in a mysql database and wish to find out how many minutes ago it was... looked across mysql.com for this but no luck.

Can anyone point me in the right direction?
Many thanks,

Sir P :D

[CFMaBiSmAd]

If you have mysql 5.0 or higher, you can use the TIMESTAMPDIFF() function with units set to "MINUTE" and one of the parameters set to NOW().

If you don't have that function available, I recommend using the mysql TIME_TO_SEC() function on your column and the NOW() value to give them both in seconds, then subtract the two, and divide by 60 to give minutes.

[sir pannels]

Thank you for your reply :)

Not sure how to tie these two together actually ...

TIME_TO_SEC(time)

Returns the time argument, converted to seconds.
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580

With NOW? -> Stumpped.

Reading dev.mysql.com but dont see how I would tie them together to give me the number of minutes ago a date was?

Cheers

[Fumigator]

You get the value you're after by subtracting TIME_TO_SEC(yourTimestamp) from TIME_TO_SEC(now()). That's the number of seconds between your timestamp and "right now". Multiply seconds by 60 to get minutes.

[sir pannels]

I see Fumigator, thanks for the reply..something is wrong with the maths though ...

SELECT TIME_TO_SEC(last) AS timethen,TIME_TO_SEC(now()) AS timenow FROM `time`

returns

timethen
32404
timenow
32470

doing the maths ...

32470 - 32404 = 66
* 60 = 3960

3960 minute since my last stamp? No, its like a couple of seconds :s Oh no Im confused :D

ha ha, any ideas?
cheers again

[CFMaBiSmAd]

He actually meant divide by 60 (think about it.) You have seconds and you want the number of minutes. How do you find the number of minutes given a number of seconds? seconds/60 = minutes

[sir pannels]

Ha ha yes of course, sorry about that.

Your assistance has been apperciated :)

[Fumigator]

Whoops :p

[sir pannels]

You assistance aswell,fumigator, thanks :)