The definitive reference to datetime data types. Mysql to format - Should I use datetime or timestamp data type in MySQL

The DATETIME, DATE, and TIMESTAMP types are related data types. This section describes their properties, common features and differences.

The DATETIME data type is used for values that contain both date and time information. MySQL retrieves and displays DATETIME values in the format "YYYY-MM-DD HH:MM:SS" . The supported range of values is from "1000-01-01 00:00:00" to "9999-12-31 23:59:59". ("supported" means that while values with earlier time values may still work, there is no guarantee that they will be stored and displayed correctly).

The DATE type is used for values with only date information, without a time part. MySQL retrieves and displays DATE values in "YYYY-MM-DD" format. The supported range of values is from "1000-01-01" to "9999-12-31".

The TIMESTAMP column type provides a data representation type that can be used to automatic recording current dates and time when performing INSERT or UPDATE operations. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

The first TIMESTAMP column is automatically updated when any of the following conditions are true:

The column is not explicitly specified in the INSERT or LOAD DATA INFILE command.
The column is not explicitly specified in the UPDATE command, and it changes a value in some other column (note that an UPDATE command that sets the column to the same value it had before the command was executed will not cause the TIMESTAMP column to be updated, because for performance reasons MySQL ignores such updates when setting a column to its current value).
The value in the TIMESTAMP column is explicitly set to NULL.

For the remaining (except for the first) columns of the TIMESTAMP type, you can also set the value to the current date and time. To do this, you simply need to set the column to NULL or NOW() .

Any TIMESTAMP column (even the first column of this type) can be set to a value other than the current date and time. This is done by explicitly setting it to the desired value. This property can be used, for example, if you need to set the TIMESTAMP column to the current date and time when creating a row, and when you subsequently update that row, the value of the column should not change:

Let MySQL automatically set the value of the TIMESTAMP column when creating a given row. The column will be reset to the current date and time.
When you make subsequent updates to other columns in a given row, you must explicitly set the TIMESTAMP column to its current value.

However, on the other hand, it might be easier to use the DATETIME column for these purposes. When a row is created, it should be initialized with the NOW() function and left alone for subsequent updates.

TIMESTAMP values can range from the beginning of 1970 to some value in 2037 with a resolution of one second. These quantities are output as numeric values.

The data format in which MySQL retrieves and displays TIMESTAMP values depends on the number of characters shown. This is illustrated in the table below. Full TIMESTAMP format is 14 decimal places, but you can create TIMESTAMP columns with a shorter output string:

Regardless of the size of the output value, the size of the data stored in TIMESTAMP columns is always the same. The most commonly used output format is 6, 8, 12, or 14 decimal places. When you create a table, you can specify a custom size for the output values, but if you set the size to 0 or greater than 14, the value 14 will be used. Odd size values in the range 1 to 13 will be scaled to the nearest larger even number.

The DATETIME , DATE and TIMESTAMP values can be specified in any standard set of formats:

As a string in the format "YYYY-MM-DD HH:MM:SS" or in the format "YY-MM-DD HH:MM:SS" . A ``lightweight'' syntax is allowed - any punctuation mark can be used as a separator between parts of date or time sections. For example, the values "98-12-31 11:30:45" , "98.12.31 11+30+45" , "98/12/31 11*30*45" and "98@12@31 11^30^ 45" are equivalent.
As a string in the format "YYYY-MM-DD" or in the format "YY-MM-DD". The ``lightweight'' syntax is also acceptable here. For example, the values "98-12-31" , "98.12.31" , "98/12/31" and "98@12@31" are equivalent.
As a string without delimiters in the format "YYYYMMDDHHMMSS" or in the format "YYMMDDHHMMSS", provided that the string is understood as a date. For example, the values "19970523091528" and "970523091528" can be interpreted as "1997-05-23 09:15:28" but the value "971122129015" is invalid (the minute section value is absurd) and is converted to "0000-00-00 00 :00:00" .
As a string without delimiters in the format "YYYYMMDD" or in the format "YYMMDD", provided that the string is interpreted as a date. For example, the values "19970523" and "970523" can be interpreted as "1997-05-23" , but the value "971332" is invalid (month and day section values are meaningless) and is converted to "0000-00-00" .
As a number in the format YYYYMMDDHHMMSS or in the format YYMMDDHHMMSS, provided that the number is interpreted as a date. For example, the values 19830905132800 and 830905132800 are interpreted as "1983-09-05 13:28:00" .
As a number in the YYYYMMDD format or in the YYMMDD format, provided that the number is interpreted as a date. For example, the values 19830905 and 830905 are interpreted as "1983-09-05" .
As the result of executing a function that returns a value acceptable in the context of the DATETIME , DATE , or TIMESTAMP data types (for example, the NOW() or CURRENT_DATE functions).

