Syntax and examples of date and time functions - AnalyticDB

AnalyticDB for MySQL supports a full set of MySQL-compatible date and time functions. Use them to get the current date and time, format values, calculate intervals, and extract specific parts such as the year, month, or day.

Function quick reference

Function	Description	Return type
Get current date and time
CURDATE()	Current date	DATE
CURTIME()	Current time	TIME
NOW() / LOCALTIME() / LOCALTIMESTAMP()	Current timestamp	DATETIME
SYSDATE()	System time	DATETIME
UTC_DATE()	Current UTC date	VARCHAR
UTC_TIME()	Current UTC time	VARCHAR
UTC_TIMESTAMP()	Current UTC timestamp	VARCHAR
Add and subtract intervals
ADDDATE(date, INTERVAL expr unit)	Add a time interval to a date	DATE
ADDTIME(expr1, expr2)	Add a time value to another	VARCHAR
SUBDATE(date, INTERVAL expr unit) / DATE_SUB	Subtract a time interval from a date	DATE
SUBTIME(expr1, expr2)	Subtract a time value from another	DATETIME
TIMESTAMPADD(unit, interval, expr)	Add an interval to a date or datetime	DATETIME
PERIOD_ADD(P, N)	Add N months to a period	BIGINT
Calculate differences
DATEDIFF(expr1, expr2)	Number of days between two dates	BIGINT
TIMEDIFF(expr1, expr2)	Difference between two time values	DATETIME
TIMESTAMPDIFF(unit, expr1, expr2)	Difference between two datetimes in the specified unit	BIGINT
PERIOD_DIFF(P1, P2)	Number of months between two periods	BIGINT
Extract date and time parts
DATE(expr)	Date part of a datetime expression	DATE
TIME(expr)	Time part of a datetime expression	VARCHAR
YEAR(date)	Year	BIGINT
MONTH(date)	Month (1–12)	BIGINT
MONTHNAME(date)	Full month name	VARCHAR
DAY(date) / DAYOFMONTH(date)	Day of the month (1–31)	BIGINT
DAYNAME(date)	Name of the weekday	VARCHAR
DAYOFWEEK(date)	Weekday index (1=Sunday, 7=Saturday)	BIGINT
DAYOFYEAR(date)	Day of the year (1–366)	BIGINT
WEEKDAY(date)	Weekday index (0=Monday, 6=Sunday)	BIGINT
HOUR(time)	Hour	BIGINT
MINUTE(time)	Minute	BIGINT
SECOND(time)	Second (0–59)	BIGINT
QUARTER(date)	Quarter (1–4)	BIGINT
WEEK(date[, mode])	Week number	BIGINT
WEEKOFYEAR(date)	Calendar week (1–53)	BIGINT
YEARWEEK(date[, mode])	Year and week combined	BIGINT
EXTRACT(unit FROM date)	One or more date/time parts in the specified unit	BIGINT
LAST_DAY(date)	Last day of the month	DATE
Format and convert
DATE_FORMAT(date, format)	Format a date as a string	VARCHAR
TIME_FORMAT(time, format)	Format a time as a string	VARCHAR
STR_TO_DATE(str, format)	Parse a string into a date or datetime	DATETIME
CONVERT_TZ(dt, from_tz, to_tz)	Convert a datetime between time zones	DATETIME
TIMESTAMP(expr)	Cast a date or datetime to DATETIME	DATETIME
FROM_UNIXTIME(unix_timestamp[, format])	Convert a Unix timestamp to a datetime	DATETIME
UNIX_TIMESTAMP([date])	Convert a date to a Unix timestamp	BIGINT
Build date and time values
MAKEDATE(year, dayofyear)	Build a date from a year and day-of-year	DATE
MAKETIME(hour, minute, second)	Build a time from hour, minute, and second	TIME
FROM_DAYS(N)	Convert a day count to a date	DATE
TO_DAYS(date)	Number of days since year 0	BIGINT
TO_SECONDS(expr)	Number of seconds since year 0	BIGINT
SEC_TO_TIME(seconds)	Convert seconds to a time value	TIME
TIME_TO_SEC(time)	Convert a time to seconds	BIGINT

Supported interval units

The following table shows which interval units each function accepts. Units not listed for a function are not supported.

Unit	ADDDATE / SUBDATE	EXTRACT	TIMESTAMPADD / TIMESTAMPDIFF
`second`	Yes	Yes	Yes
`minute`	Yes	Yes	Yes
`hour`	Yes	Yes	Yes
`day`	Yes	Yes	Yes
`week`	—	—	Yes
`month`	Yes	Yes	Yes
`quarter`	—	—	Yes
`year`	Yes	Yes	Yes
`minute_second`	Yes	Yes	—
`hour_second`	Yes	Yes	—
`hour_minute`	Yes	Yes	—
`day_second`	Yes	Yes	—
`day_minute`	Yes	Yes	—
`day_hour`	Yes	Yes	—
`year_month`	Yes	Yes	—

