Often you get confused while going to build a schema for your application to use datetime or timestamp as a datatype in a table field. Here is the detail for ‘difference between timestamp and datetime in mysql’ in tabular format which will be easy to understand the differences.:
| DATETIME |
TIMESTAMP |
The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' |
TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. |
MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS'format. |
|
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' |
With the fractional part included, the format for these values is 'YYYY-MM-DD HH:MM:SS[.fraction]', the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999' |
| MySQL doesn’t converts DATETIME values, it stores it as you have provide, and gives back as you have provided. |
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval |
| Invalid values will be converted to 0 like ‘0000-00-00 00:00:00’ |
|
MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date.for example '0000-00-00 00:00:00' |
TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). |
If both (DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP) are present in a column definition, either can occur first. |
Any of the synonyms ( CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, and LOCALTIMESTAMP()) for CURRENT_TIMESTAMP have the same meaning as CURRENT_TIMESTAMP. |
|
|
