The difference and choice between datetime and timestamp in MySQL

The two commonly used time storage types in MySQL are datetime and timestamp . How to choose between them is a necessary consideration when building a table. Let’s talk about their differences and how to choose.

1 Difference

1.1 Space Occupancy

1.2 Representation Scope

timestamp is translated into Chinese as " timestamp ", which is the number of seconds from the current time to the Unix year 1 (0 hours, 0 minutes, 0 seconds on January 1, 1970). For some time calculations, it is more difficult if they are in the form of datetime . For example, if I was born on 1994-1-20 06:06:06 and the current time is 2016-10-1 20:04:50 , then to calculate how many seconds I have lived, a function is needed to convert the datetime, but timestamp can be directly subtracted.

1.3 Time Zone

timestamp only takes up 4 bytes and is stored in UTC format. It will automatically retrieve the current time zone and perform conversion.

datetime is stored as 8 bytes and no time zone retrieval is performed.

That is to say, for timestamp , if the time zone when storing and retrieving are different, the retrieved data will also be different. For datetime , you get what you put in.

Another difference is that if NULL is stored, timestamp will automatically store the current time, while datetime will store NULL.

2 Testing

Let's create a new table

Inserting Data

Looking at the data, you can see that NULL is stored. timestamp will automatically store the current time, while datetime will store NULL

Change the time zone to East 9 and check the data again. You will find timestamp is one hour more than datetime .

What if the inserted value is invalid? If the inserted time is a timestamp

The result is 0000-00-00 00:00:00 . According to the official explanation, if the inserted value is invalid, it will be converted 0000-00-00 00:00:00 , but the timestamp is not a valid time format MySQL .

So what forms can be inserted? Here are three

//The following are all forms allowed by MySQL, and MySQL will automatically process them
2016-10-01 20:48:59
2016#10#01 20/48/59
20161001204859

3 Choices  

If the time is to exceed Linux time, or the server time zone is different, it is recommended to choose datetime .

If you want to use the automatic insertion or automatic update time function, you can use timestamp .

If you just want to represent year, date, and time, you can also use year , date , and time , which occupy 1, 3, and 3 bytes respectively, and datetime is their collection.

This is the end of this article about the difference and selection between datetime and timestamp in MySQL . For more information about datetime and timestamp in MySQL, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!