ADDDATE

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

Adds a time interval to a date. When days is supplied instead of an INTERVAL clause, the default unit is day.

Accepted parameter types

First argument	Second argument
DATE, TIMESTAMP, DATETIME, VARCHAR	INTERVAL expr unit
DATE, DATETIME, TIMESTAMP, VARCHAR	BIGINT or VARCHAR (treated as days)

Return type: DATE

Examples

The following examples use the INTERVAL syntax with different units.

-- Add 3 days to a date
SELECT adddate(date '2022-01-22', INTERVAL '3' day) AS result;
-- result: 2022-01-25

-- Add 3 days to a timestamp (returns datetime)
SELECT adddate(timestamp '2022-01-22', INTERVAL '3' day) AS result;
-- result: 2022-01-25 00:00:00

-- Add 3 seconds
SELECT adddate(datetime '2022-01-22', INTERVAL '3' second) AS result;
-- result: 2022-01-22 00:00:03

-- Add 3 months
SELECT adddate(datetime '2022-01-22', INTERVAL '3' month) AS result;
-- result: 2022-04-22 00:00:00

-- Add 3 years
SELECT adddate(datetime '2022-01-22', INTERVAL '3' year) AS result;
-- result: 2025-01-22 00:00:00

-- Add a compound interval: 1 day, 1 hour, 1 minute, 10 seconds (day_second)
SELECT adddate(datetime '2022-01-22', INTERVAL '1 01:01:10' day_second) AS result;
-- result: 2022-01-23 01:01:10

-- Add 2 years and 2 months (year_month)
SELECT adddate(datetime '2022-01-22 12:32:01', INTERVAL '2 2' year_month) AS result;
-- result: 2024-03-22 12:32:01

The following examples use the shorthand days syntax (integer or string).

-- Add 3 days (integer)
SELECT adddate('2022-01-22', 3) AS result;
-- result: 2022-01-25

-- Add 3 days (string)
SELECT adddate('2022-01-22', '3') AS result;
-- result: 2022-01-25

-- Add 3 days to a datetime value
SELECT adddate(datetime '2022-01-22 12:12:32', 3) AS result;
-- result: 2022-01-25 12:12:32

ADDTIME

ADDTIME(expr1, expr2)

Adds the time value expr2 to expr1 and returns the result.

Accepted parameter types

expr1	expr2
DATE, TIME, DATETIME, TIMESTAMP, VARCHAR	VARCHAR

Return type: VARCHAR

Examples

SELECT
    addtime(date '2022-01-01',              '01:01:01') AS from_date,
    addtime(time '01:00:00',                '01:01:01') AS from_time,
    addtime(datetime '2022-01-22 00:00:00', '01:01:01') AS from_datetime,
    addtime(timestamp '2022-01-22 00:00:00','01:01:01') AS from_timestamp,
    addtime('2022-01-22 00:00:00',          '01:01:01') AS from_varchar;

from_date	from_time	from_datetime	from_timestamp	from_varchar
2022-01-01 01:01:01	02:01:01	2022-01-22 01:01:01	2022-01-22 01:01:01	2022-01-22 01:01:01

CONVERT_TZ

CONVERT_TZ(dt, from_tz, to_tz)

Converts a datetime value dt from from_tz to to_tz and returns the result.

Accepted parameter types: VARCHAR, VARCHAR, VARCHAR

Return type: DATETIME

Examples

-- Convert using UTC offset notation
SELECT convert_tz('2022-01-01 12:00:00', '+00:00', '+10:00') AS result;
-- result: 2022-01-01 22:00:00

-- Convert using named time zones
SELECT convert_tz('2022-01-01 12:00:00', 'GMT', 'MET') AS result;
-- result: 2022-01-01 13:00:00

CURDATE

CURDATE()

Returns the current date.

Return type: DATE

SELECT curdate();
-- result: 2022-01-01

CURTIME

CURTIME()

Returns the current time.

Return type: TIME

SELECT curtime();
-- result: 14:39:22.109

DATE

DATE(expr)

Returns the date part of a date or datetime expression.

Accepted parameter types: TIMESTAMP, DATETIME, VARCHAR

Return type: DATE

Examples

SELECT
    date(timestamp '2022-01-01 01:02:03') AS from_timestamp,
    date(datetime  '2022-01-01 01:02:03') AS from_datetime,
    date('2022-01-01 01:02:03')            AS from_varchar;

from_timestamp	from_datetime	from_varchar
2022-01-01	2022-01-01	2022-01-01

DATE_FORMAT

DATE_FORMAT(date, format)

Formats a date value as a string using the format specifiers in the table below.

Format specifiers

