Difference in TIMESTAMP VALUES

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Difference in TIMESTAMP VALUES

Post 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 ?
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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.
Last edited by Buddha443556 on Sun Aug 15, 2004 9:46 am, edited 2 times in total.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post 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.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post 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.
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

Try:

Code: Select all

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