Timestamp vs. Datetime , which is better and what to use?
I’d personally go with timestamp all the time, for its accuracy, power, functions and the automatic timezone conversion by MySQL when entering and reading its value. Everything server time. Datetime stores only the ‘date’ value, much like 1st January 2014 , which very much has no function or meaning but behave like TEXT, a statement.
How to convert MySQL time to UNIX timestamp using PHP?
$timestamp = strtotime($mysqltime); (where $mysqltime is the timestamp data read from mysql)
echo date("Y-m-d H:i:s", $timestamp); (then use this to display nicely in date format)
when reading from datebase .. and formatting to show .. either use $timestamp == UNIX_TIMESTAMP(datetime) , then in PHP.. date(‘Y-m-d H:i:s T’,$timestamp) *OR* use in PHP, $timestamp = strtotime(datetime) before date(‘Y-m-d H:i:s T’,$timestamp). Remember to compare with database value with the above date() format for accuracy.
the first timestamp field in a table, is automatically updated on a record change!
Something to consider though, DATETIME can store dates from 1000 AD to 9999 AD, while Timestamp, 1970 AD to 2038 AD only:
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.
…
The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.
Reference:
http://stackoverflow.com/questions/409286/datetime-vs-timestamp
