Use AnalyticDB for MySQL date and time functions - AnalyticDB

You can use date and time functions to process date and time values. For example, you can invoke the functions to obtain the current date and time, format date and time, calculate time intervals, and extract specific parts of a date or time, such as the year, month, or day of a date or time. This topic describes the date and time function syntax and provides examples on how to use date and time functions in AnalyticDB for MySQL.

ADDDATE: adds a time interval to the specified date.
ADDTIME: adds a time interval to the specified time.
CONVERT_TZ: converts from the time zone specified by from_tz to the time zone specified by to_tz, and returns the resulting value.
CURDATE: returns the current date.
CURTIME: returns the current time.
DATE: returns the date part of a date or datetime expression.
DATE_FORMAT: returns a date as a string in the specified format.
SUBDATE/DATE_SUB: subtracts the specified interval from a date.
DATEDIFF: subtracts a date from another.
DAY/DAYOFMONTH: returns the day of the month for a date. Valid values: 1 to 31.
DAYNAME: returns the day of the week for a date, such as Monday.
DAYOFWEEK: returns the day of the week as a numeric value for a date.
DAYOFYEAR: returns the day of the year for a date.
EXTRACT: returns one or more separate parts of a date or time. For example, this function can return the year, month, day, hour, or minute of a date or time.
FROM_DAYS: returns a DATE value based on the parameter N that specifies the number of days.
FROM_UNIXTIME: returns a UNIX timestamp in the specified format.
HOUR: returns the hour part of a time.
LAST_DAY: returns the last day of the month for a date or datetime.
LOCALTIME/LOCALTIMESTAMP/NOW: returns the current timestamp.
MAKEDATE: returns a date based on the year and dayofyear parameters.
MAKETIME: returns a time based on the hour, minute, and second parameters.
MINUTE: returns the minute part of a time.
MONTH: returns the month of a date.
MONTHNAME: returns the full name of the month for a date.
PERIOD_ADD: adds a number of months specified by N to the period specified by P.
PERIOD_DIFF: returns the number of months between two periods.
QUARTER: returns the quarter of the year for a date.
SEC_TO_TIME: converts a quantity of seconds to a time.
SECOND: returns the second part of the specified time.
STR_TO_DATE: converts a string to a date or datetime in the specified format.
SUBTIME: subtracts the interval specified by expr2 from the time specified by expr1.
SYSDATE: returns the system time.
TIME: returns the time part of the date or datetime expression specified by expr as a string.
TIME_FORMAT: returns a time as a string in the specified format.
TIME_TO_SEC: converts a time to a quantity of seconds.
TIMEDIFF: subtracts the time specified by expr2 from the time specified by expr1.
TIMESTAMP: returns the date or datetime expression specified by expr as a datetime value.
TIMESTAMPADD: adds an interval to a date or datetime expression.
TIMESTAMPDIFF: returns a value after subtracting a date or datetime expression from another.
TO_DAYS: returns the number of days since year 0 based on the specified date.
TO_SECONDS: returns the number of seconds that have elapsed since year 0 based on the specified time.
UNIX_TIMESTAMP: returns the timestamp for the current time. The timestamp follows the UNIX time format. It is the number of seconds that have elapsed since 00:00:00 January 1, 1970 in UTC.
UTC_DATE: returns the UTC date.
UTC_TIME: returns the UTC time.
UTC_TIMESTAMP: returns the UTC timestamp.
WEEK: returns the week number for a date.
WEEKDAY: returns the weekday for a date.
WEEKOFYEAR: returns the calendar week for a date.
YEAR: returns the year part of a date.
YEARWEEK: returns the year and week of a date.

ADDDATE

ADDDATE(date,INTERVAL expr unit)
ADDDATE(expr,days)

Parameter types:

adddate(date, INTERVAL expr unit)
adddate(timestamp, INTERVAL expr unit)
adddate(datetime, INTERVAL expr unit)
adddate(varchar, INTERVAL expr unit)
adddate(date, varchar)
adddate(date, bigint)
adddate(datetime, bigint)
adddate(datetime, varchar)
adddate(timestamp, varchar)
adddate(timestamp, bigint)
adddate(varchar, bigint)
adddate(varchar, varchar)

Data type of the return value: DATE.
Description: This function adds a time interval to the specified date.
- Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month. Default value of unit: day.
- days and expr: This function returns the value of expr plus days.

Examples:

Example 1

select adddate(date '2022-01-22',interval '3' day);

The following information is returned:

+----------------------------------------------+
| adddate(DATE '2022-01-22', INTERVAL  '3' DAY)|
+----------------------------------------------+
| 2022-01-25                                   |

Example 2

select adddate(timestamp '2022-01-22',interval '3' day);

The following information is returned:

+---------------------------------------------------+
| adddate(TIMESTAMP '2022-01-22', INTERVAL  '3' DAY)|
+---------------------------------------------------+
| 2022-01-25 00:00:00                               |

Example 3

select adddate(datetime '2022-01-22',interval '3' day);

The following information is returned:

+--------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' DAY |
+--------------------------------------------------+
| 2022-01-25 00:00:00                              |

Example 4

select adddate('2022-01-22',interval '3' day);

The following information is returned:

+-----------------------------------------+
| adddate('2022-01-22', INTERVAL  '3' DAY)|
+-----------------------------------------+
| 2022-01-25                              |

Example 5

select adddate(datetime '2022-01-22',interval '3' second);

The following information is returned:

+-----------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' SECOND)|
+-----------------------------------------------------+
|                                 2022-01-22 00:00:03 |

Example 6

select adddate(datetime '2022-01-22',interval '3' minute);

The following information is returned:

+-----------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' MINUTE)|
+-----------------------------------------------------+
|                                 2022-01-22 00:03:00 |

Example 7

select adddate(datetime '2022-01-22',interval '3' hour);

The following information is returned:

+---------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' HOUR |
+---------------------------------------------------+
|                               2022-01-22 03:00:00 |

Example 8

select adddate(datetime '2022-01-22',interval '3' month);

The following information is returned:

+----------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' MONTH)|
+----------------------------------------------------+
|                                2022-04-22 00:00:00 |

Example 9

select adddate(datetime '2022-01-22',interval '3' year);

The following information is returned:

+---------------------------------------------------+
| adddate(DATETIME '2022-01-22', INTERVAL  '3' YEAR)|
+---------------------------------------------------+
|                               2025-01-22 00:00:00 |

Example 10

select adddate(datetime '2022-01-22',interval '01:01:10' hour_second) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-22 01:01:10 |

Example 11

select adddate(datetime '2022-01-22',interval '00:10' hour_minute) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-22 00:10:00 |

Example 12

select adddate(datetime '2022-01-22',interval '1 01:01:10' day_second) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-23 01:01:10 |

Example 13

select adddate(datetime '2022-01-22',interval '01:10' minute_second) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-22 00:01:10 |

Example 14

select adddate(datetime '2022-01-22',interval '1 01:01' day_minute) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-23 01:01:00 |

Example 15

select adddate(datetime '2022-01-22',interval '1 01' day_hour) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-23 01:00:00 |

Example 16

select adddate(datetime '2022-01-22 12:32:1',interval '2 2' year_month) as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2024-03-22 12:32:01 |

Example 17

select adddate('2022-01-22','3');

The following information is returned:

+---------------------------+
| adddate('2022-01-22', '3')|
+---------------------------+
| 2022-01-25                |

Example 18

select adddate('2022-01-22',3);

The following information is returned:

+-------------------------+
| adddate('2022-01-22', 3)|
+-------------------------+
| 2022-01-25              |

Example 19

select adddate(datetime '2022-01-22 12:12:32',3);

The following information is returned:

+-------------------------------------------+
| adddate(DATETIME '2022-01-22 12:12:32', 3)|
+-------------------------------------------+
|                       2022-01-25 12:12:32 |

Example 20

select adddate(datetime '2022-01-22 12:12:32','3');

The following information is returned:

+---------------------------------------------+
| adddate(DATETIME '2022-01-22 12:12:32', '3')|
+---------------------------------------------+
|                         2022-01-25 12:12:32 |

Example 21

select adddate(timestamp '2022-01-22 12:12:32','3');

The following information is returned:

+----------------------------------------------+
| adddate(TIMESTAMP '2022-01-22 12:12:32', '3')|
+----------------------------------------------+
|                          2022-01-25 12:12:32 |

Example 22

select adddate(timestamp '2022-01-22 12:12:32',3);

The following information is returned:

+--------------------------------------------+
| adddate(TIMESTAMP '2022-01-22 12:12:32', 3)|
+--------------------------------------------+
|                        2022-01-25 12:12:32 |

Example 23

select adddate('2022-01-22 12:12:32',3);

The following information is returned:

+----------------------------------+
| adddate('2022-01-22 12:12:32', 3)|
+----------------------------------+
| 2022-01-25 12:12:32              |

Example 24

select adddate('2022-01-22 12:12:32','3');

The following information is returned:

+------------------------------------+
| adddate('2022-01-22 12:12:32', '3')|
+------------------------------------+
| 2022-01-25 12:12:32                |