Invalid DATETIME, DATE, or TIMESTAMP value values are converted to the value ``null'' of the corresponding value type ("0000-00-00 00:00:00" , "0000-00-00" , or 00000000000000).

For quantities represented as strings containing separators between date parts, there is no need to specify two digits for month or day values less than 10. Thus, the value "1979-6-9" is equivalent to the value "1979-06-09". Likewise, for quantities represented as strings containing delimiters within the time notation, there is no need to specify two digits for hours, minutes, or seconds values less than 10 . So,

Quantities defined as numbers must have 6, 8, 12, or 14 decimal places. An 8- or 14-bit number is assumed to be in the format YYYYMMDD or YYYYMMDDHHMMSS, respectively, with the year in the first four digits. If the length of the number is 6 or 12 digits, then the following formats are assumed: YYMMDD or YYMMDDHHMMSS, where the year is indicated in the first two digits. Numbers whose length does not match any of the options described are interpreted as being padded with zeros to the nearest specified length.

Values represented by strings without delimiters are interpreted based on their length according to the following rules. If the string length is 8 or 14 characters, then the year is assumed to be given by the first four characters. Otherwise, the year is assumed to be given by the first two characters. The string is interpreted from left to right, determining the year, month, day, hour, minute, and second values for all sections represented in the string. This means that a string less than 6 characters long cannot be used. For example, if you specify a string like "9903" and think it means March 1999, MySQL will insert a ``null'' date into the table. The year and month in this entry are 99 and 03 respectively, but the section representing the day is missing (the value is zero), so overall given value is not a valid date value.

During storage permissible values TIMESTAMP columns use the full precision specified when they are specified, regardless of the number of characters output. This property has several consequences:

You must always specify the year, month, and day, even for TIMESTAMP(4) or TIMESTAMP(2) types. Otherwise, the specified value will not be a valid date value and will be stored as 0 .
Increasing the width of a narrow TIMESTAMP column by using the ALTER TABLE command will display previously ``hidden'' information.
And similarly, when you narrow a TIMESTAMP column, the stored information will not be lost, unless you take into account that less information will be output when outputting.
Although TIMESTAMP values are stored with full precision, only the UNIX_TIMESTAMP() function can directly operate on this original stored value value. The remaining functions operate on formatted values of the extracted value. This means that functions such as HOUR() or SECOND() cannot be used unless the appropriate portion of the TIMESTAMP value is included in its formatted value. For example, the HH section of a TIMESTAMP column will not be printed until the number of characters printed is at least 10, so attempting to use HOUR() on shorter TIMESTAMP values will produce meaningless results.

Values of one date type can in some cases be assigned to an object of another date type. However, some change in value or loss of information is possible:

If you assign a DATE value to a DATETIME or TIMESTAMP object, the ``time'' part of the resulting value will be set to "00:00:00" because the DATE value does not contain time information.
If you assign a value of type DATE , DATETIME , or TIMESTAMP to a DATE object, the ``time'' part in the result value will be removed because the DATE type does not include time information.
Although all DATETIME, DATE, and TIMESTAMP values can be specified using the same set of formats, be aware that the specified types have different ranges of valid values. For example, TIMESTAMP values cannot have date values earlier than 1970 or later than 2037. This means that a date such as "1968-01-01" while legal for a DATETIME or DATE value, is not legal for a TIMESTAMP value and will be converted to 0 when assigned to that object.

When setting date values, you should keep in mind some pitfalls:

