Building a database writer cannot avoid using dates and times. For a database, there are a variety of date and time fields to choose from, such as timestamp and datetime, and using int to store unix timestamp.
Not only newbies, but also some experienced programmers are still confused. Which type should I use to store dates and time?
Then we analyze their characteristics step by step, so that we can choose the appropriate field type according to our needs to store (the advantages and disadvantages are compared, which is the same as parents who like to compare their neighbors and children with themselves since childhood)
datetime and timestamp
- datetime is more like the combination of time on the calendar and the time on your watch, which refers to a specific time.
- timestamp is more suitable for recording time. For example, I am in East Eighth District and it is now 2016-08-02 10:35:52, and you are in Japan (the time in East Eighth District and it is 2016-08-02 11:35:52), and I am chatting with you. The database records the time. After taking it out, the time is 2016-08-02 10:35:52 for me, and for you in Japan, it is 2016-08-02 11:35:52. So there is no need to consider the calculation of the time zone.
- The time range is a timestamp flaw (1970-2038), and of course datetime (1000-9999) cannot record when Liu Bei was born (161).
timestamp and UNIX timestamp
- The display is intuitive, and it is easier to troubleshoot if there are problems. It looks much better than many long int numbers.
- int is accumulated since 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 contain milliseconds. If milliseconds are needed, it needs to be defined as a floating point number. datetime and timestamp natively come with 6-bit microseconds.
- timestamp is converted with its own time zone, the same as item 2 above.
- The time entered by the user's front-end is generally date type. If int is stored, it also needs to be stored before and after processing.
Summarize:
- timestamp records frequently changed updates / creation / publish / log time / purchase time / login time / registration time etc., and it is the time, which is sufficient for the time zone to be processed automatically, such as overseas purchases or business may expand overseas.
- datetime records fixed time such as server execution scheduled tasks time / fitness exercise scheduled time, etc., and in any time zone, a fixed time is required to do something. Timestamp is exceeded, if the time zone is needed, the time zone must be remembered for processing.
- UNIX timestamps is not very convenient to use. As for comparing ranges, timestamp and datetime can do both.
- If you don't consider the time zone, or have your own time zone plan, you can do whatever you like and which one you like
- laravel is an international design framework. For the convenience of programmers and to meet database design standards, it is understandable that created_at updated_at uses timestamp.
- Is there a time type that solves the problem of range and time zone? This is impossible, isn't there tinyInt BigInt? Take your needs, and MySQL allows database fields to be changed.
- Birthdays can be stored using multiple fields, such as year/month/day, so that it is easy to find users who celebrate their birthdays 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 should I choose? The demand is determined.
This is the article about how to choose the appropriate MySQL date and time type to store your time. For more related content on MySQL date and time type, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!