DATETIME or a
TIMESTAMP can be hard to understand for a beginner MySQL programmer. Let's try to figure out, what command and where should be used.
TIMESTAMP types are related. MySQL recognizes
TIMESTAMP values in several formats.
DATETIME type is used for values that contain both date and time parts. 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
TIMESTAMP data type is used for values that contain both date and time parts.
TIMESTAMP has a range of
'1970-01-01 00:00:01' UTC to
TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a
TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is
'YYYY-MM-DD HH:MM:SS[.fraction]', the range for
DATETIME values is
'1000-01-01 00:00:00.000000' to
'9999-12-31 23:59:59.999999', and the range for
TIMESTAMP values is
'1970-01-01 00:00:01.000000' to
DATETIME data types offer automatic initialization and updating to the current date and time.
TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as
DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a
TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the
time_zone system variable.
TIMESTAMP values are converted to the “zero” value of the appropriate type (
TIMESTAMP in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a
If need to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp
("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.