ADDTIME

ADDTIME(expr1,expr2)

Description: This function adds the time specified by expr2 to the time specified by expr1 and returns the result.

Parameter types:

addtime(date,varchar)
addtime(time,varchar)
addtime(datetime,varchar)
addtime(timestamp,varchar)
addtime(varchar,varchar)

Data type of the return value: VARCHAR.

Examples:

Example 1

select addtime(date '2022-01-01','01:01:01');

The following information is returned:

+----------------------------------------+
| addtime(DATE '2022-01-01', '01:01:01') |
+----------------------------------------+
| 2022-01-01 01:01:01                    |

Example 2

select addtime(time '01:00:00','01:01:01');

The following information is returned:

+--------------------------------------+
| addtime(TIME '01:00:00', '01:01:01') |
+--------------------------------------+
| 02:01:01                             |

Example 3

select addtime(datetime '2022-01-22 00:00:00','01:01:01');

The following information is returned:

+----------------------------------------------------+
| addtime(DATETIME '2022-01-22 00:00:00', '01:01:01')|
+----------------------------------------------------+
| 2022-01-22 01:01:01                                |

Example 4

select addtime(timestamp '2022-01-22 00:00:00','01:01:01');

The following information is returned:

+-----------------------------------------------------+
| addtime(TIMESTAMP '2022-01-22 00:00:00', '01:01:01')|
+-----------------------------------------------------+
| 2022-01-22 01:01:01                                 |

Example 5

select addtime('2022-01-22 00:00:00','01:01:01');

The following information is returned:

+-------------------------------------------+
| addtime('2022-01-22 00:00:00', '01:01:01')|
+-------------------------------------------+
| 2022-01-22 01:01:01                       |

CONVERT_TZ

CONVERT_TZ(dt,from_tz,to_tz)

Description: This function converts a datetime value dt from the time zone specified by from_tz to the time zone specified by to_tz and returns the result.

Parameter types:

convert_tz(varchar, varchar, varchar)

Data type of the return value: DATETIME.

Examples:

Example 1

select convert_tz('2022-01-01 12:00:00','+00:00','+10:00');

The following information is returned:

+-------------------------------------------------------+
| convert_tz('2022-01-01 12:00:00', '+00:00', '+10:00') |
+-------------------------------------------------------+
|                                   2022-01-01 22:00:00 |

Example 2

select convert_tz('2022-01-01 12:00:00','GMT','MET');

The following information is returned:

+-------------------------------------------------+
| convert_tz('2022-01-01 12:00:00', 'GMT', 'MET') |
+-------------------------------------------------+
|                             2022-01-01 13:00:00 |

CURDATE

CURDATE()

Description: This function returns the current date.
Data type of the return value: DATE.

Example:

select curdate;

The following information is returned:

+------------+
| curdate()  |
+------------+
| 2022-01-01 |

CURTIME

CURTIME()

Description: This function returns the current time.
Data type of the return value: TIME.

Example:

select curtime();

The following information is returned:

+--------------+
| curtime()    |
+--------------+
| 14:39:22.109 |

DATE

DATE(expr)

Description: This function returns the date part of a date or datetime expression.

Parameter types:

date(timestamp)
date(datetime)
date(varchar)

Data type of the return value: DATE.

Examples:

Example 1

select date(timestamp '2022-01-01 01:02:03');

The following information is returned:

+---------------------------------------+
| date(TIMESTAMP '2022-01-01 01:02:03') |
+---------------------------------------+
| 2022-01-01                            |

Example 2

select date(datetime '2022-01-01 01:02:03');

The following information is returned:

+--------------------------------------+
| date(DATETIME '2022-01-01 01:02:03') |
+--------------------------------------+
| 2022-01-01                           |

Example 3

select date('2022-01-01 01:02:03');

The following information is returned:

+-----------------------------+
| date('2022-01-01 01:02:03') |
+-----------------------------+
| 2022-01-01                  |

DATE_FORMAT

DATE_FORMAT(date,format)

Description: This function returns a date as a string in the specified format. The following table describes the format specifiers.

%a	The abbreviated name of the day of the week. Valid values: Sun to Sat.
%b	The abbreviated name of the month. Valid values: Jan to Dec.
%c	The month in the numeric format. Valid values: 0 to 12.
%d	The day of the month in the numeric format. Valid values: 00 to 31.
%e	The day of the month in the numeric format. Valid values: 0 to 31.
%f	The microseconds. Valid values: 000000 to 999999.
%H	The hour. Valid values: 00 to 23.
%h	The hour. Valid values: 01 to 12.
%I	The hour. Valid values: 01 to 12.
%i	The minutes in the numeric format. Valid values: 00 to 59.
%j	The day of the year. Valid values: 001 to 366.
%k	The hour. Valid values: 0 to 23.
%l	The hour. Valid values: 1 to 12.
%M	The name of the month. Valid values: January to December.
%m	The month in the numeric format. Valid values: 00 to 12.
%p	The abbreviation for the 12-hour period of the day. Valid values: AM and PM.
%r	The time in the 12-hour format (hh:mm:ss AM or hh:mm:ss PM).
%S	The second. Valid values: 00 to 59.
%s	The second. Valid values: 00 to 59.
%T	The time in the 24-hour format (hh:mm:ss).
%v	The number of the week in the year. This specifier applies to WEEK() mode 3 and is used with %x. Note Monday is considered the first day of the week.
%W	The name of the weekday. Valid values: Sunday to Saturday.
%x	The year of the week in the numeric format. This specifier is used with %v, and the value contains four digits. Note Monday is considered the first day of the week.
%Y	The year in the four-digit format.
%y	The year in the two-digit format.
%%	The percent sign (%).

Parameter types:

date_format(timestamp, varchar)
date_format(varchar, varchar) 
date_format(datetime, varchar)
date_format(date, varchar)

Data type of the return value: VARCHAR.

Examples:

Example 1

select date_format(timestamp '2022-01-27 13:23:00', '%W %M %Y')as result;

The following information is returned:

+-----------------------+
| result                |
+-----------------------+
| Thursday January 2022 |

Example 2

select date_format('2022-01-27 13:23:00', '%W %M %Y')as result;

The following information is returned:

+-----------------------+
| result                |
+-----------------------+
| Thursday January 2022 |

Example 3

select date_format(datetime '2022-01-27 13:23:00', '%W %M %Y')as result;

The following information is returned:

+-----------------------+
| result                |
+-----------------------+
| Thursday January 2022 |

Example 4

select date_format(date '2022-01-27', '%W %M %Y')as result;

The following information is returned:

+-----------------------+
| result                |
+-----------------------+
| Thursday January 2022 |

SUBDATE/DATE_SUB

DATE_SUB(date,INTERVAL expr unit)

Description: This function subtracts the interval specified by INTERVAL from the date specified by date.
Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month. Default value of unit: day.

Parameter types:

subdate(date, INTERVAL expr unit)
subdate(timestamp, INTERVAL expr unit)
subdate(datetime, INTERVAL expr unit)
subdate(varchar, INTERVAL expr unit)
subdate(date, bigint)
subdate(date, varchar)
subdate(datetime, bigint)
subdate(datetime, varchar)
subdate(timestamp, bigint)
subdate(timestamp, varchar)
subdate(varchar, bigint)
subdate(varchar, varchar)

Data type of the return value: DATE.

Examples:

Example 1

select date_sub(date '2022-01-22',interval '3' day);

The following information is returned:

+-----------------------------------------------+
| date_sub(DATE '2022-01-22', INTERVAL  '3' DAY)|
+-----------------------------------------------+
| 2022-01-19                                    |

Example 2

select date_sub(timestamp '2022-01-22 00:00:00',interval '3' day)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-19 00:00:00 |

Example 3

select date_sub(datetime '2022-01-22 00:00:00',interval '3' day)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-19 00:00:00 |

Example 4

select date_sub('2022-01-22 00:00:00',interval '3' day);

The following information is returned:

+---------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' DAY)|
+---------------------------------------------------+
| 2022-01-19 00:00:00                               |

Example 5

select date_sub('2022-01-22 00:00:00',interval '3' second);

The following information is returned:

+------------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' SECOND)|
+------------------------------------------------------+
| 2022-01-21 23:59:57                                  |

Example 6

select date_sub('2022-01-22 00:00:00',interval '3' minute);

The following information is returned:

+------------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' MINUTE)|
+------------------------------------------------------+
| 2022-01-21 23:57:00                                  |

Example 7

select date_sub('2022-01-22 00:00:00',interval '3' hour);

The following information is returned:

+----------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' HOUR)|
+----------------------------------------------------+
| 2022-01-21 21:00:00                                |

Example 8

select date_sub('2022-01-22 00:00:00',interval '3' month);

The following information is returned:

+-----------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' MONTH)|
+-----------------------------------------------------+
| 2021-10-22 00:00:00                                 |

Example 9

select date_sub('2022-01-22 00:00:00',interval '3' year);

The following information is returned:

