All Date and Time Units in MariaDB

In MariaDB, you can use different units to represent time, especially time periods INTERVAL. For example INTERVAL 1 HOUR, means one hour.

Units

The following table is a complete list of date and time units that can be used in MariaDB:

Composite unit

In the units, the following units are composite units:

• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

Composite units are formed by combining two or more units. For example: INTERVAL '1:2' YEAE_MONTHrepresent one year and two months.

MariaDB allows you to use arbitrary separators to separate different units, for example, all the following represent a year and two months:

• INTERVAL '1:2' YEAR_MONTH
• INTERVAL '1@2' YEAR_MONTH
• INTERVAL '1#2' YEAR_MONTH

Examples

INTERVAL

Some examples representing time periods are given below:

• INTERVAL 1 DAY represents a day
• INTERVAL 1 MONTH represents january
• INTERVAL 1 YEAE represents a year
• INTERVAL 1:2 YEAE_MONTH represents a year and two months
• INTERVAL '1:20:30' HOUR_SECOND represents 1 hour, 20 minutes and 30 seconds

EXTRACT()

The following statement uses the EXTRACT() function to extract the year from 2001-02-03 04:05:06:

SELECT EXTRACT(YEAR FROM '2001-02-03 04:05:06');

Output:

+------------------------------------------+
| EXTRACT(YEAR FROM '2001-02-03 04:05:06') |
+------------------------------------------+
|                                     2001 |
+------------------------------------------+

The following statement uses the EXTRACT() function to extract the time from 2001-02-03 04:05:06:

SELECT EXTRACT(HOUR_SECOND FROM '2001-02-03 04:05:06');

Output:

+-------------------------------------------------+
| EXTRACT(HOUR_SECOND FROM '2001-02-03 04:05:06') |
+-------------------------------------------------+
|                                           40506 |
+-------------------------------------------------+

ADDDATE()

The following statement is an example of adding minutes to a datetime value:

SELECT ADDDATE('2022-12-30 10:20:30', INTERVAL 20 MINUTE);

Output:

+----------------------------------------------------+
| ADDDATE('2022-12-30 10:20:30', INTERVAL 20 MINUTE) |
+----------------------------------------------------+
| 2022-12-30 10:40:30                                |
+----------------------------------------------------+

You can also directly use the addition operator to implement the above statement:

SELECT '2022-12-30 10:20:30' + INTERVAL 20 MINUTE;

Output:

+--------------------------------------------+
| '2022-12-30 10:20:30' + INTERVAL 20 MINUTE |
+--------------------------------------------+
| 2022-12-30 10:40:30                        |
+--------------------------------------------+

SUBDATE()

The following statement is an example of subtracting a specified time period from a datetime value:

SELECT SUBDATE('2022-12-30 10:20:30', INTERVAL '5:10:20' HOUR_SECOND);

Output:

+----------------------------------------------------------------+
| SUBDATE('2022-12-30 10:20:30', INTERVAL '5:10:20' HOUR_SECOND) |
+----------------------------------------------------------------+
| 2022-12-30 05:10:10                                            |
+----------------------------------------------------------------+

You can also directly use the subtraction operator to implement the above statement:

SELECT  '2022-12-30 10:20:30' - INTERVAL '5:10:20' HOUR_SECOND;

Output:

+--------------------------------------------------------+
| '2022-12-30 10:20:30' - INTERVAL '5:10:20' HOUR_SECOND |
+--------------------------------------------------------+
| 2022-12-30 05:10:10                                    |
+--------------------------------------------------------+

Functions

You can use date and time units in the following MariaDB functions:

• EXTRACT()
• ADDDATE()
• SUBDATE()
• DATE_ADD()
• DATE_SUB()
• TIMESTAMPADD()
• TIMESTAMPDIFF()