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.