Specifier	Description	Range
`%a`	Abbreviated day name	Sun–Sat
`%b`	Abbreviated month name	Jan–Dec
`%c`	Month, numeric	0–12
`%d`	Day of the month, zero-padded	00–31
`%e`	Day of the month	0–31
`%f`	Microseconds	000000–999999
`%H`	Hour, 24-hour clock, zero-padded	00–23
`%h`	Hour, 12-hour clock, zero-padded	01–12
`%I`	Hour, 12-hour clock, zero-padded	01–12
`%i`	Minutes, numeric	00–59
`%j`	Day of the year	001–366
`%k`	Hour, 24-hour clock	0–23
`%l`	Hour, 12-hour clock	1–12
`%M`	Full month name	January–December
`%m`	Month, numeric, zero-padded	00–12
`%p`	AM or PM	AM, PM
`%r`	Time, 12-hour format	hh:mm:ss AM/PM
`%S` or `%s`	Seconds	00–59
`%T`	Time, 24-hour format	hh:mm:ss
`%v`	Week number (WEEK() mode 3, used with `%x`)	Monday is the first day of the week.
`%W`	Full weekday name	Sunday–Saturday
`%x`	Year for the week (4 digits, used with `%v`)	Monday is the first day of the week.
`%Y`	Year, 4-digit	—
`%y`	Year, 2-digit	—
`%%`	Literal percent sign	—

Accepted parameter types: TIMESTAMP, VARCHAR, DATETIME, DATE + VARCHAR

Return type: VARCHAR

Examples

-- All four input types produce the same output
SELECT date_format(timestamp '2022-01-27 13:23:00', '%W %M %Y') AS result;
-- result: Thursday January 2022

SELECT date_format('2022-01-27 13:23:00', '%W %M %Y') AS result;
-- result: Thursday January 2022

SELECT date_format(datetime '2022-01-27 13:23:00', '%W %M %Y') AS result;
-- result: Thursday January 2022

SELECT date_format(date '2022-01-27', '%W %M %Y') AS result;
-- result: Thursday January 2022

SUBDATE / DATE_SUB

DATE_SUB(date, INTERVAL expr unit)
SUBDATE(date, INTERVAL expr unit)
SUBDATE(date, days)

Subtracts a time interval from a date. SUBDATE and DATE_SUB are aliases.

Accepted parameter types

First argument	Second argument
DATE, TIMESTAMP, DATETIME, VARCHAR	INTERVAL expr unit
DATE, DATETIME, TIMESTAMP, VARCHAR	BIGINT or VARCHAR (treated as days)

Return type: DATE

Examples

-- Subtract 3 days
SELECT date_sub('2022-01-22 00:00:00', INTERVAL '3' day) AS result;
-- result: 2022-01-19 00:00:00

-- Subtract 3 seconds
SELECT date_sub('2022-01-22 00:00:00', INTERVAL '3' second) AS result;
-- result: 2022-01-21 23:59:57

-- Subtract 3 months
SELECT date_sub('2022-01-22 00:00:00', INTERVAL '3' month) AS result;
-- result: 2021-10-22 00:00:00

-- Subtract 2 years and 2 months (year_month)
SELECT date_sub('2022-01-22 00:00:00', INTERVAL '2 2' year_month) AS result;
-- result: 2019-11-22 00:00:00

-- Subtract 3 days using the integer shorthand
SELECT date_sub(datetime '2022-01-22 00:00:00', 3) AS result;
-- result: 2022-01-19 00:00:00

DATEDIFF

DATEDIFF(expr1, expr2)

Returns the number of days between expr1 and expr2 (expr1 − expr2). Only the date part is used in the calculation.

Accepted parameter types: VARCHAR, DATETIME, TIMESTAMP, DATE (any combination)

Return type: BIGINT

Examples

SELECT datediff('2022-01-22 23:59:59', '2022-01-21') AS result;
-- result: 1

SELECT datediff('2022-01-22', date '2021-01-21') AS result;
-- result: 366

DAY / DAYOFMONTH

DAY(date)
DAYOFMONTH(date)

Returns the day of the month (1–31). DAY and DAYOFMONTH are aliases.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    dayofmonth(timestamp '2022-01-22 12:23:09') AS from_timestamp,
    dayofmonth(datetime  '2022-01-22 00:00:00') AS from_datetime,
    dayofmonth(date '2022-01-22')               AS from_date,
    day('2022-01-22')                            AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
22	22	22	22

When a TIME value is passed, the function returns the day of the month for the current date.

DAYNAME

DAYNAME(date)

Returns the full name of the weekday for a date (for example, Saturday).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: VARCHAR

Examples

SELECT
    dayname(timestamp '2022-01-22 00:00:00') AS from_timestamp,
    dayname(datetime  '2022-01-22 00:00:00') AS from_datetime,
    dayname(date '2022-01-22')               AS from_date,
    dayname('2022-01-22')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
Saturday	Saturday	Saturday	Saturday

