Tencent Cloud

Recent Pages

Date and Time Functions

Last updated: 2023-11-08 14:18:06

Date and time functions are described as follows:
Function
Description
DATE string
Returns a SQL date parsed from string in the format of "yyyy-MM-dd".
TIME string
Returns a SQL time parsed from string in the format of "HH:mm:ss".
TIMESTAMP string
Returns a SQL timestamp parsed from string in the format of "yyyy-MM-dd HH:mm:ss[.SSS]".
INTERVAL string range
Parses an interval string in the format "dd hh:mm:ss.fff" for intervals of milliseconds or "yyyy-MM" for intervals of months.
An interval range can be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND for intervals of milliseconds;
and YEAR or YEAR TO MONTH for intervals of months. Examples: INTERVAL '10 00:00:00.004' DAY TO SECOND, INTERVAL '10' DAY, and INTERVAL '2-10' YEAR TO MONTH.
CURRENT_DATE
Returns the current SQL date (UTC).
CURRENT_TIME
Returns the current SQL time (UTC).
CURRENT_TIMESTAMP
Returns the current SQL timestamp (UTC).
LOCALTIME
Returns the current SQL time in the local time zone.
LOCALTIMESTAMP
Returns the current SQL timestamp in the local time zone.
EXTRACT(timeintervalunit FROM temporal)
Returns an item in a time point or time interval string. For example, EXTRACT(DAY FROM DATE '2006-06-05') returns 5, and EXTRACT(YEAR FROM DATE '2018-06-12') returns 2018.
YEAR(date)
Returns the year from date. Equivalent to EXTRACT(YEAR FROM date). For example, YEAR(DATE '2020-08-12') returns 2020.
QUARTER(date)
Returns the quarter of a year from date. Equivalent to EXTRACT(QUARTER FROM date). For example, QUARTER(DATE '2012-09-10') returns 3.
MONTH(date)
Returns the month from date. Equivalent to EXTRACT(MONTH FROM date). For example, MONTH(DATE '2012-09-10') returns 9.
WEEK(date)
Returns the week of a year from date. Equivalent to EXTRACT(WEEK FROM date). For example, WEEK(DATE '1994-09-27') returns 39.
DAYOFYEAR(date)
Returns the day of a year (an integer between 1 and 366) from date. Equivalent to EXTRACT(DOY FROM date). For example, DAYOFYEAR(DATE '1994-09-27') returns 270.
DAYOFMONTH(date)
Returns the day of a month (an integer between 1 and 31) from date. Equivalent to EXTRACT(DAY FROM date). For example, DAYOFMONTH(DATE '1994-09-27') returns 27.
DAYOFWEEK(date)
Returns the day of a week (an integer between 1 and 7) from date. Equivalent to EXTRACT(DOW FROM date). For example, DAYOFWEEK(DATE '1994-09-27') returns 3.
HOUR(timestamp)
Returns the hour of a day (an integer between 0 and 23) from timestamp. Equivalent to EXTRACT(HOUR FROM timestamp). For example, HOUR('2017-10-02 12:25:44') returns 12.
MINUTE(timestamp)
Returns the minute of an hour (an integer between 0 and 59) from timestamp. Equivalent to EXTRACT(MINUTE FROM timestamp). For example, MINUTE('2017-10-02 12:25:44') returns 25.
SECOND(timestamp)
Returns the second of a minute (an integer between 0 and 59) from timestamp. Equivalent to EXTRACT(SECOND FROM timestamp). For example, SECOND('2017-10-02 12:25:44') returns 44.
FLOOR(timepoint TO timeintervalunit)
Returns a value that rounds timepoint down to the time unit timeintervalunit. For example, FLOOR(TIME '12:44:31' TO MINUTE) returns 12:44:00.
CEIL(timepoint TO timeintervalunit)
Returns a value that rounds timepoint up to the time unit timeintervalunit. For example, CEIL(TIME '12:44:31' TO MINUTE) returns 12:45:00.
(timepoint, temporal) OVERLAPS (timepoint, temporal)
Checks whether two time intervals overlap (returns TRUE if yes). For example, (TIME'2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME'3:30:00', INTERVAL '2' HOUR) returns TRUE, and (TIME'9:00:00', TIME '10:00:00') OVERLAPS (TIME'10:15:00', INTERVAL '3' HOUR) returns FALSE.
TO_TIMESTAMP(string, simple_format)
Converts a string-formatted timestamp to Timestamp type. For more information, see Type Conversion Functions.
DATE_FORMAT_SIMPLE (timestamp, simple_format)
Converts a BIGINT type Unix timestamp in milliseconds to a string. For more information, see Type Conversion Functions.
DATE_FORMAT(timestamp, format)
Converts timestamp to a value of string in the format. For more information, see Type Conversion Functions.
TIMESTAMP_TO_LONG(timestamp)
Converts a Timestamp type timestamp to a BIGINT type Unix timestamp in milliseconds or seconds (configurable). For more information, see Type Conversion Functions.
TIMESTAMPADD(unit, interval, timestamp)
Adds interval (which can be negative) to the specified timestamp. The unit of the interval must be one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. For example, TIMESTAMPADD(WEEK, 1, '2013-01-02') returns '2013-01-09'.
TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)
Returns the number of timepointunit between timepoint1 and timepoint2, which can be negative. timepointunit must be one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. For example, TIMESTAMPDIFF(DAY, TIMESTAMP '2003-01-02 10:00:00', TIMESTAMP '2003-01-03 10:00:00') returns 1 (that is, 1 day).
CONVERT_TZ(string1, string2, string3)
Converts string1 (in the format of 'yyyy-MM-dd HH:mm:ss') from time zone string2 to time zone string3. The format of the time zone should be either an abbreviation such as "PST", a full name such as "Asia/Shanghai", or a custom ID such as "GMT-8:00". For example, CONVERT('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles') returns '1969-12-31 16:00:00'.
FROM_UNIXTIME(numeric[, string])
Returns a representation of the numeric parameter as a value in string format ('YYYY-MM-DD hh:mm:ss'). numeric represents the number of seconds since 1970-01-01 00:00:00 UTC. The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
UNIX_TIMESTAMP()
Returns the current Unix timestamp in seconds (number of seconds since 1970-01-01 00:00:00 UTC). The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
UNIX_TIMESTAMP(string1[, string2])
Converts string1 with format string2 (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to Unix timestamp (BIGINT).
TO_DATE(string1[, string2])
Converts string1 with format string2 (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to a date.
TO_TIMESTAMP(string1[, string2])
Converts string1 with format string2 (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to a timestamp. The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
NOW()
Returns the current SQL timestamp (UTC).
﻿

Contact Us

Contact our sales team or business advisors to help your business.

Technical Support

Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

7x24 Phone Support

tencent cloud

Recent Pages

Date and Time Functions

Was this page helpful?

Was this page helpful?

Function	Description
DATE string	Returns a SQL date parsed from `string` in the format of "yyyy-MM-dd".
TIME string	Returns a SQL time parsed from `string` in the format of "HH:mm:ss".
TIMESTAMP string	Returns a SQL timestamp parsed from `string` in the format of "yyyy-MM-dd HH:mm:ss[.SSS]".
INTERVAL string range	Parses an interval string in the format "dd hh:mm:ss.fff" for intervals of milliseconds or "yyyy-MM" for intervals of months. An interval range can be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND for intervals of milliseconds; and YEAR or YEAR TO MONTH for intervals of months. Examples: `INTERVAL '10 00:00:00.004' DAY TO SECOND`, `INTERVAL '10' DAY`, and `INTERVAL '2-10' YEAR TO MONTH`.
CURRENT_DATE	Returns the current SQL date (UTC).
CURRENT_TIME	Returns the current SQL time (UTC).
CURRENT_TIMESTAMP	Returns the current SQL timestamp (UTC).
LOCALTIME	Returns the current SQL time in the local time zone.
LOCALTIMESTAMP	Returns the current SQL timestamp in the local time zone.
EXTRACT(timeintervalunit FROM temporal)	Returns an item in a time point or time interval string. For example, `EXTRACT(DAY FROM DATE '2006-06-05')` returns `5`, and `EXTRACT(YEAR FROM DATE '2018-06-12')` returns `2018`.
YEAR(date)	Returns the year from `date`. Equivalent to `EXTRACT(YEAR FROM date)`. For example, `YEAR(DATE '2020-08-12')` returns `2020`.
QUARTER(date)	Returns the quarter of a year from `date`. Equivalent to `EXTRACT(QUARTER FROM date)`. For example, `QUARTER(DATE '2012-09-10')` returns `3`.
MONTH(date)	Returns the month from `date`. Equivalent to `EXTRACT(MONTH FROM date)`. For example, `MONTH(DATE '2012-09-10')` returns `9`.
WEEK(date)	Returns the week of a year from `date`. Equivalent to `EXTRACT(WEEK FROM date)`. For example, `WEEK(DATE '1994-09-27')` returns `39`.
DAYOFYEAR(date)	Returns the day of a year (an integer between 1 and 366) from `date`. Equivalent to `EXTRACT(DOY FROM date)`. For example, `DAYOFYEAR(DATE '1994-09-27')` returns `270`.
DAYOFMONTH(date)	Returns the day of a month (an integer between 1 and 31) from `date`. Equivalent to `EXTRACT(DAY FROM date)`. For example, `DAYOFMONTH(DATE '1994-09-27')` returns `27`.
DAYOFWEEK(date)	Returns the day of a week (an integer between 1 and 7) from `date`. Equivalent to `EXTRACT(DOW FROM date)`. For example, `DAYOFWEEK(DATE '1994-09-27')` returns `3`.
HOUR(timestamp)	Returns the hour of a day (an integer between 0 and 23) from `timestamp`. Equivalent to `EXTRACT(HOUR FROM timestamp)`. For example, `HOUR('2017-10-02 12:25:44')` returns `12`.
MINUTE(timestamp)	Returns the minute of an hour (an integer between 0 and 59) from `timestamp`. Equivalent to `EXTRACT(MINUTE FROM timestamp)`. For example, `MINUTE('2017-10-02 12:25:44')` returns `25`.
SECOND(timestamp)	Returns the second of a minute (an integer between 0 and 59) from `timestamp`. Equivalent to `EXTRACT(SECOND FROM timestamp)`. For example, `SECOND('2017-10-02 12:25:44')` returns `44`.
FLOOR(timepoint TO timeintervalunit)	Returns a value that rounds `timepoint` down to the time unit `timeintervalunit`. For example, `FLOOR(TIME '12:44:31' TO MINUTE)` returns `12:44:00`.
CEIL(timepoint TO timeintervalunit)	Returns a value that rounds `timepoint` up to the time unit `timeintervalunit`. For example, `CEIL(TIME '12:44:31' TO MINUTE)` returns `12:45:00`.
(timepoint, temporal) OVERLAPS (timepoint, temporal)	Checks whether two time intervals overlap (returns TRUE if yes). For example, `(TIME'2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME'3:30:00', INTERVAL '2' HOUR)` returns TRUE, and `(TIME'9:00:00', TIME '10:00:00') OVERLAPS (TIME'10:15:00', INTERVAL '3' HOUR)` returns FALSE.
TO_TIMESTAMP(string, simple_format)	Converts a string-formatted timestamp to Timestamp type. For more information, see Type Conversion Functions.
DATE_FORMAT_SIMPLE (timestamp, simple_format)	Converts a BIGINT type Unix timestamp in milliseconds to a string. For more information, see Type Conversion Functions.
DATE_FORMAT(timestamp, format)	Converts `timestamp` to a value of string in the `format`. For more information, see Type Conversion Functions.
TIMESTAMP_TO_LONG(timestamp)	Converts a Timestamp type timestamp to a BIGINT type Unix timestamp in milliseconds or seconds (configurable). For more information, see Type Conversion Functions.
TIMESTAMPADD(unit, interval, timestamp)	Adds `interval` (which can be negative) to the specified `timestamp`. The unit of the interval must be one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. For example, `TIMESTAMPADD(WEEK, 1, '2013-01-02')` returns '2013-01-09'.
TIMESTAMPDIFF(timepointunit, timepoint1, timepoint2)	Returns the number of timepointunit between timepoint1 and timepoint2, which can be negative. `timepointunit` must be one of SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. For example, `TIMESTAMPDIFF(DAY, TIMESTAMP '2003-01-02 10:00:00', TIMESTAMP '2003-01-03 10:00:00')` returns `1` (that is, 1 day).
CONVERT_TZ(string1, string2, string3)	Converts `string1` (in the format of 'yyyy-MM-dd HH:mm:ss') from time zone `string2` to time zone `string3`. The format of the time zone should be either an abbreviation such as "PST", a full name such as "Asia/Shanghai", or a custom ID such as "GMT-8:00". For example, `CONVERT('1970-01-01 00:00:00', 'UTC', 'America/Los_Angeles')` returns '1969-12-31 16:00:00'.
FROM_UNIXTIME(numeric[, string])	Returns a representation of the `numeric` parameter as a value in string format ('YYYY-MM-DD hh:mm:ss'). `numeric` represents the number of seconds since 1970-01-01 00:00:00 UTC. The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
UNIX_TIMESTAMP()	Returns the current Unix timestamp in seconds (number of seconds since 1970-01-01 00:00:00 UTC). The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
UNIX_TIMESTAMP(string1[, string2])	Converts `string1` with format `string2` (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to Unix timestamp (BIGINT).
TO_DATE(string1[, string2])	Converts `string1` with format `string2` (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to a date.
TO_TIMESTAMP(string1[, string2])	Converts `string1` with format `string2` (default: 'yyyy-MM-dd HH:mm:ss'; configurable) to a timestamp. The default time zone is UTC+8, which is Beijing time (Asia/Shanghai).
NOW()	Returns the current SQL timestamp (UTC).

tencent cloud

Sign Up

Log in

Recent Pages

Date and Time Functions

Was this page helpful?

Was this page helpful?