The simplified format that is allowed for string values can be misleading. For example, a value such as "10:11:12" could be a time value due to the `:" separator, but when used in a date context, it would be interpreted as the year "2010-11-12". At the same time, the value "10:45:15" will be converted to "0000-00-00" because the month value "45" is not valid.
The MySQL server only performs an initial date validity check: days 00-31, months 00-12, years 1000-9999. Any date outside this range is converted to 0000-00-00. It should be noted that, however, it is not prohibited to store incorrect dates, such as 2002-04-31. This allows web applications to save form data without additional validation. To ensure the accuracy of the date, a check is performed in the application itself.
The values of the year, represented by two digits, allow for ambiguous interpretation, since the century is unknown. MySQL interprets two-digit year values using the following rules:
- Year values in the range 00-69 are converted to 2000-2069.
- Year values in the range 70-99 are converted to 1970-1999.

date and time data types: DATETIME, DATE, TIMESTAMP, TIME and YEAR. Each of them has an interval acceptable values, as well as the value "null", which is used when the user enters a truly invalid value. Note that MySQL allows you to store some date values that are not entirely reliable, for example 1999-11-31. The reason is that managing date validation is the responsibility of specific application, not SQL servers. To speed up date validation, MySQL only checks if the month is in the range 0-12 and the day is in the range 0-31. These intervals start at 0 to allow MySQL to store dates in DATE or DATETIME columns where the day or month is zero. This feature is especially useful for applications that require storing the date of birth - here the day or month of birth is not always known. In such cases, the date is simply stored as 1999-00-00 or 1999-01-00 (but you should not expect the DATE_SUB() or DATE_ADD functions to return the correct values for such dates).

MySQL retrieves values for a given date or time type only in standard format, but at the same time attempts to interpret a variety of formats that may come from users (for example, when specifying a value that should be assigned a date or time type or compared with a value that has one of these types). However, only the formats described in the following sections are supported. The user is expected to enter valid values for quantities, as using quantities in other formats may produce unpredictable results.

Although MySQL attempts to interpret values in multiple formats, in all cases it is expected that the date value section containing the year will be on the far left. Dates must be specified in the order year-month-day (for example, "98-09-04"), and not in the order month-day-year or day-month-year, i.e. not the way we usually write them (for example, "09-04-98", "04-09-98").
MySQL automatically converts a date or time value to a number when the value is used in a numeric context, and vice versa.
A value that has a date or time type that is out of bounds set interval or is invalid for this data type (see the beginning of the section), is converted to the value "null" for this type. (The exception is for values of type TIME that go beyond the boundaries of the specified interval, which are truncated to the corresponding boundary point of the specified TIME interval). In table 4.3. Here are the formats for the value "null" for each column type:

Zero values are special. To store them or refer to them, you can explicitly use the values presented in the table, or you can use “0”, which is easier to write.

DATETIME, DATE and TIMESTAMP data types

The DATETIME data type is used for values that contain both date and time information. MySQL retrieves and displays DATETIME values in the format "YYYY-MM-DD HH:MM:SS". The supported range of values is from "1000-01-01 00:00:00" to "9999-12-31 23:59:59". ("supported" means that while values with earlier time values may still work, there is no guarantee that they will be stored and displayed correctly).

The DATE type is used for values with only date information, without a time part. MySQL retrieves and displays DATE values in the "YYYY-MM-DD" format. The supported range of values is from "1000-01-01" to "9999-12-31".

The TIMESTAMP column type provides a data representation type that can be used to automatically record the current date and time when performing INSERT or UPDATE operations. If you have multiple TIMESTAMP columns, only the first one is updated automatically.

For the remaining (except for the first) columns of the TIMESTAMP type, you can also set the value to the current date and time. To do this, you simply need to set the column to NULL or NOW() .

Any TIMESTAMP column (even the first column of that type) can be set to a value other than the current date and time. This is done by explicitly setting it to the desired value. This property can be used, for example, if you want to set the TIMESTAMP column to the current date and time when creating a row, and when you subsequently update that row, the value of the column should not change.

TIMESTAMP values can range from the beginning of 1970 to some value in 2037 with a resolution of one second. These quantities are output as numeric values.

The data format in which MySQL retrieves and displays TIMESTAMP values depends on the number of characters shown. This is illustrated in Table 4.4. Full format TIMESTAMP is 14 decimal places, but you can create TIMESTAMP columns with a shorter output string:

Table 4.4. TIMESTAMP data format depending on the number of digits extracted

Column type	Output Format
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

The DATETIME , DATE and TIMESTAMP values can be set to any standard set formats:

As a string in the format "YYYY-MM-DD HH:MM:SS" or in the format "YY-MM-DD HH:MM:SS". "Lightweight" syntax is allowed - you can use any punctuation mark as a separator between parts of date or time sections. For example, the values "98-12-31 11:30:45", "98.12.31 11+30+45", "98/12/31 11*30*45" and "98@12@31 11^30^ 45" are equivalent.
As a string in the format "YYYY-MM-DD" or in the format "YY-MM-DD". A "lightweight" syntax is also acceptable here. For example, the values "98-12-31", "98.12.31", "98/12/31" and "98@12@31" are equivalent.
As a string without delimiters in the format "YYYYMMDDHHMMSS" or in the format "YYMMDDHHMMSS", provided that the string is understood as a date. For example, the values "19970523091528" and "970523091528" can be interpreted as "1997-05-23 09:15:28", but the value "971122129015" is invalid (the minute section value is absurd) and is converted to "0000-00-00 00 :00:00."
As a string without delimiters in the format "YYYYMMDD" or in the format "YYMMDD", provided that the string is interpreted as a date. For example, the values "19970523" and "970523" can be interpreted as "1997-05-23", but the value "971332" is invalid (month and day section values are meaningless) and is converted to "0000-00-00".
As a number in the format YYYYMMDDHHMMSS or in the format YYMMDDHHMMSS, provided that the number is interpreted as a date. For example, the values 19830905132800 and 830905132800 are interpreted as "1983-09-05 13:28:00".
As a number in the YYYYMMDD format or in the YYMMDD format, provided that the number is interpreted as a date. For example, the values 19830905 and 830905 are interpreted as "1983-09-05".
As the result of executing a function that returns a value acceptable in the context of the DATETIME , DATE , or TIMESTAMP data types (for example, the NOW() or CURRENT_DATE() functions).

TIME data type

MySQL retrieves and displays TIME values in the format "HH:MM:SS" (or in the format "HHH:MM:SS" for larger hour values). TIME values can vary from "-838:59:59" to "838:59:59". The reason that the "hour" part of a value can be so large is that the TIME type can be used not only to represent the time of day (which must be less than 24 hours), but also to represent the total elapsed time or the time interval between two events (which may be significantly longer than 24 hours or even negative).

TIME values can be specified in various formats:

As a string in the format "D HH:MM:SS.fractional part" (note that MySQL does not yet support storing the fractional part of a value in a column of the type in question). You can also use one of the following "lightweight" views: HH:MM:SS.fractional part, HH:MM:SS , HH:MM , D HH:MM:SS , D HH:MM , D HH or SS . Here D is days from the range of values 0-33.

As a string without delimiters in the format "HHMMSS", provided that the string is interpreted as a date. For example, the value "101112" is understood as "10:11:12", but the value "109712" would be invalid (the minute section value is absurd) and would be converted to "00:00:00".
As a number in HHMMSS format, provided that the string is interpreted as a date. For example, the value 101112 is understood as "10:11:12". MySQL also understands the following alternative formats: SS
As a four-digit number in the range from 1901 to 2155.
As a two-character string in the range of values from "00" to "99". Values in the intervals from "00" to "69" and from "70" to "99" are then converted to YEAR values in the intervals from 2000 to 2069 and from 1970 to 1999, respectively.
As a two-digit number in the range from 1 to 99. Values in the ranges from 1 to 69 and from 70 to 99 are then converted to YEAR values in the ranges from 2001 to 2069 and from 1970 to 1999, respectively. Please note that the spacing for two-digit numbers and two-digit strings is slightly different because you cannot specify "zero" directly as a number and interpret it as 2000. You must specify it as the string "0" or "00" or it will be interpreted like 0000.
As the result of executing a function that returns a value acceptable in the context of a YEAR data type (such as NOW() ).

Invalid YEAR values are converted to 0000.

The DATETIME, DATE, and TIMESTAMP types are related data types. This section describes their properties, common features and differences.

The first TIMESTAMP column is automatically updated when any of the following conditions are true:

The column is not explicitly specified in the INSERT or LOAD DATA INFILE command.
The column is not explicitly specified in the UPDATE command, and it changes a value in some other column (note that an UPDATE command that sets the column to the same value it had before the command was executed will not cause the TIMESTAMP column to be updated, because for performance reasons MySQL ignores such updates when setting a column to its current value).
The value in the TIMESTAMP column is explicitly set to NULL.

For the remaining (except for the first) columns of the TIMESTAMP type, you can also set the value to the current date and time. To do this, you simply need to set the column to NULL or NOW() .

Any TIMESTAMP column (even the first column of that type) can be set to a value other than the current date and time. This is done by explicitly setting it to the desired value. This property can be used, for example, if you need to set the TIMESTAMP column to the current date and time when creating a row, and when you subsequently update this row, the value of the column should not change:

Let MySQL automatically set the value of the TIMESTAMP column when creating a given row. The column will be reset to the current date and time.
When you make subsequent updates to other columns in a given row, you must explicitly set the TIMESTAMP column to its current value.

TIMESTAMP values can range from the beginning of 1970 to some value in 2037 with a resolution of one second. These quantities are output as numeric values.

The data format in which MySQL retrieves and displays TIMESTAMP values depends on the number of characters shown. This is illustrated in the table below. The full TIMESTAMP format is 14 decimal places, but you can create TIMESTAMP columns with a shorter output string:

Column type	Output Format
TIMESTAMP(14)	YYYYMMDDHHMMSS
TIMESTAMP(12)	YYMMDDHHMMSS
TIMESTAMP(10)	YYMMDDHHMM
TIMESTAMP(8)	YYYYMMDD
TIMESTAMP(6)	YYMMDD
TIMESTAMP(4)	YYMM
TIMESTAMP(2)	YY

The DATETIME, DATE, and TIMESTAMP values can be specified in any standard set of formats:

As a string in the format "YYYY-MM-DD HH:MM:SS" or in the format "YY-MM-DD HH:MM:SS" . "Lightweight" syntax is allowed - you can use any punctuation mark as a separator between parts of date or time sections. For example, the values "98-12-31 11:30:45" , "98.12.31 11+30+45" , "98/12/31 11*30*45" and "98@12@31 11^30^ 45" are equivalent.
As a string in the format "YYYY-MM-DD" or in the format "YY-MM-DD". "Lite" syntax is also acceptable here. For example, the values "98-12-31" , "98.12.31" , "98/12/31" and "98@12@31" are equivalent.
As a string without delimiters in the format "YYYYMMDDHHMMSS" or in the format "YYMMDDHHMMSS", provided that the string is understood as a date. For example, the values "19970523091528" and "970523091528" can be interpreted as "1997-05-23 09:15:28" but the value "971122129015" is invalid (the minute section value is absurd) and is converted to "0000-00-00 00 :00:00" .
As a string without delimiters in the format "YYYYMMDD" or in the format "YYMMDD", provided that the string is interpreted as a date. For example, the values "19970523" and "970523" can be interpreted as "1997-05-23" , but the value "971332" is invalid (month and day section values are meaningless) and is converted to "0000-00-00" .
As a number in the format YYYYMMDDHHMMSS or in the format YYMMDDHHMMSS, provided that the number is interpreted as a date. For example, the values 19830905132800 and 830905132800 are interpreted as "1983-09-05 13:28:00" .
As a number in the YYYYMMDD format or in the YYMMDD format, provided that the number is interpreted as a date. For example, the values 19830905 and 830905 are interpreted as "1983-09-05" .
As the result of executing a function that returns a value acceptable in the context of the DATETIME , DATE , or TIMESTAMP data types (for example, the NOW() or CURRENT_DATE functions).

Invalid DATETIME, DATE, or TIMESTAMP value values are converted to the value "zero" of the corresponding value type ("0000-00-00 00:00:00", "0000-00-00", or 00000000000000).

For values represented as strings containing delimiters between date parts, there is no need to specify two digits for month or day values less than 10. Thus, the value "1979-6-9" is equivalent to the value "1979-06-09". Likewise, for quantities represented as strings containing delimiters within the time notation, there is no need to specify two digits for hours, minutes, or seconds values less than 10 . So,

Values represented by strings without delimiters are interpreted based on their length according to the following rules. If the string length is 8 or 14 characters, then the year is assumed to be given by the first four characters. Otherwise, the year is assumed to be given by the first two characters. The string is interpreted from left to right, determining the year, month, day, hour, minute, and second values for all sections represented in the string. This means that a string less than 6 characters long cannot be used. For example, if you set a string like "9903" and think it means March 1999, MySQL will insert a "null" date into the table. The year and month in this entry are 99 and 03, respectively, but the day section is missing (the value is zero), so overall this value is not a valid date value.

When valid values are stored in TIMESTAMP columns, the full precision specified when they are specified is used, regardless of the number of characters output. This property has several consequences:

You must always specify the year, month, and day, even for TIMESTAMP(4) or TIMESTAMP(2) types. Otherwise, the specified value will not be a valid date value and will be stored as 0 .
Increasing the width of a narrow TIMESTAMP column by using the ALTER TABLE command will display previously "hidden" information.
And similarly, when you narrow a TIMESTAMP column, the stored information will not be lost, unless you take into account that less information will be output when outputting.
Although TIMESTAMP values are stored with full precision, only the UNIX_TIMESTAMP() function can directly operate on this original stored value value. The remaining functions operate on formatted values of the extracted value. This means that functions such as HOUR() or SECOND() cannot be used unless the appropriate portion of the TIMESTAMP value is included in its formatted value. For example, the HH section of a TIMESTAMP column will not be printed until the number of characters to print is at least 10, so attempting to use HOUR() on shorter TIMESTAMP values will produce meaningless results.

Values of one date type can in some cases be assigned to an object of another date type. However, some change in value or loss of information is possible:

If you assign a DATE value to a DATETIME or TIMESTAMP object, the "time" part of the resulting value will be set to "00:00:00" because the DATE value does not contain time information.
If you assign a value of type DATE , DATETIME , or TIMESTAMP to a DATE object, the "time" portion of the resulting value will be removed because the DATE type does not include time information.
Although all DATETIME, DATE, and TIMESTAMP values can be specified using the same set of formats, be aware that the specified types have different ranges of valid values. For example, TIMESTAMP values cannot have date values earlier than 1970 or later than 2037. This means that a date such as "1968-01-01" while legal for a DATETIME or DATE value, is not legal for a TIMESTAMP value and will be converted to 0 when assigned to that object.

When setting date values, you should keep in mind some pitfalls:

The simplified format that is allowed for string values can be misleading. For example, a value such as "10:11:12" could be a time value thanks to the ":" separator, but when used in a date context it would be interpreted as the year "2010-11-12". At the same time, the value "10:45:15" will be converted to "0000-00-00" since the value "45" is not valid for the month.
The MySQL server only performs an initial check for the validity of the date: days 00-31, months 00-12, years 1000-9999. Any date outside this range is converted to 0000-00-00. It should be noted that, however, it is not prohibited to store incorrect dates, such as 2002-04-31. This allows web applications to save form data without additional validation. To ensure the accuracy of the date, a check is performed in the application itself.
The values of the year, represented by two digits, allow for ambiguous interpretation, since the century is unknown. MySQL interprets two-digit year values using the following rules:
- Year values in the range 00-69 are converted to 2000-2069.
- Year values in the range 70-99 are converted to 1970-1999.

If you have any other questions or something is not clear - welcome to our

I don't recommend using neither DATETIME field, nor TIMESTAMP. If you want to represent a specific day in general (like a birthday), use the DATE type, but if you're more specific, you're probably interested in recording an actual moment rather than a unit of time (day, week, month, year). Instead of using DATETIME or TIMESTAMP, use BIGINT and just store the number of milliseconds since the epoch (System.currentTimeMillis() if you are using Java). This has a number of advantages:

You avoid vendor lock-in. Almost every database supports integers in a relatively similar way. Let's say you want to move to another database. Do you want to worry about the differences between MySQL DATETIME values and how Oracle determines them? Even among different versions MySQL, TIMESTAMPS have different levels of precision. More recently, MySQL supported milliseconds in timestamps.
No time zone problems. There have been some insightful comments here about what's going on with time zones since different types data. But is this general knowledge, and will your colleagues learn it all the time? On the other hand, it is quite difficult to mix up BigINT in java.util.Date. Using BIGINT leads to a lot of timezone issues that fall by the wayside.
Don't worry about ranges or accuracy. You don't have to worry about being truncated by future date ranges (TIMESTAMP is 2038 only).
Integration of third party tools. Using an integer is trivial for third party tools (like EclipseLink) to interact with the database. Not every third party tool will have the same understanding of "datetime" as MySQL does. Want to try to figure out in Hibernate whether you should use a java.sql.TimeStamp or a java.util.Date object if you are using these custom data types? Usage basic types data makes use with third party tools trivial.

This problem is closely related to how you should store the monetary value (i.e. $1.99) in the database. Should you use Decimal or the money type in the database, or worst of all, Double? All 3 of these options are terrible, for many of the reasons listed above. The solution is to store the value of money in cents using BIGINT and then convert the cents to dollars when you display the value to the user. The job of a database is to store data and NOT collect data. All those fancy data types you see in databases (especially Oracle) add little and start you down the path to vendor lock.

2018-12-04T00:00Z

TIMESTAMP is 4 bytes with 8 bytes for DATETIME.

But just like skronid said it has a lower limit of 1970. This is great for anything that might happen in the future though ;)

2018-12-11T00:00Z

The main difference is that DATETIME is constant, while TIMESTAMP depends on the time_zone setting.

So this only matters when you have - or may in the future have - synchronized clusters in time zones.

In more in simple words: If I have a database in Australia and I take a dump of that database to sync/populate the database in America, then TIMESTAMP will be updated to reflect real time events in the new time zone, while DATETIME still reflects the time of the event in the time zone.

A great example of DATETIME being used where TIMESTAMP should have been used is Facebook, where their servers can never be sure what happened during time zones. I once had a conversation where the time was telling me that I was replying to messages before the message was actually sent. (This, of course, could also be caused incorrect translation in time zone software for messaging if the time was sent and not synchronized).

2018-12-18T00:00Z

I always use DATETIME fields for anything other than row metadata (date created or modified).

In the beginning brief description each type:

TIMESTAMP - data type for storing date and time. The data is stored as the number of seconds that have passed since the start of the “Unix era”. Value range: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Occupies 4 bytes.
YEAR - data type for storing the year. Value range: 1901 - 2155. Occupies 1 byte.
DATE is a data type for storing dates. Value range: 1000-01-01 - 9999-12-31. Occupies 3 bytes.
TIME is a data type for storing time. Value range: −828:59:59 - 828:59:59. Occupies 3 bytes.
DATETIME is a data type for storing date and time. Value range: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Occupies 8 bytes.

Note to the hostess. The interesting thing is that most programmers believe that the concept of “timestamp” is Unix time. In fact, a timestamp is a mark that is a sequence of characters indicating the date and/or time when a certain event occurred. And “Unix time” (Unix time) or POSIX time is the number of seconds that have passed since midnight January 1, 1970 UTC. The concept of timestamp is broader than Unix time.

After analyzing the description of the types presented above, you can draw almost all the conclusions about the advantages and disadvantages of certain types. Everything is quite simple and obvious.

But before talking about the use of these types, I want to note that in practice another type is often used to store date and time: an integer value (for storing date - INT (4 bytes), date and time - BIGINT (8 bytes)). The only difference between using integer types and DATE and DATETIME is that the data is not formatted during output, and in calculations with dates and times, integers must be converted to the appropriate calendar type. In addition, there is no check for the validity of the presented value before saving. Sorting capabilities are retained. 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 don’t see any other advantages; if there are any, I suggest you indicate them in the comments.

Using calendar data types in MySQL

Let's start with the simplest - type YEAR. Its only advantage is its small size - only 1 byte. But because of this, there is a strict restriction on the range of valid values (the type can only store 255 different meanings). I have a hard time imagining a practical situation where one might want to store years strictly in the range from 1901 to 2155. Additionally, the SMALLINT type (2 bytes) gives a range that is sufficient in most situations to store a year. And saving 1 byte per row in a database table makes no sense in our time.

Types DATE And DATETIME can be combined into one group. They store a date or date and time with a fairly wide range of valid values, independent of the time zone set on the server. Their use definitely makes practical sense. But if you want to store dates for historical events going back beyond the Common Era, you will have to choose other data types. These types are ideal for storing dates for certain events that potentially fall outside the range of the TIMESTAMP type (birthdays, product release dates, presidential elections, space rocket launches, etc.). One thing to keep in mind when using these types is important nuance, but more on that below.

Type TIME can be used to store a period of time when accuracy less than 1 second is not needed, and time periods of less than 829 hours. There is nothing more to add here.

The most left interesting guy - TIMESTAMP. It should be considered in comparison with DATE and DATETIME: TIMESTAMP is also designed to store the date and/or time of occurrence of certain events. An important difference between them is in the ranges of values: obviously, TIMESTAMP is not suitable for storing historical events (even such as birthdays), but is excellent for storing current ones (logging, dates of posting articles, adding products, placing orders) and upcoming ones in the foreseeable future future events (new version releases, calendars and schedulers, etc.).

The main convenience of using the TIMESTAMP type is that for columns of this type in tables you can set a default value in the form of a substitution of the current time, as well as setting the current time when updating a record. If you need these features, then there is a 99% chance that TIMESTAMP is exactly what you need. (See the manual for how to do this.)

Don't be afraid that your software will stop working as we approach 2038. Firstly, before this time, your software will most likely simply be stopped being used (especially the versions that are being written now). Secondly, as this date approaches, MySQL developers will definitely come up with something to keep your software working. Everything will be solved as well as the Y2K problem.

So, we use the TIMESTAMP type to store the dates and times of events of our time, and DATETIME and DATE to store the dates and times of historical events, or events of the deep future.

Value ranges are an important difference between the TIMESTAMP, DATETIME, and DATE types, but they are not the main difference. Main that TIMESTAMP stores the value in UTC. When storing a value, it is translated from the current time zone to UTC, and when reading it, it is translated from the current time zone from UTC. DATETIME and DATE always store and display the same time, regardless of time zones.

Time zones are set in MySQL DBMS globally or for current connection.The latter can be used to ensure operation 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 his time zone. But only when using the TIMESTAMP data type. DATE and DATETIME always receive, store, and return the same value.

The NOW() function and its synonyms return the time value in the user's current time zone.

Given all these circumstances, you must be extremely careful when changing the time zone within a 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. The date of birth is the same in any zone. Those. if you were born on January 1 at 0:00 UTC/GMT+0, then this Not means that in America they will celebrate your birthday on December 31st. But if you decide to store time events in the DATETIME column, then it simply won’t be possible to work with user time zones at the DBMS level. Let me explain with an example:

User X works in the UTC/GMT+2 zone, Y - in the UTC/GMT+3 zone. For user connections to MySQL, a corresponding (each has its own) time zone is set. The user posts a message on the forum, we are interested in the date the message was written.

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 executing the NOW() function - 14:00. User Y reads the time the message was written and sees the same 14:00. But his settings are set to UTC/GMT+3, and he thinks that the message was written not just now, 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 executing the NOW() function - in in this case- 12:00 UTC/GMT+0. UserY reads the time the message was written and receives (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Everything turns out exactly the way we want. And most importantly, it is extremely convenient to use: to support custom time zones, you do not need to write any time conversion code.

The possibilities of substituting the current time and working with time zones in the TIMESTAMP type are so powerful that if you need to store a date without time in a certain log, you should still use TIMESTAMP instead of DATE, without saving 1 byte of the difference between them. In this case, simply ignore “00:00:00”.

If you cannot use TIMESTAMP due to the relatively small range of its values (usually 1-2 cases versus 10-15 in the site database), you will have to use DATETIME and carefully adjust its values in in the right places(i.e. when writing to this field, convert the date to UTC, and when reading - to the time in the reading user’s zone). If you're only storing the date, then it probably doesn't matter what time zone you have: New Year Everyone celebrates January 1st according to local time, there is no need to translate anything here.