+----------------------------------------------------+
| date_sub('2022-01-22 00:00:00', INTERVAL  '3' YEAR)|
+----------------------------------------------------+
| 2019-01-22 00:00:00                                |

Example 10

select date_sub('2022-01-22 00:00:00',interval '01:10' minute_second)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-21 23:58:50 |

Example 11

select date_sub('2022-01-22 00:00:00',interval '01:01:10' hour_second)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-21 22:58:50 |

Example 12

select date_sub('2022-01-22 00:00:00',interval '01:01' hour_minute)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-21 22:59:00 |

Example 13

select date_sub('2022-01-22 00:00:00',interval '1 01:01:10' day_second)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-20 22:58:50 |

Example 14

select date_sub('2022-01-22 00:00:00',interval '1 01:01' day_minute)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-20 22:59:00 |

Example 15

select date_sub('2022-01-22 00:00:00',interval '1 01' day_hour)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-20 23:00:00 |

Example 16

select date_sub('2022-01-22 00:00:00',interval '2 2' year_month)as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2019-11-22 00:00:00 |

Example 17

select date_sub(date '2022-01-22 00:00:00',3);

The following information is returned:

+----------------------------------------+
| date_sub(DATE '2022-01-22 00:00:00', 3)|
+----------------------------------------+
| 2022-01-19                             |

Example 18

select date_sub(date '2022-01-22 00:00:00','3');

The following information is returned:

+------------------------------------------+
| date_sub(DATE '2022-01-22 00:00:00', '3')|
+------------------------------------------+
| 2022-01-19                               |

Example 19

select date_sub(datetime '2022-01-22 00:00:00',3);

The following information is returned:

+--------------------------------------------+
| date_sub(DATETIME '2022-01-22 00:00:00', 3)|
+--------------------------------------------+
|                        2022-01-19 00:00:00 |

Example 20

select date_sub(datetime '2022-01-22 00:00:00','3');

The following information is returned:

+----------------------------------------------+
| date_sub(DATETIME '2022-01-22 00:00:00', '3')|
+----------------------------------------------+
|                          2022-01-19 00:00:00 |

Example 21

select date_sub(timestamp '2022-01-22 00:00:00',3);

The following information is returned:

+---------------------------------------------+
| date_sub(TIMESTAMP '2022-01-22 00:00:00', 3)|
+---------------------------------------------+
|                         2022-01-19 00:00:00 |

Example 22

select date_sub(timestamp '2022-01-22 00:00:00','3');

The following information is returned:

+-----------------------------------------------+
| date_sub(TIMESTAMP '2022-01-22 00:00:00', '3')|
+-----------------------------------------------+
|                           2022-01-19 00:00:00 |

Example 23

select date_sub('2022-01-22 00:00:00',3);

The following information is returned:

+-----------------------------------+
| date_sub('2022-01-22 00:00:00', 3)|
+-----------------------------------+
| 2022-01-19 00:00:00               |

Example 24

select date_sub('2022-01-22 00:00:00','3');

The following information is returned:

+-------------------------------------+
| date_sub('2022-01-22 00:00:00', '3')|
+-------------------------------------+
| 2022-01-19 00:00:00                 |

DATEDIFF

DATEDIFF(expr1,expr2)

Description: This function subtracts the date specified by expr2 from the date specified by expr1.

Parameter types:

datediff(varchar, varchar) 
datediff(datetime, varchar)
datediff(varchar, datetime)
datediff(datetime, datetime)
datediff(varchar, timestamp)
datediff(timestamp, timestamp)
datediff(timestamp, varchar)
datediff(date, date)
datediff(date, varchar)
datediff(varchar, date)

Data type of the return value: BIGINT.

Examples:

Example 1

select datediff('2022-01-22 23:59:59','2022-1-21');

The following information is returned:

+-----------------------------------------------+
| datediff('2022-01-22 23:59:59','2022-1-21')   |
+-----------------------------------------------+
|                                             1 |

Example 2

