MySQL calendar data types: usage features
MySQL 5 has several data types for storing dates and times. These are TIMESTAMP, DATE, DATETIME, TIME and YEAR. All of them have their own characteristics, and the choice in favor of a calendar type should be made separately in each specific situation. I would like to share with you the result of my mini-research of these types, including in the aspect of working with time zones.
So, all calendar data types are described in detail in section “10.3. Date and Time Types ”MySQL guides. And important information regarding the support of time zone DBMS is listed in the section “9.7. MySQL Server Time Zone Support . All the following is based on the study guide. At the same time, in here only nuances of a choice in favor of one or another type are indicated, therefore this material does not replace the manual, but complements it.
')
First, a brief description of each type:
Mistress on the note . Interestingly, most programmers believe that the concept of “timestamp” is Unix time. In fact, the timestamp is a label that is a sequence of characters indicating the date and / or time when a certain event occurred. A "Unix time" (Unix time) or POSIX time is the number of seconds since midnight, January 1, 1970, UTC. The concept of timestamp is broader than Unix time.
After analyzing the description of the types presented above, almost all conclusions can be made about the advantages and disadvantages of certain types. It's pretty simple and obvious.
But before telling about the use of these types, I want to note that in practice another type is often used to store the date and time: an integer value (for storing the date - INT (4 bytes), a date and time - BIGINT (8 bytes)). The difference in the use of integer types from DATE and DATETIME is only in the fact that when outputting, the data is not formatted, and in calculations with dates and time, integers must be converted to the corresponding calendar type. In addition, the validity of the presented value is not checked before saving. Sorting options are preserved. Therefore, it makes sense to use INT and BIGINT in the same cases as DATE and DATETIME in order to maximize portability and independence from the DBMS. I do not see other advantages, if there are any, I suggest to indicate in the comments.
Let's start with the simplest - type YEAR . Its only advantage is its small size - just 1 byte. But because of this, there is a strict restriction on the range of permissible values (the type can store only 255 different values). It is difficult for me to imagine a practical situation when it may be necessary to store years strictly in the range from 1901 to 2155. In addition, the type SMALLINT (2 bytes) gives a range sufficient in most situations to store the year. And saving 1 byte per line in the database table in our time does not make sense.
The DATE and DATETIME types can be combined into one group. They store the date or date and time with a fairly wide range of acceptable values, independent of the time zone set on the server. Their use definitely has a practical meaning. But if you want to keep the dates of historical events that go back in time for Our era, you will have to choose other types of data. For storing dates of certain events that potentially go beyond the TIMESTAMP type range (birthdays, product release dates, election of presidents, launches of space rockets, etc.), these types are perfect. When using these types you need to take into account one important nuance, but more on that below.
The TIME type can be used to store a period of time when precision is not needed for less than 1 second, and for intervals of less than 829 hours. There is nothing more to add.
Remained the most interesting type - TIMESTAMP . It should be considered in comparison with DATE and DATETIME: TIMESTAMP is also intended to store the date and / or time of the origin of certain events. An important difference between them is in the ranges of values: it is obvious that TIMESTAMP is not suitable for storing historical events (even such as birthdays), but it is great for storing current (logging, posting articles, adding goods, placing orders) and upcoming in the foreseeable future events (new releases, calendars and planners, etc.).
The main convenience of using the TIMESTAMP type is that for columns of this type in the tables you can set the default value as a substitution of the current time, as well as the current time settings when updating the record. If you need these capabilities, then with a probability of 99% TIMESTAMP is exactly what you need. (How to do this, see the manual.)
Do not be afraid that with the approach to 2038 your software will stop working. First, until this time your software will most likely just stop using (especially the versions that are being written now). Secondly, approaching this date, MySQL developers will definitely come up with something to keep your software up and running. Everything will be solved as well as the Y2K problem.
So, the TIMESTAMP type is used to store the dates and times of the events of our time, and DATETIME and DATE - to store the dates and times of the historical events, or the events of the deep future.
Value ranges are an important difference between the TIMESTAMP, DATETIME and DATE types, but not the main one. The key is that TIMESTAMP stores the value in UTC . When the value is saved, it is transferred from the current time zone to UTC, and when it is read, it is transferred during the current time zone from UTC. DATETIME and DATE always store and output the same time, regardless of time zones.
Time zones are set in MySQL DBMS globally or for the current connection . The latter can be used to ensure the work of different users in different time zones at the DBMS level . All time values will be physically stored in UTC, and received from the client and given to the client in the values of its time zone. But only when using the TIMESTAMP data type. DATE and DATETIME always accept, store, and give the same value.
The NOW () function and its synonyms return the time value in the current user time zone.
Considering all these circumstances, you must be extremely careful when changing the time zone within the connection to the server and using the DATE and DATETIME types. If you need to store a date (for example, date of birth), then there will be no problems. Date of birth in any zone is the same. Those. If you were born on January 1 at 0:00 UTC / GMT + 0, this does not mean that your birthday will be celebrated on December 31 in America. But if you decide to store the event time in the DATETIME column, then building work with custom time zones at the DBMS level simply will not work. Let me explain with an example:
User X is working in the UTC / GMT + 2 zone, Y - in the UTC / GMT + 3 zone. For user connections to MySQL, the corresponding (each has its own) time zone is set. The user places a message on the forum, we are interested in the date of writing the message.
Option 1: DATETIME. User X writes a message at 14:00 UTC / GMT + 2. The value in the “date” field of the message is substituted as the result of the execution of the NOW () function - 14:00. User Y reads the time of writing the message and sees the same 14:00. But he has the UTC / GMT + 3 zone in the settings, and he thinks that the message was written not just that, but an hour ago.
Option 2: TIMESTAMP. User X writes a message at 14:00 UTC / GMT + 2. The “date” field contains the result of the NOW () function execution - in this case, 12:00 UTC / GMT + 0. UserY reads the time of writing the message and receives (UTC / GMT + 3) (12:00 UTC / GMT + 0) = 15:00 UTC / GMT + 3. Everything turns out exactly as we want. And most importantly, it is extremely convenient to use this: to support custom time zones, you do not need to write any time reference code.
The possibilities of substituting the current time and working with time zones in the TIMESTAMP type are so weighty that if you need to store a date without time in a certain log, you should still use TIMESTAMP instead of DATE, not saving 1 byte of the difference between them. In this case, at "00:00:00" just do not pay attention.
If you cannot use TIMESTAMP due to the relatively small range of its values (and usually it is 1-2 cases versus 10-15 in the base of the site), you will have to use DATETIME and carefully correct it in the right places (i.e., when recording in this field translate the date in UTC, and when reading - during the time in the zone of the reading user). If you keep only the date, then most likely it does not matter what your time zone is: everyone celebrates the new year on January 1 local time, there is no need to translate anything here.
So, all calendar data types are described in detail in section “10.3. Date and Time Types ”MySQL guides. And important information regarding the support of time zone DBMS is listed in the section “9.7. MySQL Server Time Zone Support . All the following is based on the study guide. At the same time, in here only nuances of a choice in favor of one or another type are indicated, therefore this material does not replace the manual, but complements it.
')
First, a brief description of each type:
- TIMESTAMP - data type for storing date and time. Data is stored in the form of the number of seconds elapsed since the beginning of the “Unix era”. Range of values: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. It takes 4 bytes.
- YEAR is the data type for storing the year. Range of values: 1901 - 2155. It takes 1 byte.
- DATE is a data type for storing a date. Range of values: 1000-01-01 - 9999-12-31. It takes 3 bytes.
- TIME - data type for storing time. The range of values is −828: 59: 59– 828: 59: 59. It takes 3 bytes.
- DATETIME is a data type for storing date and time. Range of values: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. It takes 8 bytes.
Mistress on the note . Interestingly, most programmers believe that the concept of “timestamp” is Unix time. In fact, the timestamp is a label that is a sequence of characters indicating the date and / or time when a certain event occurred. A "Unix time" (Unix time) or POSIX time is the number of seconds since midnight, January 1, 1970, UTC. The concept of timestamp is broader than Unix time.
After analyzing the description of the types presented above, almost all conclusions can be made about the advantages and disadvantages of certain types. It's pretty simple and obvious.
But before telling about the use of these types, I want to note that in practice another type is often used to store the date and time: an integer value (for storing the date - INT (4 bytes), a date and time - BIGINT (8 bytes)). The difference in the use of integer types from DATE and DATETIME is only in the fact that when outputting, the data is not formatted, and in calculations with dates and time, integers must be converted to the corresponding calendar type. In addition, the validity of the presented value is not checked before saving. Sorting options are preserved. Therefore, it makes sense to use INT and BIGINT in the same cases as DATE and DATETIME in order to maximize portability and independence from the DBMS. I do not see other advantages, if there are any, I suggest to indicate in the comments.
Using calendar types given in MySQL
Let's start with the simplest - type YEAR . Its only advantage is its small size - just 1 byte. But because of this, there is a strict restriction on the range of permissible values (the type can store only 255 different values). It is difficult for me to imagine a practical situation when it may be necessary to store years strictly in the range from 1901 to 2155. In addition, the type SMALLINT (2 bytes) gives a range sufficient in most situations to store the year. And saving 1 byte per line in the database table in our time does not make sense.
The DATE and DATETIME types can be combined into one group. They store the date or date and time with a fairly wide range of acceptable values, independent of the time zone set on the server. Their use definitely has a practical meaning. But if you want to keep the dates of historical events that go back in time for Our era, you will have to choose other types of data. For storing dates of certain events that potentially go beyond the TIMESTAMP type range (birthdays, product release dates, election of presidents, launches of space rockets, etc.), these types are perfect. When using these types you need to take into account one important nuance, but more on that below.
The TIME type can be used to store a period of time when precision is not needed for less than 1 second, and for intervals of less than 829 hours. There is nothing more to add.
Remained the most interesting type - TIMESTAMP . It should be considered in comparison with DATE and DATETIME: TIMESTAMP is also intended to store the date and / or time of the origin of certain events. An important difference between them is in the ranges of values: it is obvious that TIMESTAMP is not suitable for storing historical events (even such as birthdays), but it is great for storing current (logging, posting articles, adding goods, placing orders) and upcoming in the foreseeable future events (new releases, calendars and planners, etc.).
The main convenience of using the TIMESTAMP type is that for columns of this type in the tables you can set the default value as a substitution of the current time, as well as the current time settings when updating the record. If you need these capabilities, then with a probability of 99% TIMESTAMP is exactly what you need. (How to do this, see the manual.)
Do not be afraid that with the approach to 2038 your software will stop working. First, until this time your software will most likely just stop using (especially the versions that are being written now). Secondly, approaching this date, MySQL developers will definitely come up with something to keep your software up and running. Everything will be solved as well as the Y2K problem.
So, the TIMESTAMP type is used to store the dates and times of the events of our time, and DATETIME and DATE - to store the dates and times of the historical events, or the events of the deep future.
Value ranges are an important difference between the TIMESTAMP, DATETIME and DATE types, but not the main one. The key is that TIMESTAMP stores the value in UTC . When the value is saved, it is transferred from the current time zone to UTC, and when it is read, it is transferred during the current time zone from UTC. DATETIME and DATE always store and output the same time, regardless of time zones.
Time zones are set in MySQL DBMS globally or for the current connection . The latter can be used to ensure the work of different users in different time zones at the DBMS level . All time values will be physically stored in UTC, and received from the client and given to the client in the values of its time zone. But only when using the TIMESTAMP data type. DATE and DATETIME always accept, store, and give the same value.
The NOW () function and its synonyms return the time value in the current user time zone.
Considering all these circumstances, you must be extremely careful when changing the time zone within the connection to the server and using the DATE and DATETIME types. If you need to store a date (for example, date of birth), then there will be no problems. Date of birth in any zone is the same. Those. If you were born on January 1 at 0:00 UTC / GMT + 0, this does not mean that your birthday will be celebrated on December 31 in America. But if you decide to store the event time in the DATETIME column, then building work with custom time zones at the DBMS level simply will not work. Let me explain with an example:
User X is working in the UTC / GMT + 2 zone, Y - in the UTC / GMT + 3 zone. For user connections to MySQL, the corresponding (each has its own) time zone is set. The user places a message on the forum, we are interested in the date of writing the message.
Option 1: DATETIME. User X writes a message at 14:00 UTC / GMT + 2. The value in the “date” field of the message is substituted as the result of the execution of the NOW () function - 14:00. User Y reads the time of writing the message and sees the same 14:00. But he has the UTC / GMT + 3 zone in the settings, and he thinks that the message was written not just that, but an hour ago.
Option 2: TIMESTAMP. User X writes a message at 14:00 UTC / GMT + 2. The “date” field contains the result of the NOW () function execution - in this case, 12:00 UTC / GMT + 0. UserY reads the time of writing the message and receives (UTC / GMT + 3) (12:00 UTC / GMT + 0) = 15:00 UTC / GMT + 3. Everything turns out exactly as we want. And most importantly, it is extremely convenient to use this: to support custom time zones, you do not need to write any time reference code.
The possibilities of substituting the current time and working with time zones in the TIMESTAMP type are so weighty that if you need to store a date without time in a certain log, you should still use TIMESTAMP instead of DATE, not saving 1 byte of the difference between them. In this case, at "00:00:00" just do not pay attention.
If you cannot use TIMESTAMP due to the relatively small range of its values (and usually it is 1-2 cases versus 10-15 in the base of the site), you will have to use DATETIME and carefully correct it in the right places (i.e., when recording in this field translate the date in UTC, and when reading - during the time in the zone of the reading user). If you keep only the date, then most likely it does not matter what your time zone is: everyone celebrates the new year on January 1 local time, there is no need to translate anything here.
Source: https://habr.com/ru/post/69983/
All Articles