DAYOFWEEK

DAYOFWEEK(date)

Returns the weekday index for a date: 1 = Sunday, 2 = Monday, …, 7 = Saturday.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: BIGINT

Examples

SELECT
    dayofweek(timestamp '2022-01-22 00:00:00') AS from_timestamp,
    dayofweek(datetime  '2022-01-22 00:00:00') AS from_datetime,
    dayofweek(date '2022-01-22')               AS from_date,
    dayofweek('2022-01-22')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
7	7	7	7

DAYOFYEAR

DAYOFYEAR(date)

Returns the day of the year (1–366).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: BIGINT

Examples

SELECT
    dayofyear(timestamp '2022-02-01 00:12:12') AS from_timestamp,
    dayofyear(datetime  '2022-02-01 00:12:12') AS from_datetime,
    dayofyear(date '2022-02-01')               AS from_date,
    dayofyear('2022-02-01')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
32	32	32	32

EXTRACT

EXTRACT(unit FROM date)

Returns one or more date/time parts in the specified unit. For compound units such as day_second, the result concatenates the component values.

Accepted parameter types: VARCHAR, TIMESTAMP, DATETIME, TIME

Return type: BIGINT

Examples

-- Single-component units
SELECT extract(second  FROM '2022-01-22 00:12:34') AS sec;   -- 34
SELECT extract(minute  FROM '2022-01-22 00:12:34') AS min;   -- 12
SELECT extract(hour    FROM '2022-01-22 00:12:34') AS hr;    -- 0
SELECT extract(day     FROM '2022-01-22 00:12:34') AS dy;    -- 22
SELECT extract(month   FROM '2022-01-22 00:12:34') AS mo;    -- 1
SELECT extract(year    FROM timestamp '2022-01-22') AS yr;   -- 2022

-- Compound units (values are concatenated)
SELECT extract(minute_second FROM '2022-01-22 00:12:34') AS result; -- 1234
SELECT extract(hour_second   FROM '2022-01-22 12:12:34') AS result; -- 121234
SELECT extract(hour_minute   FROM '2022-01-22 12:12:34') AS result; -- 1212
SELECT extract(day_second    FROM '2022-01-22 12:12:34') AS result; -- 22121234
SELECT extract(day_minute    FROM '2022-01-22 00:12:34') AS result; -- 220012
SELECT extract(day_hour      FROM '2022-01-22 12:12:34') AS result; -- 2212
SELECT extract(year_month    FROM '2022-01-22 00:12:34') AS result; -- 202201

When a TIME value is passed with a date-related unit (such as year), the function uses the current date.

FROM_DAYS

FROM_DAYS(N)

Returns the DATE value corresponding to day count N (days since year 0).

Accepted parameter types: BIGINT, VARCHAR

Return type: DATE

Examples

SELECT from_days(738565)   AS result; -- 2022-02-14
SELECT from_days('738565') AS result; -- 2022-02-14

FROM_UNIXTIME

FROM_UNIXTIME(unix_timestamp[, format])

Converts a Unix timestamp to a datetime. When format is specified, the result is a formatted string instead. The format parameter uses the same specifiers as DATE_FORMAT.

Accepted parameter types: VARCHAR or DOUBLE, optionally followed by VARCHAR (format)

Return type: DATETIME

Examples

-- Without format: returns a datetime
SELECT from_unixtime('1647738565') AS result;
-- result: 2022-03-20 09:09:25

SELECT from_unixtime(1647738456) AS result;
-- result: 2022-03-20 09:07:36

-- With format: returns a formatted string
SELECT from_unixtime('1647738565', '%Y %M %h:%i:%s %x') AS result;
-- result: 2022 March 09:09:25 2022

SELECT from_unixtime(1647738456, '%Y %M %h:%i:%s %x') AS result;
-- result: 2022 March 09:07:36 2022

HOUR

HOUR(time)

Returns the hour part of a time value.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    hour(timestamp '2022-01-22 10:05:03') AS from_timestamp,
    hour(datetime  '2022-01-22 10:05:03') AS from_datetime,
    hour(date '2022-01-22')               AS from_date,
    hour(time '10:05:03')                 AS from_time,
    hour('10:05:03')                      AS from_varchar;

from_timestamp	from_datetime	from_date	from_time	from_varchar
10	10	0	10	10

LAST_DAY

LAST_DAY(date)

Returns the last day of the month for the given date or datetime.

Accepted parameter types: VARCHAR, TIMESTAMP, DATETIME, DATE

Return type: DATE

Examples

SELECT
    last_day('2022-01-22')                    AS from_varchar,
    last_day(timestamp '2022-01-22 12:12:12') AS from_timestamp,
    last_day(datetime  '2022-01-22 12:12:12') AS from_datetime,
    last_day(date '2022-01-22')               AS from_date;

