What's the difference between PHP time and SQL time?

Why is the timestamp generated by the PHP time() function 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 t开发者_如何学编程his 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 I have created. I think one is tracked in microseconds vs the other is in seconds, but I can't find any documentation on this! What would be the right conversion between these two?

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

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

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().

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.