The difference between Datetime and Timestamp in Mysql

The three types of date, datetime, and timestamp (if you count int, four types) used to represent time in mysql are relatively easy to confuse. Let’s compare the similarities and differences of these three types

Same points

Both can be used to represent time
Both are displayed as strings

Different points

1. As the name suggests, date only represents the date in the form of 'YYYY-MM-DD', datetime represents 'YYYY-MM-DD' Date plus time in the form of HH:mm:ss', timestamp is displayed in the same format as datetime.
2. The time range that date and datetime can represent is '1000-01-01' to '9999-12-31'. Timestamp can represent '1970-01-01 due to the limitation of the 32-bit int type. 00:00:01'to'2038-01-19 03:14:07' UTC time.
3. Mysql will convert the time to UTC time when storing the timestamp type, and then restore it to the current time zone when reading. If you store a timestamp type value and then modify the MySQL time zone, you will get an incorrect time when you read the value again. This does not happen with date and datetime.
4. The timestamp type provides the automatic update function, you only need to set its default value to CURRENT_TIMESTAMP.
5. Except that date is kept to the day, datetime and timestamp are both kept to the second, and milliseconds are ignored.

Time format

mysql provides a relatively loose time string format for addition, deletion, modification and query. Refer to the iso time format, which is generally written as '2013-06-05 16:34:18'. But you can also abbreviate it as '13-6-5', but this can easily cause confusion. For example, mysql will also treat '13:6:5' as the year, month and day, and when '13:16:5' form, mysql considers it to be an incorrect format, a warning will be given, and then the value stored in the database is '0000-00-00 00:00:00'.

The manual also specifically mentions a situation, that is, when the value of the current year is 0~69, mysql considers it to be 2000~2069, and when 70~99, it considers it to be 1970~1999. I feel like it's superfluous.

In short, if you stay the same and adapt to the ever-changing situation, you can always use the 'YYYY-MM-DD HH:mm:ss' format.

The above is the detailed content of The difference between Datetime and Timestamp in Mysql. For more information, please follow other related articles on the PHP Chinese website!