from_varchar	from_timestamp	from_datetime	from_date
2022-01-31	2022-01-31	2022-01-31	2022-01-31

LOCALTIME / LOCALTIMESTAMP / NOW

localtime
localtime()
localtimestamp
localtimestamp()
now()

Returns the current timestamp. All five forms are equivalent.

Return type: DATETIME

Examples

SELECT now();            -- 2022-01-22 16:28:37
SELECT localtime;        -- 2022-01-22 16:28:37
SELECT localtime();      -- 2022-01-22 16:28:37
SELECT localtimestamp;   -- 2022-01-22 17:28:37
SELECT localtimestamp(); -- 2022-01-22 17:38:13

MAKEDATE

MAKEDATE(year, dayofyear)

Builds a date from a year and the day-of-year value.

Accepted parameter types: BIGINT, BIGINT or VARCHAR, VARCHAR

Return type: DATE

Examples

SELECT makedate(2022, 31) AS day_31, makedate(2022, 32) AS day_32;

day_31	day_32
2022-01-31	2022-02-01

MAKETIME

MAKETIME(hour, minute, second)

Builds a TIME value from hour, minute, and second components.

Accepted parameter types: BIGINT, BIGINT, BIGINT or VARCHAR, VARCHAR, VARCHAR

Return type: TIME

Examples

SELECT maketime(12, 15, 30)       AS result; -- 12:15:30
SELECT maketime('12', '15', '30') AS result; -- 12:15:30

MINUTE

MINUTE(time)

Returns the minute part of a time value.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    minute(timestamp '2022-02-03 10:05:03') AS from_timestamp,
    minute(datetime  '2022-02-03 10:05:03') AS from_datetime,
    minute(date '2022-02-03')               AS from_date,
    minute(time '12:12:12')                 AS from_time,
    minute('2022-02-03 10:05:03')           AS from_varchar;

from_timestamp	from_datetime	from_date	from_time	from_varchar
5	5	0	12	5

MONTH

MONTH(date)

Returns the month of a date (1–12).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    month(timestamp '2022-02-03 00:00:00') AS from_timestamp,
    month(datetime  '2022-02-03 00:00:00') AS from_datetime,
    month(date '2022-02-03')               AS from_date,
    month('2022-02-03')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
2	2	2	2

When a TIME value is passed, MONTH returns the month for the current date at the time of execution.

MONTHNAME

MONTHNAME(date)

Returns the full name of the month (for example, February).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: VARCHAR

Examples

SELECT
    monthname(timestamp '2022-02-03 00:00:00') AS from_timestamp,
    monthname(datetime  '2022-02-03 00:00:00') AS from_datetime,
    monthname(date '2022-02-03')               AS from_date,
    monthname('2022-02-03')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
February	February	February	February

PERIOD_ADD

PERIOD_ADD(P, N)

Adds N months to period P, where P is in YYYYMM format. Returns the result in the same format.

Accepted parameter types: BIGINT, BIGINT or VARCHAR, VARCHAR or VARCHAR, BIGINT

Return type: BIGINT

Examples

SELECT period_add(202201, 2)    AS result; -- 202203
SELECT period_add('202201', '2') AS result; -- 202203
SELECT period_add('202201', 2)  AS result; -- 202203

PERIOD_DIFF

PERIOD_DIFF(P1, P2)

Returns the number of months between two periods (P1 − P2), where both periods are in YYYYMM format.

Accepted parameter types: BIGINT, BIGINT or VARCHAR, VARCHAR

Return type: BIGINT

Examples

SELECT period_diff(202202, 202103)    AS result; -- 11
SELECT period_diff('202202', '202103') AS result; -- 11

QUARTER

QUARTER(date)

Returns the quarter of the year (1–4).

Accepted parameter types: DATETIME, VARCHAR, TIMESTAMP, DATE

Return type: BIGINT

Examples

SELECT
    quarter(datetime  '2022-04-01 12:12:12') AS from_datetime,
    quarter('2022-04-01')                    AS from_varchar,
    quarter(timestamp '2022-04-01 12:12:12') AS from_timestamp,
    quarter(date '2022-04-01')               AS from_date;

from_datetime	from_varchar	from_timestamp	from_date
2	2	2	2

SEC_TO_TIME

SEC_TO_TIME(seconds)

Converts a number of seconds to a TIME value.

Accepted parameter types: BIGINT, VARCHAR

Return type: TIME

Examples

SELECT sec_to_time(2378)   AS result; -- 00:39:38
SELECT sec_to_time('2378') AS result; -- 00:39:38

SECOND

SECOND(time)

Returns the second part of a time value (0–59).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    second(timestamp '2022-03-12 12:13:14') AS from_timestamp,
    second(datetime  '2022-03-12 12:13:14') AS from_datetime,
    second(date '2022-03-12')               AS from_date,
    second(time '12:13:14')                 AS from_time,
    second('12:12:23')                      AS from_varchar;

