CODEWITHSUNDEEP
phpmysqldatetime
JDelage
JDelage

Reputation: 13682

Extracting server time with PHP and storing in MySQL - Unix time stamp OK?

All,

I'm trying to decide how to deal with time in a project which relies on (server) time intervals (in short, some content is available after user completed a specific action at least n hours before). Right now, it seems like the easiest option would be to extract the Unix time stamp with time() and store it as is in MySQL.

Any reason why this is not a good idea? Any gotcha I need to be aware of? Performance impact?

Upvotes: 1

Views: 299

Answers (3)

David Bélanger
David Bélanger

Reputation: 7438

Actually, this is the best idea. The function time() give you the number of seconds from January 1th, 1970 00:00:00. There's no performance impact because it's only an integer. In MySQL, create a field like that INT, 10, Unsigned.

Time will give you performance on the SELECT and the WHERE. See http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/

The only problem you have is : time is limited to year 2038... but by the time 2038 come, the internal computer clock bytes will be larger ... hope so.

The other thing you may want to worrie about the DATETIME is : PHP time() run under UTC, while DATETIME depend on the timezone...

Stats when you do INSERT with 10000000 rows.

enter image description here

Stats when you SELECT / WHERE with indexes :

enter image description here

Upvotes: 1

Madara's Ghost
Madara's Ghost

Reputation: 174977

Seems fine to me. Though you should probably store it as a DATETIME and use DateTime objects, rather than UNIX timestamps and time().

$time = new DateTime;
echo $time->format("Y-m-d H:i:s"); //Outputs current time, example: 2012-10-13 22:58:34

Upvotes: 2

Levente Pánczél
Levente Pánczél

Reputation: 1923

Timestamps are fine. Don't divide them, it's unneeded calculation. If you plan to query (per object) about a timeout more often than update it then you would be better off storing the expiration time instead of the current (so calculating delta only once). Beware about DATETIME columns: they don't regard timezone setting, while your PHP does... so if you happen to have different timezone settings on different requests, then you're out of luck. Timestamps are absolute, and they also account for manace like daylight-savings times, where 3:01 is 2 minutes after 1:59...

Upvotes: 2

Related Questions