How to choose the right MySQL datetime type to store your time

When building a database and writing a program, it is inevitable to use date and time. For the database, there are a variety of date and time fields to choose from, such as timestamp and datetime, and use int to store unix timestamp.

Not only novices, but also some experienced programmers are still confused. Which type should I use to store date and time?

Then we will analyze their characteristics step by step, so that we can choose the appropriate field type to store according to our needs (advantages and disadvantages are compared, just like parents like to compare their neighbors' children with themselves since childhood)

datetime and timestamp

1. datetime is more like a combination of the time on the calendar and the time on your watch, which means a specific time.
2. timestamp is more suitable for recording time. For example, I am in 2016-08-02 10:35:52 in Eastern Time Zone 8, and you are in Japan (the time in Eastern Time Zone 9 is 2016-08-02 11:35:52). We are chatting, and the database records the time. After it is retrieved, the time for me is 2016-08-02 10:35:52, and for you in Japan it is 2016-08-02 11:35:52. So there is no need to consider the time zone calculation.
3. The time range is a limitation of timestamp (1970-2038). Of course, datetime (1000-9999) cannot record when Liu Bei was born (161).

timestamp and UNIX timestamp

1. The display is intuitive, which makes it easier to troubleshoot problems. It is much better looking than many long int numbers.
2. int starts to accumulate from 1970, but the range supported by int is from 1901-12-13 to 2038-01-19 03:14:07. If a larger range is required, it needs to be set to bigInt. However, this time does not include milliseconds. If milliseconds are needed, it needs to be defined as a floating point number. datetime and timestamp come with 6 digits of microseconds natively.
3. timestamp has its own time zone conversion, same as item 2 above.
4. The time entered by the user on the front end is generally in date type. If it is stored as int, it needs to be processed before storage and after retrieval.

Summarize:

1. timestamp records frequently changing update/creation/publishing/logging time/purchase time/login time/registration time, etc., and is recent and sufficient, with automatic time zone processing, for example, when doing overseas shopping or business may expand overseas
2. datetime records fixed time, such as the time when the server executes a scheduled task/fitness exercise plan time, etc. A fixed time is required to do something in any time zone. If the time zone is beyond the timestamp, you must remember the time zone processing if the time zone is required
3. UNIX timestamps are not very convenient to use. As for comparing ranges, timestamp and datetime can do it.
4. If you don't care about time zones, or have your own time zone plan, it's up to you to choose whichever one you like.
5. Laravel is an internationally designed framework. For the convenience of programmers and in compliance with database design standards, it is understandable that created_at and updated_at use timestamp.
6. Is there a time type that solves the problem of range and time zone? This is impossible, aren't there also tinyInt and BigInt? Take what you need, and MySQL allows database fields to be changed.
7. Birthdays can be stored using multiple fields, such as year/month/day, so that it is easy to find users whose birthdays are on a certain day (User::where(['month' => 8, 'day' => 12])->get())

When building a project, you need to think carefully about which one is more suitable for your business scenario. Which one to choose? Depends on demand.

This is the end of this article on how to choose the right MySQL date and time type to store your time. For more information about MySQL date and time types, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!