from_timestamp	from_datetime	from_date	from_time	from_varchar
14	14	0	14	23

STR_TO_DATE

STR_TO_DATE(str, format)

Parses a string into a date or datetime using the specified format. The format parameter uses the same specifiers as DATE_FORMAT.

Accepted parameter types: VARCHAR, VARCHAR

Return type: DATETIME

Example

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

SUBTIME

SUBTIME(expr1, expr2)

Subtracts the time value expr2 from expr1 and returns the result.

Accepted parameter types

expr1	expr2
DATE, DATETIME, TIMESTAMP, TIME, VARCHAR	VARCHAR

Return type: DATETIME

Examples

SELECT
    subtime(date '2022-10-31',              '0:1:1') AS from_date,
    subtime(datetime '2022-10-31 12:12:12', '0:1:1') AS from_datetime,
    subtime(timestamp '2022-10-31 12:12:12','0:1:1') AS from_timestamp,
    subtime(time '12:12:12',               '0:1:1') AS from_time,
    subtime('2022-10-31 23:59:59',         '0:1:1') AS from_varchar;

from_date	from_datetime	from_timestamp	from_time	from_varchar
2022-10-30 23:58:59	2022-10-31 12:11:11	2022-10-31 12:11:11	12:11:11	2022-10-31 23:58:58

SYSDATE

SYSDATE()

Returns the system time.

Return type: DATETIME

SELECT sysdate();
-- result: 2022-02-26 00:47:21

TIME

TIME(expr)

Returns the time part of a date or datetime expression as a string.

Accepted parameter types: VARCHAR, DATETIME, TIMESTAMP

Return type: VARCHAR

Examples

SELECT
    time('2022-01-31 01:02:03')            AS from_varchar,
    time(datetime '2022-01-31 01:02:03')   AS from_datetime,
    time(timestamp '2022-01-31 01:02:03')  AS from_timestamp;

from_varchar	from_datetime	from_timestamp
01:02:03	01:02:03	01:02:03

TIME_FORMAT

TIME_FORMAT(time, format)

Formats a time value as a string using the specified format. The format parameter uses the same specifiers as DATE_FORMAT.

Accepted parameter types: VARCHAR, TIMESTAMP, DATETIME, TIME, DATE + VARCHAR

Return type: VARCHAR

Examples

SELECT time_format('12:00:00', '%H %k %h %I %l') AS result;
-- result: 12 12 12 12 12

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

SELECT time_format(time '23:00:00', '%H %k %h %I %l') AS result;
-- result: 23 23 11 11 11

SELECT time_format(date '2022-01-22', '%H %k %h %I %l') AS result;
-- result: 00 0 12 12 12

TIME_TO_SEC

TIME_TO_SEC(time)

Converts a time value to the equivalent number of seconds.

Accepted parameter types: VARCHAR, DATETIME, TIMESTAMP, DATE, TIME

Return type: BIGINT

Examples

SELECT
    time_to_sec(datetime  '2022-01-22 22:23:00') AS from_datetime,  -- 80580
    time_to_sec(timestamp '2022-01-22 22:23:00') AS from_timestamp, -- 80580
    time_to_sec(date '2022-01-22')               AS from_date,      -- 0
    time_to_sec(time '12:12:12')                 AS from_time,      -- 43932
    time_to_sec('22:23:00')                      AS from_varchar;   -- 80580

TIMEDIFF

TIMEDIFF(expr1, expr2)

Returns the difference between two time values (expr1 − expr2). Equivalent to SUBTIME.

Accepted parameter types: TIME + VARCHAR, TIME + TIME, VARCHAR + VARCHAR

Return type: DATETIME

Examples

SELECT
    timediff(time '12:00:00', '10:00:00')          AS result1,
    timediff('12:00:00', '10:00:00')                AS result2,
    timediff(time '12:00:00', time '10:00:00')      AS result3;

result1	result2	result3
02:00:00	02:00:00	02:00:00

TIMESTAMP

TIMESTAMP(expr)

Casts a date or datetime expression to a DATETIME value.

Accepted parameter types: DATE, VARCHAR

Return type: DATETIME

Examples

SELECT timestamp(date '2022-01-22') AS result; -- 2022-01-22 00:00:00
SELECT timestamp('2022-01-22')      AS result; -- 2022-01-22 00:00:00

TIMESTAMPADD

TIMESTAMPADD(unit, interval, datetime_expr)

Adds interval (in the specified unit) to datetime_expr.

Valid values of unit: second, minute, hour, day, week, month, quarter, year.

Accepted parameter types

unit	interval	datetime_expr
VARCHAR	BIGINT or VARCHAR	TIMESTAMP, DATE, DATETIME, or VARCHAR

Return type: DATETIME

Examples

