What's the difference between PHP time and SQL time?
Why is the timestamp generated by the PHP function time()
so different from the SQL datetime?
If I do date('Y-m-d', time());
in PHP, it gives me the time, as it should. If I just take a part time()
and do:
$now = time();
//then execute this statement 'SELECT * FROM `reservation` WHERE created_at < $now'
I am not getting anything. But hey, so if the value $now
was 1273959833 and I requested
'SELECT * FROM `reservation` WHERE created_at < 127395983300000000'
then I see the records that I have created. I think one is tracked in microseconds and the other in seconds, but I can't find any documentation on this! What would be the correct conversion between the two?
a source to share
The function time()
does not return microseconds, so it should work if you are using the correct data type. But you have two different data types right now, INT
and a date field (maybe DATE
/ DATETIME
/ TIMESTAMP
). If you want to compare a date in a database with a timestamp as an integer, you can use something like:
SELECT * FROM Tbl WHERE UNIX_TIMESTAMP(date) < $timestamp;
a source to share
time()
gives a Unix timestamp (seconds elapsed from 01-01-1970). SQL wants to have formatted timestamps YYYY-mm-dd hh-ii-ss
that are 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()
.
a source to share
These are just two different ways of storing dates, each with their own advantages and disadvantages. You can use MySQL date field or just store unix timestamps in INT field. You can also use:
SELECT UNIX_TIMESTAMP (field_name) FROM ...
to return the date field as a Unix timestamp.
The MySQL date field is human readable and can store any date for the foreseeable future. However, it does not store time zone information, which can cause serious problems if not handled correctly. This issue has a Facebook issue.
Unix timestamps store time zone information (since it is defined as the number of seconds from 12:00 to January 1, 1970 UTC). Comparisons are faster on integers, and PHP time and time functions are designed to be used with Unix timestamps. However, Linux can only support dates from 1902 to 2038 and Windows from 1970 to 2038. MySQL and architecture in general will switch to 64-bit integers long before 2038, but if you need to store dates that are in the distant future, the past, Unix time is not for you.
a source to share