Page 1 of 1

Difference in TIMESTAMP VALUES

Posted: Sun Aug 15, 2004 1:43 am
by anjanesh
TIMESTAMP(14) :

Expiration is 20040814000751
NOW() is 20040815120624

NOW()-Expiration is giving 1119873 which is mathematically correct. But only one day has passed and 1119873/(60*60*24) gives 12.96.
Where am I going wrong ? The query part or the math part ?

Posted: Sun Aug 15, 2004 5:48 am
by Buddha443556
TIMESTAMPS are not stored in seconds.

You maybe interested in some of these functions:

Date and Time Functions

Perticularly FROM_UNIXTIME(unix_timestamp) and UNIX_TIMESTAMP(date) these two will help convert to/from Unix time and MySQL time formats.

Posted: Sun Aug 15, 2004 5:57 am
by Buddha443556
You may also want to use something other than TIMESTAMP for an expiry? TIMESTAMP's are automatically updated on INSERT or UPDATE operations.

Posted: Sun Aug 15, 2004 9:57 am
by anjanesh
DATEDIFF is available for only for ver 4.1 and up. My host provides 3.23. Regarding UPDATE I set the value to the prev value SET Expiration=Expiration. And most of the other fns are only from 4.1 and up.

Posted: Sun Aug 15, 2004 12:20 pm
by Buddha443556
Try:

Code: Select all

UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(Expiration)
That should give you the seconds you want.