-- Add 1 second
SELECT timestampadd(second, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-01-02 12:12:13

-- Add 8820 minutes (converts to days and hours)
SELECT timestampadd(minute, 8820, '2022-02-24 09:00:00') AS result;
-- result: 2022-03-02 12:00:00

-- Add 1 hour
SELECT timestampadd(hour, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-01-02 13:12:12

-- Add 1 day
SELECT timestampadd(day, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-01-03 12:12:12

-- Add 1 week
SELECT timestampadd(week, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-01-09 12:12:12

-- Add 1 month
SELECT timestampadd(month, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-02-02 12:12:12

-- Add 1 quarter
SELECT timestampadd(quarter, 1, '2022-01-02 12:12:12') AS result;
-- result: 2022-04-02 12:12:12

-- Add 1 year
SELECT timestampadd(year, 1, '2022-01-02 12:12:12') AS result;
-- result: 2023-01-02 12:12:12

TIMESTAMPDIFF

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

Returns datetime_expr2 − datetime_expr1 in the specified unit. Valid values of unit are the same as for TIMESTAMPADD.

Accepted parameter types: VARCHAR + TIMESTAMP, DATE, DATETIME, or VARCHAR (any matching pair)

Return type: BIGINT

Examples

-- Difference in seconds (Feb 1 to Mar 1, 2022 = 28 days)
SELECT timestampdiff(second, datetime '2022-02-01 10:12:13', datetime '2022-03-01 10:12:13') AS result;
-- result: 2419200

-- Difference in minutes
SELECT timestampdiff(minute, datetime '2022-02-01 10:12:13', datetime '2022-03-01 10:12:13') AS result;
-- result: 40320

-- Difference in hours
SELECT timestampdiff(hour, datetime '2022-02-01 10:12:13', datetime '2022-03-01 10:12:13') AS result;
-- result: 672

-- Difference in days
SELECT timestampdiff(day, '2022-02-01', '2022-03-01') AS result;
-- result: 28

-- Difference in weeks
SELECT timestampdiff(week, '2022-02-01', '2022-03-01') AS result;
-- result: 4

-- Difference in months
SELECT timestampdiff(month, '2022-02-01', '2022-03-01') AS result;
-- result: 1

-- Negative result (expr2 is earlier than expr1)
SELECT timestampdiff(year, datetime '2022-02-01 10:12:13', datetime '2020-05-01 10:12:13') AS result;
-- result: -1

TO_DAYS

TO_DAYS(date)

Returns the number of days from year 0 to the specified date.

Accepted parameter types: DATE, TIME, VARCHAR, TIMESTAMP, DATETIME

Return type: BIGINT

Examples

SELECT to_days(date '2022-02-12')            AS result; -- 738563
SELECT to_days(datetime '2022-02-12 12:12:12') AS result; -- 738563
SELECT to_days(timestamp '2022-02-12 12:12:12') AS result; -- 738563
SELECT to_days(now())                          AS result; -- current date as day count
SELECT to_days(curdate())                      AS result; -- equivalent to to_days(now())

When a TIME value is passed, TO_DAYS uses the current date.

TO_SECONDS

TO_SECONDS(expr)

Returns the number of seconds from year 0 to the specified date or datetime.

Accepted parameter types: DATE, DATETIME, TIMESTAMP, VARCHAR, TIME

Return type: BIGINT

Examples

SELECT to_seconds(date '2022-02-02')              AS result; -- 63810979200
SELECT to_seconds(datetime '2022-02-02 09:09:00') AS result; -- 63811012140
SELECT to_seconds(timestamp '2022-02-02 09:09:00') AS result; -- 63811012140
SELECT to_seconds('2022-02-02')                   AS result; -- 63810979200

When a TIME value is passed, TO_SECONDS adds the time to the current date.

UNIX_TIMESTAMP

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)

UNIX_TIMESTAMP() returns the number of seconds elapsed since 1970-01-01 00:00:00 UTC to the current time.
UNIX_TIMESTAMP(date) returns the number of seconds elapsed since 1970-01-01 00:00:00 UTC to the specified date.

Accepted parameter types: (none), VARCHAR, TIMESTAMP, DATE, DATETIME

Return type: BIGINT

Examples

SELECT unix_timestamp() AS result;
-- result: 1654759686 (varies by execution time)

SELECT unix_timestamp(timestamp '2022-02-08 12:12:12') AS result; -- 1644293532
SELECT unix_timestamp(date '2022-02-08')               AS result; -- 1644249600
SELECT unix_timestamp(datetime '2022-02-08 12:12:12')  AS result; -- 1644293532
SELECT unix_timestamp('2022-02-08 12:12:12')            AS result; -- 1644293532

UTC_DATE

UTC_DATE()

Returns the current UTC date.

Return type: VARCHAR

SELECT utc_date();
-- result: 2022-05-27

UTC_TIME

UTC_TIME()

Returns the current UTC time.

Return type: VARCHAR

SELECT utc_time();
-- result: 05:53:19

UTC_TIMESTAMP

utc_timestamp()

Returns the current UTC timestamp.

Return type: VARCHAR

SELECT utc_timestamp();
-- result: 2022-05-27 15:55:15

WEEK

WEEK(date[, mode])

Returns the week number for a date. The optional mode parameter controls how week boundaries are calculated.

Mode reference

Mode	First day of week	Week number range	Week 0 or last week of previous year
0	Sunday	0–53	Days before the first Sunday are week 0
1	Monday	0–53	Week 0 if fewer than 4 days before first Monday; otherwise week 1
2	Sunday	1–53	Days before the first Sunday become the last week of the previous year
3	Monday	1–53	Week 1 if 4 or more days before first Monday; otherwise last week of previous year
4	Sunday	0–53	Week 0 if fewer than 4 days before first Sunday; otherwise week 1
5	Monday	0–53	Days before the first Monday are week 0
6	Sunday	1–53	Week 1 if 4 or more days before first Sunday; otherwise last week of previous year
7	Monday	1–53	Days before the first Monday become the last week of the previous year

Accepted parameter types: VARCHAR, DATE, DATETIME, TIMESTAMP (each optionally with BIGINT mode)

Return type: BIGINT

Examples

SELECT week('2022-02-27')    AS no_mode;  -- 9  (default mode 0, Sunday-based)
SELECT week('2022-02-20', 1) AS mode_1;   -- 7  (Monday-based)
SELECT week(date '2022-02-20')            AS date_no_mode; -- 8
SELECT week(date '2022-02-20', 1)         AS date_mode_1;  -- 7
SELECT week(datetime '2022-02-20 00:00:00', 1) AS datetime_mode_1; -- 7
SELECT week(timestamp '2022-02-20 00:00:00')   AS timestamp_no_mode; -- 8

WEEKDAY

WEEKDAY(date)

Returns the weekday index for a date: 0 = Monday, 1 = Tuesday, …, 6 = Sunday.

WEEKDAY uses a different index from DAYOFWEEK. DAYOFWEEK uses 1–7 with Sunday = 1, while WEEKDAY uses 0–6 with Monday = 0.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: BIGINT

Examples

SELECT
    weekday(timestamp '2022-02-20 00:09:00') AS from_timestamp,
    weekday(datetime  '2022-02-20 00:09:00') AS from_datetime,
    weekday(date '2022-02-20 00:09:00')      AS from_date,
    weekday('2022-02-20')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
6	6	6	6

WEEKOFYEAR

WEEKOFYEAR(date)

Returns the calendar week for a date (1–53).

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR

Return type: BIGINT

Examples

SELECT
    weekofyear(timestamp '2022-02-27 09:00:00') AS from_timestamp,
    weekofyear(datetime  '2022-02-27 09:00:00') AS from_datetime,
    weekofyear(date '2022-02-27')               AS from_date,
    weekofyear('2022-02-27')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
8	8	8	8

YEAR

YEAR(date)

Returns the year part of a date.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, TIME, VARCHAR

Return type: BIGINT

Examples

SELECT
    year(timestamp '2022-02-27 00:00:00') AS from_timestamp,
    year(datetime  '2022-02-27 00:00:00') AS from_datetime,
    year(date '2022-02-27')               AS from_date,
    year('2022-02-27')                    AS from_varchar;

from_timestamp	from_datetime	from_date	from_varchar
2022	2022	2022	2022

When a TIME value is passed, YEAR returns the year for the current date. The system adds the current date to the TIME value before extracting the year.

YEARWEEK

YEARWEEK(date)
YEARWEEK(date, mode)

Returns the year and week of a date as a single integer in YYYYWW format. The mode parameter works the same way as in WEEK. When no mode is specified, the default is 0.

For dates near the start or end of a year, the year in the result may differ from the year in the input date.

Accepted parameter types: TIMESTAMP, DATETIME, DATE, VARCHAR (each optionally with BIGINT mode)

Return type: BIGINT

Examples

SELECT
    yearweek(timestamp '2022-02-27 00:00:00')    AS ts_no_mode,  -- 202209
    yearweek(timestamp '2022-02-27 00:00:00', 1) AS ts_mode_1,   -- 202208
    yearweek(datetime  '2022-02-27 00:00:00')    AS dt_no_mode,  -- 202209
    yearweek(datetime  '2022-02-27 00:00:00', 1) AS dt_mode_1,   -- 202208
    yearweek(date '2022-02-27')                  AS d_no_mode,   -- 202209
    yearweek(date '2022-02-27', 1)               AS d_mode_1,    -- 202208
    yearweek('2022-02-27')                       AS v_no_mode,   -- 202209
    yearweek('2022-02-27', 1)                    AS v_mode_1;    -- 202208