what's the difference between php time and sql time

Question

Can anyone tell me why the timestamp generated by the php time() function is so different from SQL datetime?

If i do a date('Y-m-d', time()); in php, it gives me the time now, as it should. If I just take the time() portion and do:

$now = time();
//then execute this statement 'SELECT * FROM `reservation` WHERE created_at < $now'

I get nothing. But hey, so if the value of $now was 1273959833 and I queried

  'SELECT * FROM `reservation` WHERE created_at < 127395983300000000'

Then I see the records that ive created. I think one is tracked in microseconds vs the other is in seconds, but I cant find any documentation on this! What would be the right conversion between these two?? any help appreciated.

Answer 1

The time() function doesn't return microseconds, so it should work if your using the correct datatype. But you're 2 different datatypes right now, INT and a date field (could be DATE/DATETIME/TIMESTAMP). If you wanna compare a date in the database to a timestamp as integer, you could use something like:

SELECT * FROM Tbl WHERE UNIX_TIMESTAMP(date) < $timestamp;

Answer 2

time() gives a unix-timestamp (seconds passed since 01-01-1970) - sql wants to have timestamps in format YYYY-mm-dd hh-ii-ss which is done by date() - so if you don't want to call 2 php-functions, just use $now = date("Y-m-d H:i:s") or, better, change your sql-query to created_at < NOW()

Answer 3

They're just 2 different ways of storing dates, each with their advantages and disadvantages. You can use MySQL's date field, or simply store unix timestamps in an INT field. You can also use:

SELECT UNIX_TIMESTAMP(field_name) FROM ...

to return a date field as a Unix timestamp.

The MySQL date field is human-readable and can store any date in the foreseeable future. However, it does not store timezone information which can cause serious issues if not handled correctly. Facebook had this problem a while back.

Unix timestamps store timezone information (since it's defined as the number of seconds since 12:00am January 1st 1970 UTC). Comparison operations are faster on integers, and PHP's time/date functions are designed to be used with Unix timestamps. However, Linux can only support dates from 1902 to 2038 and on Windows from 1970 to 2038. MySQL and architecture in general will switch to 64-bit integers long before 2038 arrives, but if you need to store dates that are in the distant future or past, Unix time isn't for you.