select datediff(datetime '2022-01-22 23:59:59','2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 3

select datediff('2022-01-22 23:59:59',datetime '2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 4

select datediff(datetime '2022-01-22 23:59:59',datetime '2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 5

select datediff('2022-01-22 23:59:59',timestamp '2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 6

select datediff(timestamp '2022-01-22 23:59:59',timestamp '2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 7

select datediff(timestamp '2022-01-22 23:59:59','2022-1-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 8

select datediff(date '2022-01-22 23:59:59',date '2022-01-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 9

select datediff(date '2022-01-22 23:59:59','2022-01-21')as result;

The following information is returned:

+--------+
| result |
+--------+
|      1 |

Example 10

select datediff('2022-01-22',date '2021-01-21');

The following information is returned:

+-------------------------------------------+
| datediff('2022-01-22', DATE '2021-01-21') |
+-------------------------------------------+
|                                      366  |

DAY/DAYOFMONTH

DAY(date)
DAYOFMONTH(date)

Description: This function returns the day of the month for the date specified by date. Valid values: 1 to 31.

Parameter types:

dayofmonth(timestamp)
dayofmonth(datetime)
dayofmonth(date)
dayofmonth(time)
dayofmonth(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select dayofmonth(timestamp '2022-01-22 12:23:09');

The following information is returned:

+---------------------------------------------+
| dayofmonth(TIMESTAMP '2022-01-22 12:23:09') |
+---------------------------------------------+
|                                          22 |

Example 2

select dayofmonth(date '2022-01-22');

The following information is returned:

+-------------------------------+
| dayofmonth(DATE '2022-01-22') |
+-------------------------------+
|                            22 |

Example 3

select dayofmonth(time '17:01:10');

The following information that shows the current date is returned:

+-----------------------------+
| dayofmonth(TIME '17:01:10') |
+-----------------------------+
|                          22 |

Example 4

select dayofmonth(datetime '2022-01-22 00:00:00');

The following information is returned:

+--------------------------------------------+
| dayofmonth(DATETIME '2022-01-22 00:00:00') |
+--------------------------------------------+
|                                         22 |

Example 5

select day('2022-01-22');

The following information is returned:

+-------------------+
| day('2022-01-22') |
+-------------------+
|                22 |

DAYNAME

DAYNAME(date)

Description: This function returns the day of the week for a date, such as Monday.

Parameter types:

dayname(timestamp)
dayname(datetime)
dayname(date)
dayname(varchar)

Data type of the return value: VARCHAR.

Examples:

Example 1

select dayname(timestamp '2022-01-22 00:00:00');

The following information is returned:

+------------------------------------------+
| dayname(TIMESTAMP '2022-01-22 00:00:00') |
+------------------------------------------+
| Saturday                                 |

Example 2

select dayname(datetime '2022-01-22 00:00:00');

The following information is returned:

+-----------------------------------------+
| dayname(DATETIME '2022-01-22 00:00:00') |
+-----------------------------------------+
| Saturday                                |

Example 3

select dayname(date '2022-01-22');

The following information is returned:

+----------------------------+
| dayname(DATE '2022-01-22') |
+----------------------------+
| Saturday                   |

Example 4

select dayname('2022-01-22');

The following information is returned:

+-----------------------+
| dayname('2022-01-22') |
+-----------------------+
| Saturday              |

DAYOFWEEK

DAYOFWEEK(date)

Description: This function returns the day of the week as a numeric value for a date, where 1 is for Sunday, 2 for Monday, and 7 for Saturday.

Parameter types:

dayofweek(timestamp)
dayofweek(datetime)
dayofweek(date)
dayofweek(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select dayofweek(timestamp '2022-01-22 00:00:00');

The following information is returned:

+--------------------------------------------+
| dayofweek(TIMESTAMP '2022-01-22 00:00:00') |
+--------------------------------------------+
|                                          7 |

Example 2

select dayofweek(datetime '2022-01-22 00:00:00');

The following information is returned:

+-------------------------------------------+
| dayofweek(DATETIME '2022-01-22 00:00:00') |
+-------------------------------------------+
|                                         7 |

Example 3

select dayofweek(date '2022-01-22');

The following information is returned:

+------------------------------+
| dayofweek(DATE '2022-01-22') |
+------------------------------+
|                            7 |

Example 4

select dayofweek('2022-01-22');

The following information is returned:

+-------------------------+
| dayofweek('2022-01-22') |
+-------------------------+
|                       7 |

DAYOFYEAR

DAYOFYEAR(date)

Description: This function returns the day of the year for a date. Valid values: 1 to 366.

Parameter types:

dayofyear(timestamp)
dayofyear(datetime) 
dayofyear(date) 
dayofyear(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select dayofyear(timestamp '2022-02-01 00:12:12');

The following information is returned:

+--------------------------------------------+
| dayofyear(TIMESTAMP '2022-02-01 00:12:12') |
+--------------------------------------------+
|                                         32 |

Example 2

select dayofyear(datetime '2022-02-01 00:12:12');

The following information is returned:

+-------------------------------------------+
| dayofyear(DATETIME '2022-02-01 00:12:12') |
+-------------------------------------------+
|                                        32 |

Example 3

select dayofyear(date '2022-02-01');

The following information is returned:

+------------------------------+
| dayofyear(DATE '2022-02-01') |
+------------------------------+
|                           32 |

Example 4

select dayofyear('2022-02-01');

The following information is returned:

+-------------------------+
| dayofyear('2022-02-01') |
+-------------------------+
|                      32 |

EXTRACT

EXTRACT(unit FROM date)

Description: This function returns one or more separate parts of a date or time in the specified unit. For example, this function can return the year, month, day, hour, or minute of a date or time.
Valid values of unit: second, minute, hour, day, month, year, minute_second, hour_second, hour_minute, day_second, day_minute, day_hour, and year_month.
Supported input parameter types: VARCHAR, TIMESTAMP, DATETIME, and TIME.
Data type of the return value: BIGINT.

Examples:

Example 1

select extract(second from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|    34 |

Example 2

select extract(minute from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|    12 |

Example 3

select extract(hour from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|     0 |

Example 4

select extract(day from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|    22 |

Example 5

select extract(month from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|     1 |

Example 6

select extract(year from timestamp '2022-01-22');

The following information is returned:

+-------+
| _col0 |
+-------+
|  2022 |

Example 7

select extract(year from datetime  '2022-01-22');

The following information is returned:

+-------+
| _col0 |
+-------+
|  2022 |

Example 8

select extract(year from time '15:23:22');

The following information that shows the current year is returned.

+-------+
| _col0 |
+-------+
|  2022 |

Example 9

select extract(minute_second from '2022-01-22 00:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|  1234 |

Example 10

select extract(hour_second from '2022-01-22 12:12:34');

The following information is returned:

+--------+
| _col0  |
+--------+
| 121234 |

Example 11

select extract(hour_minute from '2022-01-22 12:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|  1212 |

Example 12

select extract(day_second from '2022-01-22 12:12:34');

The following information is returned:

+----------+
| _col0    |
+----------+
| 22121234 |

Example 13

select extract(day_minute from '2022-01-22 00:12:34');

The following information is returned:

+--------+
| _col0  |
+--------+
| 220012 |

Example 14

select extract(day_hour from '2022-01-22 12:12:34');

The following information is returned:

+-------+
| _col0 |
+-------+
|  2212 |

Example 15

select extract(year_month from '2022-01-22 00:12:34');

The following information is returned:

+--------+
| _col0  |
+--------+
| 202201 |

FROM_DAYS

FROM_DAYS(N)

Description: This function returns a DATE value based on the parameter N that specifies the number of days.
Parameter types:
```
from_days(varchar) 
from_days(bigint)
```
Data type of the return value: DATE.

Examples:

Example 1

select from_days(738565);

The following information is returned:

+-------------------+
| from_days(738565) |
+-------------------+
|  2022-02-14       |

Example 2

select from_days('738565');

The following information is returned:

+---------------------+
| from_days('738565') |
+---------------------+
|  2022-02-14         |

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp[,format])

Description: This function returns the UNIX timestamp specified by unix in the specified format.
The format parameter conforms to the format in the DATE_FORMAT function.

Parameter types:

from_unixtime(varchar, varchar)
from_unixtime(varchar)
from_unixtime(double, varchar)
from_unixtime(double)

Data type of the return value: DATETIME.

Examples:

Example 1

select from_unixtime('1647738565','%Y %M %h:%i:%s %x');

The following information is returned:

+--------------------------------------------------+
| from_unixtime('1647738565', '%Y %M %h:%i:%s %x') |
+--------------------------------------------------+
|    2022 March 09:09:25 2022                      |

Example 2

select from_unixtime('1647738565');

The following information is returned:

+-----------------------------+
| from_unixtime('1647738565') |
+-----------------------------+
|         2022-03-20 09:09:25 |

Example 3

select from_unixtime(1647738456);

The following information is returned:

+---------------------------+
| from_unixtime(1647738456) |
+---------------------------+
|       2022-03-20 09:07:36 |

Example 4

select from_unixtime(1647738456,'%Y %M %h:%i:%s %x');

The following information is returned:

+------------------------------------------------+
| from_unixtime(1647738456, '%Y %M %h:%i:%s %x') |
+------------------------------------------------+
|     2022 March 09:07:36 2022                   |

HOUR

HOUR(time)

Description: This function returns the hour part of a time.

Parameter types:

hour(timestamp)
hour(datetime)
hour(date)
hour(time)
hour(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select hour(timestamp '2022-01-22 10:05:03');

The following information is returned:

+---------------------------------------+
| hour(TIMESTAMP '2022-01-22 10:05:03') |
+---------------------------------------+
|                                    10 |

Example 2

select hour(datetime '2022-01-22 10:05:03');

The following information is returned:

+--------------------------------------+
| hour(DATETIME '2022-01-22 10:05:03') |
+--------------------------------------+
|                                   10 |

Example 3

select hour(date '2022-01-22');

The following information is returned:

+-------------------------+
| hour(DATE '2022-01-22') |
+-------------------------+
|                       0 |

Example 4

select hour(time '10:05:03');

The following information is returned:

+-----------------------+
| hour(TIME '10:05:03') |
+-----------------------+
|                    10 |

Example 5

select hour('10:05:03');

The following information is returned:

+------------------+
| hour('10:05:03') |
+------------------+
|       10         |

LAST_DAY

LAST_DAY(date)

Description: This function returns the last day of the month for a date or datetime.

Parameter types:

last_day(varchar)
last_day(timestamp)
last_day(datetime)
last_day(date)

Data type of the return value: DATE.

Examples:

Example 1

select last_day('2022-01-22');

The following information is returned:

+------------------------+
| last_day('2022-01-22') |
+------------------------+
| 2022-01-31             |

Example 2

select last_day(timestamp '2022-01-22 12:12:12');

The following information is returned:

+-------------------------------------------+
| last_day(TIMESTAMP '2022-01-22 12:12:12') |
+-------------------------------------------+
| 2022-01-31                                |

Example 3

select last_day(datetime '2022-01-22 12:12:12');

The following information is returned:

+------------------------------------------+
| last_day(DATETIME '2022-01-22 12:12:12') |
+------------------------------------------+
| 2022-01-31                               |

Example 4

select last_day(date '2022-01-22');

The following information is returned:

+-----------------------------+
| last_day(DATE '2022-01-22') |
+-----------------------------+
| 2022-01-31                  |

LOCALTIME/LOCALTIMESTAMP/NOW

localtime
localtime()
localtimestamp
localtimestamp()
now()

Description: This function returns the current timestamp.
Data type of the return value: DATETIME.

Examples:

Example 1

select now();

The following information is returned:

+---------------------+
| now()               |
+---------------------+
| 2022-01-22 16:28:37 |

Example 2

select localtime;

The following information is returned:

+---------------------+
| localtime()         |
+---------------------+
| 2022-01-22 16:28:37 |

Example 3

select localtime();

The following information is returned:

+---------------------+
| localtime()         |
+---------------------+
| 2022-01-22 16:28:37 |

Example 4

select localtimestamp;

The following information is returned:

+---------------------+
| localtimestamp()    |
+---------------------+
| 2022-01-22 17:28:37 |

Example 5

select localtimestamp();

The following information is returned:

+---------------------+
| localtimestamp()    |
+---------------------+
| 2022-01-22 17:38:13 |

MAKEDATE

MAKEDATE(year,dayofyear)

Description: This function returns a date based on the year and dayofyear parameters.

Parameter types:

makedate(bigint, bigint)
makedate(varchar, varchar)

Data type of the return value: DATE.

Examples:

Example 1

select makedate(2022,31), makedate(2022,32);

The following information is returned:

+--------------------+--------------------+
| makedate(2022, 31) | makedate(2022, 32) |
+--------------------+--------------------+
| 2022-01-31         | 2022-02-01         |

Example 2

select makedate('2022','31'), makedate('2022','32');

The following information is returned:

+------------------------+------------------------+
| makedate('2022', '31') | makedate('2022', '32') |
+------------------------+------------------------+
| 2022-01-31             | 2022-02-01             |

MAKETIME

MAKETIME(hour,minute,second)

Description: This function returns a time based on the hour, minute, and second parameters.

Parameter types:

maketime(bigint, bigint, bigint)
maketime(varchar, varchar, varchar)

Data type of the return value: TIME.

Examples:

Example 1

select maketime(12,15,30);

The following information is returned:

+----------------------+
| maketime(12, 15, 30) |
+----------------------+
| 12:15:30             |

Example 2

select maketime('12','15','30');

The following information is returned:

+----------------------------+
| maketime('12', '15', '30') |
+----------------------------+
| 12:15:30                   |

MINUTE

MINUTE(time)

Description: This function returns the minute part of a time.

Parameter types:

minute(timestamp)
minute(datetime)
minute(date)
minute(time)
minute(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select minute(timestamp '2022-02-03 10:05:03');

The following information is returned:

+-----------------------------------------+
| minute(TIMESTAMP '2022-02-03 10:05:03') |
+-----------------------------------------+
|                                       5 |

Example 2

select minute(datetime '2022-02-03 10:05:03');

The following information is returned:

+----------------------------------------+
| minute(DATETIME '2022-02-03 10:05:03') |
+----------------------------------------+
|                                      5 |

Example 3

select minute(date '2022-02-03');

The following information is returned:

+---------------------------+
| minute(DATE '2022-02-03') |
+---------------------------+
|                         0 |

Example 4

select minute(time '12:12:12');

The following information is returned:

+-------------------------+
| minute(TIME '12:12:12') |
+-------------------------+
|                      12 |

Example 5

select minute('2022-02-03 10:05:03');

The following information is returned:

+-------------------------------+
| minute('2022-02-03 10:05:03') |
+-------------------------------+
|                             5 |

MONTH

MONTH(date)

Description: This function returns the month part of a date.

Parameter types:

month(timestamp)
month(datetime)
month(date)
month(time)
month(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select month(timestamp '2022-02-03 00:00:00');

The following information is returned:

+----------------------------------------+
| month(TIMESTAMP '2022-02-03 00:00:00') |
+----------------------------------------+
|                                      2 |

Example 2

select month(datetime '2022-02-03 00:00:00');

The following information is returned:

+---------------------------------------+
| month(DATETIME '2022-02-03 00:00:00') |
+---------------------------------------+
|                                     2 |

Example 3

select month(date '2022-02-03');

The following information is returned:

+--------------------------+
| month(DATE '2022-02-03') |
+--------------------------+
|                        2 |

Example 4

The MONTH function can also return the month when an SQL statement is executed. In the following example, 5 is returned for an SQL statement that is executed in May 2021.

select month(time '12:12:12');

The following information is returned:

+------------------------+
| month(TIME '12:12:12') |
+------------------------+
|                      5 |

Example 5

select month('2022-02-03');

The following information is returned:

+---------------------+
| month('2022-02-03') |
+---------------------+
|                   2 |

MONTHNAME

MONTHNAME(date)

Description: This function returns the full name of the month for a date.

Parameter types:

monthname(timestamp)
monthname(datetime)
monthname(date)
monthname(varchar)

Data type of the return value: VARCHAR.

Examples:

Example 1

select monthname(timestamp '2022-02-03 00:00:00');

The following information is returned:

+--------------------------------------------+
| monthname(TIMESTAMP '2022-02-03 00:00:00') |
+--------------------------------------------+
| February                                   |

Example 2

select monthname(datetime '2022-02-03 00:00:00');

The following information is returned:

+-------------------------------------------+
| monthname(DATETIME '2022-02-03 00:00:00') |
+-------------------------------------------+
| February                                  |

Example 3

select monthname(date '2022-02-03');

The following information is returned:

+------------------------------+
| monthname(DATE '2022-02-03') |
+------------------------------+
| February                     |

Example 4

select monthname('2022-02-03');

The following information is returned:

+-------------------------+
| monthname('2022-02-03') |
+-------------------------+
| February                |

PERIOD_ADD

PERIOD_ADD(P,N)

Description: This function adds a number of months specified by N to the period specified by P.

Parameter types:

period_add(bigint, bigint)
period_add(varchar, varchar) 
period_add(varchar, bigint)

Data type of the return value: BIGINT.

Examples:

Example 1

select period_add(202201,2);

The following information is returned:

+-----------------------+
| period_add(202201, 2) |
+-----------------------+
|                202203 |

Example 2

select period_add('202201','2');

The following information is returned:

+---------------------------+
| period_add('202201', '2') |
+---------------------------+
|                    202203 |

Example 3

select period_add('202201',2);

The following information is returned:

+-------------------------+
| period_add('202201', 2) |
+-------------------------+
|                  202203 |

PERIOD_DIFF

PERIOD_DIFF(P1,P2)

Description: This function returns the number of months between the two periods specified by P1 and P2.

Parameter types:

period_diff(bigint, bigint)
period_diff(varchar, varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select period_diff(202202,202103);

The following information is returned:

+----------------------------+
| period_diff(202202,202103) |
+----------------------------+
|                         11 |

Example 2

select period_diff('202202','202103');

The following information is returned:

+---------------------------------+
| period_diff('202202', '202103') |
+---------------------------------+
|                              11 |

QUARTER

QUARTER(date)

Description: This function returns the quarter of the year for a date. Valid values: 1 to 4.

Parameter types:

quarter(datetime)
quarter(varchar)
quarter(timestamp)
quarter(date)

Data type of the return value: BIGINT.

Examples:

Example 1

select quarter(datetime '2022-04-01 12:12:12');

The following information is returned:

+-----------------------------------------+
| quarter(DATETIME '2022-04-01 12:12:12') |
+-----------------------------------------+
|                                       2 |

Example 2

select quarter('2022-04-01');

The following information is returned:

+-----------------------+
| quarter('2022-04-01') |
+-----------------------+
|                     2 |

Example 3

select quarter(timestamp '2022-04-01 12:12:12');

The following information is returned:

+------------------------------------------+
| quarter(TIMESTAMP '2022-04-01 12:12:12') |
+------------------------------------------+
|                                        2 |

Example 4

select quarter(date '2022-04-01');

The following information is returned:

+----------------------------+
| quarter(DATE '2022-04-01') |
+----------------------------+
|                          2 |

SEC_TO_TIME

SEC_TO_TIME(seconds)

Description: This function converts a quantity of seconds specified by seconds to a time.

Parameter types:

sec_to_time(bigint)
sec_to_time(varchar)

Data type of the return value: TIME.

Examples:

Example 1

select sec_to_time(2378);

The following information is returned:

+-------------------+
| sec_to_time(2378) |
+-------------------+
| 00:39:38          |

Example 2

select sec_to_time('2378');

The following information is returned:

+---------------------+
| sec_to_time('2378') |
+---------------------+
| 00:39:38            |

SECOND

SECOND(time)

Description: This function returns the second part of the specified time. Valid values: 0 to 59.

Parameter types:

second(timestamp)
second(datetime)
second(date)
second(time)
second(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select second(timestamp '2022-03-12 12:13:14');

The following information is returned:

+-----------------------------------------+
| second(TIMESTAMP '2022-03-12 12:13:14') |
+-----------------------------------------+
|                                      14 |

Example 2

select second(datetime '2022-03-12 12:13:14');

The following information is returned:

+----------------------------------------+
| second(DATETIME '2022-03-12 12:13:14') |
+----------------------------------------+
|                                     14 |

Example 3

select second(date '2022-03-12');

The following information is returned:

+---------------------------+
| second(DATE '2022-03-12') |
+---------------------------+
|                         0 |

Example 3

select second(time '12:13:14');

The following information is returned:

+-------------------------+
| second(TIME '12:13:14') |
+-------------------------+
|                      14 |

Example 4

select second('12:12:23');

The following information is returned:

+--------------------+
| second('12:12:23') |
+--------------------+
|                 23 |

STR_TO_DATE

STR_TO_DATE(str,format)

Description: This function converts a string to a date or datetime in the specified format.
The format parameter conforms to the format in the DATE_FORMAT function.
Parameter types:
```
str_to_date(varchar, varchar)
```
Data type of the return value: DATETIME.

Examples:

Example 1

select str_to_date('2022-01-06 10:20:30','%Y-%m-%d %H:%i:%s') as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-06 10:20:30 |

SUBTIME

SUBTIME(expr1,expr2)

Description: This function subtracts the interval specified by expr2 from the time specified by expr1.

Parameter types:

subtime(date, varchar)
subtime(datetime, varchar)
subtime(timestamp, varchar)
subtime(time, varchar)
subtime(varchar, varchar)

Data type of the return value: DATETIME.

Examples:

Example 1

select subtime(date '2022-10-31','0:1:1');

The following information is returned:

+-------------------------------------+
| subtime(DATE '2022-10-31', '0:1:1') |
+-------------------------------------+
|                 2022-10-30 23:58:59 |

Example 2

select subtime(datetime '2022-10-31 12:12:12','0:1:1');

The following information is returned:

+--------------------------------------------------+
| subtime(DATETIME '2022-10-31 12:12:12', '0:1:1') |
+--------------------------------------------------+
|                              2022-10-31 12:11:11 |

Example 3

select subtime(timestamp '2022-10-31 12:12:12','0:1:1');

The following information is returned:

+---------------------------------------------------+
| subtime(TIMESTAMP '2022-10-31 12:12:12', '0:1:1') |
+---------------------------------------------------+
|                               2022-10-31 12:11:11 |

Example 4

select subtime(time '12:12:12','0:1:1');

The following information is returned:

+-----------------------------------+
| subtime(TIME '12:12:12', '0:1:1') |
+-----------------------------------+
| 12:11:11                          |
+-----------------------------------+

Example 5

select subtime('2022-10-31 23:59:59','0:1:1');

The following information is returned:

+-----------------------------------------+
| subtime('2022-10-31 23:59:59', '0:1:1') |
+-----------------------------------------+
| 2022-10-31 23:58:58                     |

SYSDATE

SYSDATE()

Description: This function returns the system time.
Data type of the return value: DATETIME.

Examples:

Example 1

select sysdate();

The following information is returned:

+---------------------+
| sysdate()           |
+---------------------+
| 2022-02-26 00:47:21 |

TIME

TIME(expr)

Description: This function returns the time part of the date or datetime expression specified by expr as a string.

Parameter types:

time(varchar)
time(datetime)
time(timestamp)

Data type of the return value: VARCHAR.

Examples:

Example 1

select time('2022-01-31 01:02:03');

The following information is returned:

+-----------------------------+
| time('2022-01-31 01:02:03') |
+-----------------------------+
| 01:02:03                    |

Example 2

select time(datetime '2022-01-31 01:02:03');

The following information is returned:

+--------------------------------------+
| time(DATETIME '2022-01-31 01:02:03') |
+--------------------------------------+
| 01:02:03                             |

Example 3

select time(timestamp '2022-01-31 01:02:03');

The following information is returned:

+---------------------------------------+
| time(TIMESTAMP '2022-01-31 01:02:03') |
+---------------------------------------+
| 01:02:03                              |

TIME_FORMAT

TIME_FORMAT(time,format)

Description: This function returns the time specified by time as a string in the specified format.
The format parameter conforms to the format in the DATE_FORMAT function.

Parameter types:

time_format(varchar, varchar) 
time_format(timestamp, varchar)
time_format(datetime, varchar)
time_format(time, varchar) 
time_format(date, varchar)

Data type of the return value: VARCHAR.

Examples:

Example 1

select time_format('12:00:00', '%H %k %h %I %l');

The following information is returned:

+-------------------------------------------+
| time_format('12:00:00', '%H %k %h %I %l') |
+-------------------------------------------+
| 12 12 12 12 12                            |

Example 2

select time_format(timestamp '2022-01-22 23:00:00','%H %k %h %I %l')as result;

The following information is returned:

+----------------+
| result         |
+----------------+
| 23 23 11 11 11 |

Example 3

select time_format(datetime '2022-01-22 23:00:00','%H %k %h %I %l')as result;

The following information is returned:

+----------------+
| result         |
+----------------+
| 23 23 11 11 11 |

Example 4

select time_format(time '23:00:00','%H %k %h %I %l');

The following information is returned:

+------------------------------------------------+
| time_format(TIME '23:00:00', '%H %k %h %I %l') |
+------------------------------------------------+
| 23 23 11 11 11                                 |

Example 5

select time_format(date '2022-01-22','%H %k %h %I %l');

The following information is returned:

+--------------------------------------------------+
| time_format(DATE '2022-01-22', '%H %k %h %I %l') |
+--------------------------------------------------+
| 00 0 12 12 12                                    |

TIME_TO_SEC

TIME_TO_SEC(time)

Description: This function converts the time specified by time to a quantity of seconds.

Parameter types:

time_to_sec(varchar)
time_to_sec(datetime)
time_to_sec(timestamp)
time_to_sec(date)
time_to_sec(time)

Data type of the return value: BIGINT.

Examples:

Example 1

select time_to_sec(datetime '2022-01-22 22:23:00');

The following information is returned:

+---------------------------------------------+
| time_to_sec(DATETIME '2022-01-22 22:23:00') |
+---------------------------------------------+
|                                       80580 |

Example 2

select time_to_sec(timestamp '2022-01-22 22:23:00');

The following information is returned:

+----------------------------------------------+
| time_to_sec(TIMESTAMP '2022-01-22 22:23:00') |
+----------------------------------------------+
|                                        80580 |

Example 3

select time_to_sec(date '2022-01-22');

The following information is returned:

+--------------------------------+
| time_to_sec(DATE '2022-01-22') |
+--------------------------------+
|                              0 |

Example 4

select time_to_sec(time '12:12:12');

The following information is returned:

+------------------------------+
| time_to_sec(TIME '12:12:12') |
+------------------------------+
|                        43932 |

Example 5

select time_to_sec('22:23:00');

The following information is returned:

+-------------------------+
| time_to_sec('22:23:00') |
+-------------------------+
|                   80580 |

TIMEDIFF

TIMEDIFF(expr1,expr2)

Description: This function subtracts the time specified by expr2 from the time specified by expr1. This function is equivalent to the SUBTIME function.

Parameter types:

timediff(time, varchar)
timediff(time, time)
timediff(varchar, varchar)

Data type of the return value: DATETIME.

Examples:

Example 1

select timediff(time '12:00:00','10:00:00');

The following information is returned:

+---------------------------------------+
| timediff(TIME '12:00:00', '10:00:00') |
+---------------------------------------+
| 02:00:00                              |

Example 2

select timediff('12:00:00','10:00:00');

The following information is returned:

+----------------------------------+
| timediff('12:00:00', '10:00:00') |
+----------------------------------+
| 02:00:00                         |

Example 3

select timediff(time '12:00:00',time '10:00:00');

The following information is returned:

+--------------------------------------------+
| timediff(TIME '12:00:00', TIME '10:00:00') |
+--------------------------------------------+
| 02:00:00                                   |

TIMESTAMP

TIMESTAMP(expr)

Description: This function returns the date or datetime expression specified by expr as a datetime value.

Parameter types:

timestamp(date)
timestamp(varchar)

Data type of the return value: DATETIME.

Examples:

Example 1

select timestamp(date '2022-01-22');

The following information is returned:

+------------------------------+
| timestamp(DATE '2022-01-22') |
+------------------------------+
| 2022-01-22 00:00:00          |

Example 2

select timestamp('2022-01-22');

The following information is returned:

+-------------------------+
| timestamp('2022-01-22') |
+-------------------------+
|     2022-01-22 00:00:00 |

TIMESTAMPADD

TIMESTAMPADD(unit,interval,datetime_expr)

Description: This function adds the interval specified by interval to the date or datetime expression specified by datetime_expr. unit specifies the unit of the interval.
Valid values of unit: second, minute, hour, day, week, month, quarter, and year.

Parameter types:

timestampadd(varchar, varchar, timestamp) 
timestampadd(varchar, bigint, timestamp)
timestampadd(varchar, varchar, date)
timestampadd(varchar, bigint, date)
timestampadd(varchar, varchar, datetime)
timestampadd(varchar, bigint, datetime) 
timestampadd(varchar, varchar, varchar)
timestampadd(varchar, bigint, varchar)

Data type of the return value: DATETIME.

Examples:

Example 1

select timestampadd(second,'1',timestamp '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 2

select timestampadd(second,1,timestamp '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 3

select timestampadd(second,'1',date '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 00:00:01 |

Example 4

select timestampadd(second,1,date '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 00:00:01 |

Example 5

select timestampadd(second,'1',datetime '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 6

select timestampadd(second,1,datetime '2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 7

select timestampadd(second,'1','2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 8

select timestampadd(second,1,'2022-01-02 12:12:12')as result;

The following information is returned:

+---------------------+
| result              |
+---------------------+
| 2022-01-02 12:12:13 |

Example 9

select timestampadd(second,1,'2022-01-02 12:12:12');

The following information is returned:

+--------------------------------------------------+
| timestampadd('second', 1, '2022-01-02 12:12:12') |
+--------------------------------------------------+
| 2022-01-02 12:12:13                              |

Example 10

select timestampadd(minute,8820,'2022-02-24 09:00:00');

The following information is returned:

+-----------------------------------------------------+
| timestampadd('MINUTE', 8820, '2022-02-24 09:00:00') |
+-----------------------------------------------------+
| 2022-03-02 12:00:00                                 |

Example 11

select timestampadd(hour,1,'2022-01-02 12:12:12');

The following information is returned:

+------------------------------------------------+
| timestampadd('hour', 1, '2022-01-02 12:12:12') |
+------------------------------------------------+
| 2022-01-02 13:12:12                            |

Example 12

select timestampadd(day,1,'2022-01-02 12:12:12');

The following information is returned:

+-----------------------------------------------+
| timestampadd('day', 1, '2022-01-02 12:12:12') |
+-----------------------------------------------+
| 2022-01-03 12:12:12                           |

Example 13

select timestampadd(week,1,'2022-01-02 12:12:12');

The following information is returned:

+------------------------------------------------+
| timestampadd('week', 1, '2022-01-02 12:12:12') |
+------------------------------------------------+
| 2022-01-09 12:12:12                            |

Example 14

select timestampadd(month,1,'2022-01-02 12:12:12');

The following information is returned:

+-------------------------------------------------+
| timestampadd('month', 1, '2022-01-02 12:12:12') |
+-------------------------------------------------+
| 2022-02-02 12:12:12                             |

Example 15

select timestampadd(year,1,'2022-01-02 12:12:12');

The following information is returned:

+------------------------------------------------+
| timestampadd('year', 1, '2022-01-02 12:12:12') |
+------------------------------------------------+
| 2023-01-02 12:12:12                            |

Example 16

select timestampadd(quarter,1,'2022-01-02 12:12:12');

The following information is returned:

+---------------------------------------------------+
| timestampadd('quarter', 1, '2022-01-02 12:12:12') |
+---------------------------------------------------+
| 2022-04-02 12:12:12                               |

TIMESTAMPDIFF

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Description: This function returns a value after subtracting a date or datetime expression specified by datetime_expr1 from another specified by datetime_expr2. unit specifies the unit of the result.
Valid values of unit: second, minute, hour, day, week, month, quarter, and year.
This function can be used in the same way as TIMESTAMPADD.

Parameter types:

timestampdiff(varchar, timestamp, timestamp)
timestampdiff(varchar, date, date)
timestampdiff(varchar, datetime, datetime)
timestampdiff(varchar, varchar, varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select timestampdiff(second,datetime '2022-02-01 10:12:13',datetime '2022-03-01 10:12:13')as result;

The following information is returned:

+----------+
|  result  |
+----------+
|  2419200 |

Example 2

select timestampdiff(minute,datetime '2022-02-01 10:12:13',datetime '2022-03-01 10:12:13')as result;

The following information is returned:

+---------+
|  result |
+---------+
|  40320  |

Example 3

select timestampdiff(hour,datetime '2022-02-01 10:12:13',datetime '2022-03-01 10:12:13')as result;

The following information is returned:

+--------+
| result |
+--------+
|   672  |

Example 4

select timestampdiff(day,timestamp '2022-02-01',timestamp '2022-03-01')as result;

The following information is returned:

+--------+
| result |
+--------+
|    28  |

Example 5

select timestampdiff(day,date '2022-02-01',date '2022-03-01');

The following information is returned:

+------------------------------------------------------------+
| timestampdiff('day', DATE '2022-02-01', DATE '2022-03-01') |
+------------------------------------------------------------+
|                                                         28 |

Example 6

select timestampdiff(day,datetime '2022-02-01 10:12:13',datetime '2022-03-01 10:12:13')as result;

The following information is returned:

+--------+
| result |
+--------+
|    28  |

Example 7

select timestampdiff(day,'2022-02-01','2022-03-01');

The following information is returned:

+--------------------------------------------------+
| timestampdiff('day', '2022-02-01', '2022-03-01') |
+--------------------------------------------------+
|                                              28  |

Example 8

select timestampdiff(week,'2022-02-01','2022-03-01');

The following information is returned:

+---------------------------------------------------+
| timestampdiff('week', '2022-02-01', '2022-03-01') |
+---------------------------------------------------+
|                                                4  |

Example 9

select timestampdiff(quarter,'2022-02-01','2022-03-01');

The following information is returned:

+------------------------------------------------------+
| timestampdiff('quarter', '2022-02-01', '2022-03-01') |
+------------------------------------------------------+
|                                                   0  |

Example 10

select timestampdiff(month,'2022-02-01','2022-03-01');

The following information is returned:

+----------------------------------------------------+
| timestampdiff('month', '2022-02-01', '2022-03-01') |
+----------------------------------------------------+
|                                                 1  |

Example 11

select timestampdiff(year,datetime '2022-02-01 10:12:13',datetime '2020-05-01 10:12:13')as result;

The following information is returned:

+--------+
| result |
+--------+
|     -1 |

TO_DAYS

TO_DAYS(date)

Description: This function returns the number of days since year 0 based on the date specified by date.

Parameter types:

to_days(date)
to_days(time)
to_days(varchar)
to_days(timestamp)
to_days(datetime)

Data type of the return value: BIGINT.

Examples:

Example 1

select to_days(date '2022-02-12');

The following information is returned:

+----------------------------+
| to_days(DATE '2022-02-12') |
+----------------------------+
|                     738563 |

Example 2

select to_days(time '12:12:12');

The following information that shows the number of days since year 0 based on the current date is returned:

+--------------------------+
| to_days(TIME '12:12:12') |
+--------------------------+
|                   738563 |

Example 3

select to_days(now());

The following information is returned:

+----------------+
| to_days(now()) |
+----------------+
|         738563 |

Example 4

Note

This example is equivalent to Example 3.

select to_days(curdate());

The following information is returned:

+--------------------+
| to_days(curdate()) |
+--------------------+
|             738563 |

Example 5

select to_days(datetime '2022-02-12 12:12:12');

The following information is returned:

+-----------------------------------------+
| to_days(DATETIME '2022-02-12 12:12:12') |
+-----------------------------------------+
|                                  738563 |

Example 6

select to_days('2022-02-12 12:12:12');

The following information is returned:

+--------------------------------+
| to_days('2022-02-12 12:12:12') |
+--------------------------------+
|                         738563 |

Example 7

select to_days(timestamp '2022-02-12 12:12:12');

The following information is returned:

+------------------------------------------+
| to_days(TIMESTAMP '2022-02-12 12:12:12') |
+------------------------------------------+
|                                   738563 |

TO_SECONDS

TO_SECONDS(expr)

Description: This function returns the number of seconds that have elapsed since year 0 based on the time specified by expr.

Parameter types:

to_seconds(date)
to_seconds(datetime)
to_seconds(timestamp)
to_seconds(varchar)
to_seconds(time)

Data type of the return value: BIGINT.

Examples:

Example 1

select to_seconds(date '2022-02-02');

The following information is returned:

+-------------------------------+
| to_seconds(DATE '2022-02-02') |
+-------------------------------+
|                   63810979200 |

Example 2

select to_seconds(datetime '2022-02-02 09:09:00');

The following information is returned:

+--------------------------------------------+
| to_seconds(DATETIME '2022-02-02 09:09:00') |
+--------------------------------------------+
|                                63811012140 |

Example 3

select to_seconds(timestamp '2022-02-02 09:09:00');

The following information is returned:

+---------------------------------------------+
| to_seconds(TIMESTAMP '2022-02-02 09:09:00') |
+---------------------------------------------+
|                                 63811012140 |

Example 4

If you execute the following SQL statement, the system adds the value of curdate() to '09:09:00'.

select to_seconds(time '09:09:00');

The following information is returned:

+-----------------------------+
| to_seconds(TIME '09:09:00') |
+-----------------------------+
|                 63811012140 |

Example 5

select to_seconds('2022-02-02');

The following information is returned:

+--------------------------+
| to_seconds('2022-02-02') |
+--------------------------+
|              63810979200 |

UNIX_TIMESTAMP

UNIX_TIMESTAMP([date])

Description: UNIX_TIMESTAMP() returns the number of seconds that have elapsed since 00:00:00 January 1, 1970 in UTC to the current time. UNIX_TIMESTAMP(date) returns the number of seconds that have elapsed since 00:00:00 January 1, 1970 in UTC to the time specified by date.

Parameter types:

unix_timestamp()
unix_timestamp(varchar)
unix_timestamp(timestamp)
unix_timestamp(date)
unix_timestamp(datetime)

Data type of the return value: BIGINT.

Examples:

Example 1

select unix_timestamp();

The following information is returned:

+------------------+
| unix_timestamp() |
+------------------+
|       1654759686 |

Example 2

select unix_timestamp(timestamp '2022-02-08 12:12:12');

The following information is returned:

+-------------------------------------------------+
| unix_timestamp(TIMESTAMP '2022-02-08 12:12:12') |
+-------------------------------------------------+
|                                      1644293532 |

Example 3

select unix_timestamp(date '2022-02-08');

The following information is returned:

+-----------------------------------+
| unix_timestamp(DATE '2022-02-08') |
+-----------------------------------+
|                        1644249600 |

Example 4

select unix_timestamp(datetime '2022-02-08 12:12:12');

The following information is returned:

+------------------------------------------------+
| unix_timestamp(DATETIME '2022-02-08 12:12:12') |
+------------------------------------------------+
|                                     1644293532 |

Example 5

select unix_timestamp('2022-02-08 12:12:12');

The following information is returned:

+---------------------------------------+
| unix_timestamp('2022-02-08 12:12:12') |
+---------------------------------------+
|                            1644293532 |

UTC_DATE

UTC_DATE()

Description: This function returns the UTC date.
Data type of the return value: VARCHAR.

Example:

select utc_date();

The following information is returned:

+------------+
| utc_date() |
+------------+
| 2022-05-27 |

UTC_TIME

UTC_TIME()

Description: This function returns the UTC time.
Data type of the return value: VARCHAR.

Example:

select utc_time();

The following information is returned:

+------------+
| utc_time() |
+------------+
| 05:53:19   |

UTC_TIMESTAMP

utc_timestamp()

Description: This function returns the UTC timestamp.
Data type of the return value: VARCHAR.

Example:

select utc_timestamp();

The following information is returned:

+---------------------+
| utc_timestamp()     |
+---------------------+
| 2022-05-27 15:55:15 |

WEEK

WEEK(date[,mode])

Description: This function returns the week number for the date specified by date, which is the week of the year in which date falls.

date specifies the date for which you want to obtain the week number.
mode is an optional parameter that specifies the mode in which you want to calculate the week number. By default, the first day of the week is Sunday. You can also specify whether the week starts from Monday or Sunday. The following table describes the formats that mode supports.

mode	First day of the week	Valid values for the week number	Description
0	Sunday	0 to 53	The first week of the year starts from the first Sunday of the year. The week that includes the days before the first Sunday of the year is considered Week 0.
1	Monday	0 to 53	If the number of days from January 1 to the first Monday exceeds three days, the week that includes the days before the first Monday of the year is considered the first week of the year. Otherwise, the week that includes the days before the first Monday of the year is considered Week 0.
2	Sunday	1 to 53	The first week of the year starts from the first Sunday of the year. The week that includes the days before the first Sunday of the year is considered the last week of the previous year.
3	Monday	1 to 53	If the number of days from January 1 to the first Monday exceeds three days, the week that includes the days before the first Monday of the year is considered the first week of the year. Otherwise, the week that includes the days before the first Monday of the year is considered the last week of the previous year.
4	Sunday	0 to 53	If the number of days from January 1 to the first Sunday exceeds three days, the week that includes the days before the first Sunday of the year is considered the first week of the year. Otherwise, the week that includes the days before the first Sunday of the year is considered Week 0.
5	Monday	0 to 53	The first week of the year starts from the first Monday of the year. The week that includes the days before the first Monday of the year is considered Week 0.
6	Sunday	1 to 53	If the number of days from January 1 to the first Sunday exceeds three days, the week that includes the days before the first Sunday of the year is considered the first week of the year. Otherwise, the week that includes the days before the first Sunday of the year is considered the last week of the previous year.
7	Monday	1 to 53	The first week of the year starts from the first Monday of the year. The week that includes the days before the first Monday of the year is considered the last week of the previous year.

Parameter types:

week(varchar)
week(varchar, bigint)
week(date)
week(date, bigint)
week(datetime)
week(datetime, bigint)
week(timestamp)
week(timestamp, bigint)

Data type of the return value: BIGINT.

Examples:

Example 1

select week('2022-02-27');

The following information is returned:

+--------------------+
| week('2022-02-27') |
+--------------------+
|                 9  |

Example 2

select week('2022-02-20',1);

The following information is returned:

+-----------------------+
| week('2022-02-20', 1) |
+-----------------------+
|                     7 |

Example 3

select week(date '2022-02-20');

The following information is returned:

+-------------------------+
| week(DATE '2022-02-20') |
+-------------------------+
|                       8 |

Example 4

select week(date '2022-02-20',1);

The following information is returned:

+----------------------------+
| week(DATE '2022-02-20', 1) |
+----------------------------+
|                          7 |

Example 5

select week(datetime '2022-02-20 00:00:00',1);

The following information is returned:

+-----------------------------------------+
| week(DATETIME '2022-02-20 00:00:00', 1) |
+-----------------------------------------+
|                                       7 |

Example 6

select week(datetime '2022-02-20 00:00:00');

The following information is returned:

+--------------------------------------+
| week(DATETIME '2022-02-20 00:00:00') |
+--------------------------------------+
|                                    8 |

Example 7

select week(timestamp '2022-02-20 00:00:00');

The following information is returned:

+---------------------------------------+
| week(TIMESTAMP '2022-02-20 00:00:00') |
+---------------------------------------+
|                                     8 |

Example 8

select week(timestamp '2022-02-20 00:00:00',1);

The following information is returned:

+------------------------------------------+
| week(TIMESTAMP '2022-02-20 00:00:00', 1) |
+------------------------------------------+
|                                        7 |

WEEKDAY

WEEKDAY(date)

Description: This function returns the weekday for the date specified by date. The result is an integer that indicates the weekday. The following section lists result mappings: 0 = Monday, 1 = Tuesday, 2 = Wednesday, 3 = Thursday, 4 = Friday, 5 = Saturday, and 6 = Sunday.

Parameter types:

weekday(timestamp)
weekday(datetime)
weekday(date)
weekday(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select weekday(timestamp '2022-02-20 00:09:00');

The following information is returned:

+------------------------------------------+
| weekday(TIMESTAMP '2022-02-20 00:09:00') |
+------------------------------------------+
|                                        6 |

Example 2

select weekday(datetime '2022-02-20 00:09:00');

The following information is returned:

+-----------------------------------------+
| weekday(DATETIME '2022-02-20 00:09:00') |
+-----------------------------------------+
|                                       6 |

Example 3

select weekday(date '2022-02-20 00:09:00');

The following information is returned:

+-------------------------------------+
| weekday(DATE '2022-02-20 00:09:00') |
+-------------------------------------+
|                                   6 |

Example 4

select weekday('2022-02-20');

The following information is returned:

+-----------------------+
| weekday('2022-02-20') |
+-----------------------+
|                     6 |

WEEKOFYEAR

WEEKOFYEAR(date)

Description: This function returns the calendar week for the date specified by date. Valid values: 1 to 53.

Parameter types:

weekofyear(timestamp)
weekofyear(datetime)
weekofyear(date)
weekofyear(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select weekofyear(timestamp '2022-02-27 09:00:00');

The following information is returned:

+---------------------------------------------+
| weekofyear(TIMESTAMP '2022-02-27 09:00:00') |
+---------------------------------------------+
|                                          8  |

Example 2

select weekofyear(datetime '2022-02-27 09:00:00');

The following information is returned:

+--------------------------------------------+
| weekofyear(DATETIME '2022-02-27 09:00:00') |
+--------------------------------------------+
|                                         8  |

Example 3

select weekofyear(date '2022-02-27');

The following information is returned:

+-------------------------------+
| weekofyear(DATE '2022-02-27') |
+-------------------------------+
|                            8  |

Example 4

select weekofyear('2022-02-27');

The following information is returned:

+--------------------------+
| weekofyear('2022-02-27') |
+--------------------------+
|                       8  |

YEAR

YEAR(date)

Description: This function returns the year part of the date specified by date.

Parameter types:

year(timestamp)
year(datetime)
year(date)
year(time)
year(varchar)

Data type of the return value: BIGINT.

Examples:

Example 1

select year(timestamp '2022-02-27 00:00:00');

The following information is returned:

select year(timestamp '2019-05-27 00:00:00');
+---------------------------------------+
| year(TIMESTAMP '2022-02-27 00:00:00') |
+---------------------------------------+
|                                  2022 |

Example 2

select year(datetime '2022-02-27 00:00:00')

The following information is returned:

+--------------------------------------+
| year(DATETIME '2022-02-27 00:00:00') |
+--------------------------------------+
|                                 2022 |

Example 3

select year(date '2022-02-27');

The following information is returned:

+-------------------------+
| year(DATE '2022-02-27') |
+-------------------------+
|                    2022 |

Example 4

If you execute the following SQL statement, the system adds the value of curdate() to '00:00:00' and returns the result as a string.

select year(time '00:00:00');

The following information is returned:

+-----------------------+
| year(TIME '00:00:00') |
+-----------------------+
|                  2022 |

Example 5

select year('2022-02-27');

The following information is returned:

+--------------------+
| year('2022-02-27') |
+--------------------+
|               2022 |

YEARWEEK

YEARWEEK(date)
YEARWEEK(date,mode)

Description: This function returns the year and week of a date.
The year in the result may be different from the year in the date parameter for the first and last weeks of the year.
The mode parameter in this function works in the same way as the mode parameter in the WEEK function. For the single-parameter syntax, the value of mode is 0.

Parameter types:

yearweek(timestamp)
yearweek(timestamp, bigint)
yearweek(datetime)
yearweek(datetime, bigint)
yearweek(date, bigint) 
yearweek(date) 
yearweek(varchar)
yearweek(varchar, bigint)

Data type of the return value: BIGINT.

Examples:

Example 1

select yearweek(timestamp '2022-02-27 00:00:00');

The following information is returned:

+-------------------------------------------+
| yearweek(TIMESTAMP '2022-02-27 00:00:00') |
+-------------------------------------------+
|                                    202209 |

Example 2

select yearweek(timestamp '2022-02-27 00:00:00',1);

The following information is returned:

+----------------------------------------------+
| yearweek(TIMESTAMP '2022-02-27 00:00:00', 1) |
+----------------------------------------------+
|                                       202208 |

Example 3

select yearweek(datetime '2022-02-27 00:00:00');

The following information is returned:

+------------------------------------------+
| yearweek(DATETIME '2022-02-27 00:00:00') |
+------------------------------------------+
|                                   202209 |

Example 4

select yearweek(datetime '2022-02-27 00:00:00',1);

The following information is returned:

+---------------------------------------------+
| yearweek(DATETIME '2022-02-27 00:00:00', 1) |
+---------------------------------------------+
|                                      202208 |

Example 5

select yearweek(date '2022-02-27',1);

The following information is returned:

+--------------------------------+
| yearweek(DATE '2022-02-27', 1) |
+--------------------------------+
|                         202208|

Example 6

select yearweek(date '2022-02-27');

The following information is returned:

+-----------------------------+
| yearweek(DATE '2022-02-27') |
+-----------------------------+
|                      202209 |

Example 7

select yearweek('2022-02-27');

The following information is returned:

+------------------------+
| yearweek('2022-02-27') |
+------------------------+
|                 202209 |

Example 8

select yearweek('2022-02-27',1);

The following information is returned:

+---------------------------+
| yearweek('2022-02-27', 1) |
+---------------------------+
|                    202208 |