Date and time in SQL

Among all the data types in SQL, date and time data is the most complex 🤯. The complexity arises due to several reasons, and here are some of them:

• many ways to set the date and time
• availability of time zones
• non-obviousness of calculations of some values ​​based on date and time. For example, the complexity of calculating age.

Generation of date and time data

Date and time data can be retrieved in one of the following ways:

• copy data from existing column with date and time type
• set date and time via string representation
• get date and time by calling built-in functions that return a date and time data type

String representation of date and time

The following formats are used to set the date and time:

Moreover, when specifying a date, it is allowed to use any punctuation mark as a separator between parts of the date or time sections. It is also possible to set the date without a separator character at all, together.

Examples of valid setting of values ​​for date and time via string representation:

MySQL 8.1
SELECT  CAST("2022-06-16 16:37:23" AS DATETIME) AS datetime_1,
        CAST("2014/02/22 16*37*22" AS DATETIME) AS datetime_2,
        CAST("20220616163723" AS DATETIME) AS datetime_3,
        CAST("2021-02-12" AS DATE) AS date_1,
        CAST("160:23:13" AS TIME) AS time_1,
        CAST("89" AS YEAR) AS year

MySQL 8.1
SELECT  CAST('2022-06-16 16:37:23' AS TIMESTAMP) AS timestamp_1,
        CAST('2014/02/22 16:37:22' AS TIMESTAMP) AS timestamp_2,
        CAST('20220616163723' AS TIMESTAMP) AS timestamp_3,
        CAST('2021-02-12' AS DATE) AS date_1,
        CAST('16:23:13' AS TIME) AS time_1

In the query above, the CAST function was used to force the string to be converted to a date and time. It is needed if the server does not expect the date and time and, accordingly, does not automatically convert the string to the correct type. We'll learn more about type conversion in "Type conversion functions, CAST".

Date generation functions

If you need to get the date and time from a string that does not match any format that accepts the CAST function, you can use special functions for parsing dates.

MySQL 8.1
SELECT STR_TO_DATE('November 13, 1998', '%M %d, %Y') AS date;

MySQL 8.1
SELECT TO_DATE('November 13, 1998', 'Month DD, YYYY') AS date;

To generate the current date or time, there is no need to create a string for its subsequent conversion to a date, because there are built-in functions for getting given values.

MySQL 8.1
SELECT CURDATE(), CURTIME(), NOW();

MySQL 8.1
SELECT CURRENT_DATE, CURRENT_TIME, NOW();

Date and Time Extraction Functions

Sometimes it is necessary to obtain information not about the full date, but about its specific part, for example, about its month or year.

The difference between DATETIME and TIMESTAMP

Time zones

Since people all over the world want noon to approximate the maximum rise of the Sun, there has never been a problem to use universal time and the world was divided into 24 time zones.

UTC (Coordinated Universal Time) is currently used as the time reference point. All other time zones can be described by the number of hours offset from UTC. For example, the Moscow time zone can be described as UTC+3.

The time zone is one of the database server settings and can be set:

• globally
• for the current user
• for the current user session

MySQL 8.1
SET GLOBAL time_zone = '+03:00';    // globally
SET time_zone = '+03:00';           // for the current user
SET @@session.time_zone = '+03:00'; // for the current user session

MySQL 8.1
ALTER DATABASE mydb SET timezone = 'Europe/Moscow';  // globally for database
ALTER USER myuser SET timezone = 'Europe/Moscow';    // for specific user
SET TIME ZONE 'Europe/Moscow';                       // for current session
SET TIME ZONE '+03:00';                              // for current session

Examples of tasks for date and time

I would like to pay special attention to the most popular tasks related to the temporary data type, where mistakes are often made.

Age determination

When setting the task to find the age of a person by the date of his birth, there is often a temptation 😈 to calculate the difference between the current year and the person's year of birth:

MySQL 8.1
SELECT YEAR(NOW()) - YEAR('2003-07-03 14:10:26');

MySQL 8.1
SELECT EXTRACT(YEAR FROM NOW()) - EXTRACT(YEAR FROM TIMESTAMP '2003-07-03 14:10:26');

The problem with this approach is that it does not take into account whether the person had a birthday this year or not yet. That is, if at the time of the request it was already July 3rd (07-03), then the person celebrated his birthday and he is already 20 years old, otherwise he is still 19 years old. The difference in functions will be useless here - in both cases it will give 20 years.

If determining the age in terms of the difference in years is a non-working option, then you may want to find the age in terms of the difference of days between two dates, then divide this difference by the number of days in a year and round down:

MySQL 8.1
SELECT FLOOR(DATEDIFF(NOW(), '2003-07-03 14:10:26') / 365);

MySQL 8.1
SELECT FLOOR(EXTRACT(DAY FROM NOW() - TIMESTAMP '2003-07-03 14:10:26') / 365);

And this solution will be much more accurate than the previous one. But it will not be absolutely accurate due to the presence of leap years, when there are 366 days in a year. Although the error in calculating the age for 1 person due to the presence of a leap year is quite small, in the calculations for determining, say, average age among a certain list of people, the error can accumulate and distort the real values.

And how then is it correct to determine the age?

MySQL 8.1
SELECT TIMESTAMPDIFF(YEAR, '2003-07-03 14:10:26', NOW());

MySQL 8.1
SELECT EXTRACT(YEAR FROM AGE(NOW(), TIMESTAMP '2003-07-03 14:10:26'));