SQL Date Functions

Date & Time types

In different SQL dialects, date and time types may differ, but the most common are DATE, TIME, DATETIME, TIMESTAMP, and INTERVAL. This article follows MySQL's implementation of these types to show other types and different data ranges for them in other SQL dialects.

The DATE type is used for storing a date that consists of a year, a month, and a day (without the time) in the 'YYYY-MM-DD' format. Use it to store the dates like 2020-12-31 (the 31st of December 2020) or 1996-11-06 (the 6th of November 1996). This type supports any date ranging from 1000-01-01 to 9999-12-31.

To store only the time without the date, use the TIME type. The TIME type stores hours, minutes, and seconds in the 'hh:mm:ss' format. Use it to store the time of the day or to store the time that will represent the interval between two dates because the range supported by this type is very wide, from -838:59:59 to 838:59:59. In MySQL, both '00:00:00' and '-455:34:34' are valid values of the TIME type. Use values like '54:45' as TIME values, but be careful: this value will be interpreted by MySQL as '54:45:00' not as '00:54:45'.

The DATETIME type stores the date and time in the 'YYYY-MM-DD hh:mm:ss' format. It supports the range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. The TIMESTAMP is another type used to store both date and time, but in MySQL, its range is more narrow. The range of TIMESTAMP is from '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Another interesting data type for time storage is INTERVAL. Use it to store the interval between two dates. There are two classes of the INTERVAL type: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The first one stores the difference between two dates in years and months, while the second one will be useful if you need to store the difference in days, hours, minutes, and seconds. This data type can be met in the Oracle SQL dialect.

Current date & time

To use the current date and time while working with data, MySQL provides several functions to get time, date, or both.

Use the CURDATE() or CURRENT_DATE() functions to get the current date. These two functions are fully equivalent. To select the current date, use the following query:

SELECT CURDATE();

Likewise, to get the current time use CURRENT_TIME() or CURTIME() functions. To select the current time, use the following function:

SELECT CURTIME();

To select both date and time, apply CURRENT_TIMESTAMP() function:

SELECT CURRENT_TIMESTAMP();

Date & time difference

To calculate the difference between two dates, utilize the DATEDIFF(first_date, second_date) function. In the MySQL dialect, the DATEDIFF() function yields the number of days between the two dates. However, various other SQL dialects offer the flexibility to specify the desired units for the difference, such as years, months, seconds, and more.

For example, the query below in MySQL will return 5 as a result:

SELECT DATEDIFF('2020-05-15 09:34:34', '2020-05-10 15:34:43');

Caution! A negative number can result from the query execution when first_date is smaller than second_date.

In MySQL, use TIMEDIFF(first_time, second_time) to get the difference between two TIME values.

Parts of dates

To get a part of the date, use the EXTRACT(unit FROM date) function, which extracts a specified piece from a given date.

The query below will extract the month from the given date and return 11 as a result:

SELECT EXTRACT(MONTH FROM '2020-11-04');

Adding and subtracting dates

Add and subtract dates using DATE_ADD(date, INTERVAL value_of_interval units) and DATE_SUB(date, INTERVAL value_of_interval units) functions, respectively.

To add ten days to the current date, use the DATE_ADD function:

SELECT DATE_ADD(CURDATE(), INTERVAL 10 DAY);

This query will return the date in 10 days from today. For example, today is the 14th of December 2020, so the query will return '2020-12-24'.

ADDDATE has two forms: the first is similar to DATE_ADD, and the second accepts only days as an argument:

SELECT ADDDATE(CURDATE(), 10);

The date subtraction works similarly. To subtract two years from the date '1996-11-30':

SELECT DATE_SUB('1996-11-30', INTERVAL 2 YEAR);

As the query result, we will get 1994-11-30.

Time zone support

There are a lot of time zones around the world, so sometimes there is a need to change the timezone of the date and time values.

In MySQL, to convert dates from one time zone to another can use CONVERT_TZ (value, from_time_zone, to_time_zone). As a timezone, use both named time zones, such as 'Europe/Helsinki' or 'UTC' or offsets in the inclusive range from -12:59 to +13:00. Also, use the system time zone using the SYSTEM keyword.

For example, the query below will convert the given date and time from the 'UTC' time zone to the 'US/Eastern' timezone:

SELECT CONVERT_TZ('2008-05-15 12:00:00','UTC','US/Eastern');

To set the time zone per session, use the following query:

SET time_zone = timezone;

Use all the variants of time zone representations mentioned above as timezone values.

‍