Tencent Cloud

Recent Pages

Date and Time Functions

Last updated: 2024-08-07 17:32:05
DATE
Function statement:
DATE(<expr> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Force the type to be converted to date.
Return type: date
Example:
> select date('2022-02-02');
 2022-02-02
TIMESTAMP
Function statement:
TIMESTAMP(<expr> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Force the type to be converted to timestamp.
Return type: timestamp
Example:
> select timestamp('2022-02-02 11:11:11');
 2022-02-02 11:11:11
ADD_MONTHS
Function statement:
ADD_MONTHS(<start_date> date|timestamp|string, <num> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the date that is num months after start_date.
Return type: date
Example:
> SELECT add_months('2016-08-31', 1);
 2016-09-30
CURRENT_DATE
Function statement:
CURRENT_DATE
Supported engines: SparkSQL and Presto
Usage instructions: Return the current date at the start of the query calculation.
Return type: date
Example:
> SELECT CURRENT_DATE;
 2022-07-27
CURRENT_TIMESTAMP
Function statement:
CURRENT_TIMESTAMP
Supported engines: SparkSQL and Presto
Usage instructions: Return the current timestamp at the start of the query calculation.
Return type: timestamp
Example:
> SELECT CURRENT_TIMESTAMP;
 2022-07-27 18:06:00.632
CURRENT_TIMEZONE
Function statement:
CURRENT_TIMEZONE(
Supported engines: SparkSQL and Presto
Usage instructions: Return the local timezone of the current session.
Return type: string
Example:
> select CURRENT_TIMEZONE();
Asia/Shanghai
DATEDIFF
Function statement:
DATEDIFF(<end> date|timestamp|string, <start> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the number of days from start to end.
Return type: integer
Example:
> SELECT datediff('2009-07-31', '2009-07-30');
 1
> SELECT datediff('2009-07-30', '2009-07-31');
 -1
DATE_ADD
Function statement:
DATE_ADD(<start_dates> date|timestamp|string, <num> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the date that is num days after start_date.
Return type: date
Example:
> SELECT date_add('2016-07-30', 1);
 2016-07-31
DATE_FORMAT
Function statement:
DATE_FORMAT(<ts> date|timestamp|string, <format> string)
Supported engines: SparkSQL and Presto
Usage instructions: Convert a timestamp to a string value in the specified date format.
Return type: string
Example:
> SELECT date_format('2016-04-08', 'y');
 2016
DATE_SUB
Function statement:
DATE_SUB(<start_date> date|timestamp|string, <num> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Return the date that is num days before start_date.
Return type: date
Example:
> SELECT date_sub('2016-07-30', 1);
 2016-07-29
DAY
Function statement:
DAY(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Calculate which day of the month the date/timestamp d is.
Return type: integer
Example:
> SELECT day('2009-07-30');
 30
DAYOFYEAR
Function statement:
DAYOFYEAR(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Calculate which day of the year the date/timestamp d is.
Return type: integer
Example:
> SELECT dayofyear('2016-04-09');
 100
DAYOFMONTH
Function statement:
DAYOFMONTH
Supported engines: SparkSQL and Presto
Usage instructions: Calculate which day of the month the date/timestamp d is.
Return type: integer
Example:
> SELECT dayofmonth('2009-07-30');
 30
FROM_UNIXTIME
Function statement:
FROM_UNIXTIME(<unix_time> bigint[, <fmt> string])
Supported engines: SparkSQL and Presto
Usage instructions: Return the date/time represented by unix_time in the format fmt. If fmt is omitted, the format 'yyyy-MM-dd HH:mm:ss' is used.
Return type: string
Example:
> SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
 1969-12-31 16:00:00
> SELECT from_unixtime(0);
 1969-12-31 16:00:00
FROM_UTC_TIMESTAMP
Function statement:
FROM_UTC_TIMESTAMP(<ts> timestamp, <timezone> string)
Supported engines: SparkSQL and Presto
Usage instructions: Given a UTC timestamp, present the time as a timestamp in the specified timezone.
Return type: timestamp
Example:
> SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul');
 2016-08-31 09:00:00
HOUR
Function statement:
HOUR(<ts> string|timestamp)
Supported engines: SparkSQL and Presto
Usage instructions: Return the hour part of the specified timestamp ts.
Return type: integer
Example:
> SELECT hour('2009-07-30 12:58:59');
 12
LAST_DAY
Function statement:
LAST_DAY(<d> date|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the last day of the current month for the date d.
Return type: date
Example:
> SELECT last_day('2009-01-12');
 2009-01-31
MINUTE
Function statement:
MINUTE(<ts> timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the minute part of the timestamp ts.
Return type: integer
Example:
> SELECT minute('2009-07-30 12:58:59');
 58
MONTH
Function statement:
MONTH(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the month of the date d.
Return type: integer
Example:
> SELECT month('2016-07-30');
 7
MONTHS_BETWEEN
Function statement:
MONTHS_BETWEEN(<ts1> date|timestamp|string, <ts2> date|timestamp|string, <roundOff> boolean)
Supported engines: SparkSQL and Presto
Usage instructions: If ts1 is later than ts2, the result is positive. If ts1 and ts2 are on the same day of the month or both are the last day of the month, the time of the day is ignored. Otherwise, the difference is calculated assuming 31 days per month and rounded to 8 decimal places unless roundOff is false.
Return type: double
Example:
> SELECT months_between('1997-02-28 10:30:00', '1996-10-30');
 3.94959677
> SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false);
 3.9495967741935485
NEXT_DAY
Function statement:
NEXT_DAY(<start_date> date|timestamp|string, <day_of_week> string
Supported engines: SparkSQL and Presto
Usage instructions: Return the first specified week after the start_date.
Return type:
SparkSQL:date
Presto:string
Example:
> SELECT next_day('2015-01-14', 'TU');
 2015-01-20
NOW
Function statement:
NOW()
Supported engines: SparkSQL and Presto
Usage instructions: Return the current timestamp.
Return type: timestamp
Example:
> SELECT now();
 2020-04-25 15:49:11.914
QUARTER
Function statement:
QUARTER(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the quarter of the year for date d.
Return type: integer
Example:
> SELECT quarter('2016-08-31');
 3
SECOND
Function statement:
SECOND(<ts> timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the seconds of the current timestamp.
Return type: integer
Example:
> SELECT second('2009-07-30 12:58:59');
 59
TO_TIMESTAMP
Function statement:
TO_TIMESTAMP(<ts_str> string[, <fmt> string]
Supported engines: SparkSQL and Presto
Usage instructions: Parse the ts_str expression in fmt format to a timestamp. If the input is invalid, NULL is returned. By default, if fmt is omitted, it follows the forced conversion rules for the timestamp. The result data type is consistent with the configuration value.
Return type: timestamp
Example:
> SELECT to_timestamp('2016-12-31 00:12:00');
 2016-12-31 00:12:00
> SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
 2016-12-31 00:00:00
TO_DATE
Function statement:
TO_DATE(<date_str> string[, <fmt> string])
Supported engines: SparkSQL and Presto
Usage instructions: Parse the date_str expression in fmt format to a date. If the input is invalid, NULL is returned. By default, if fmt is omitted, it follows the forced conversion rules for the date. The result data type is consistent with the configuration value.
Return type: date
Example:
> SELECT to_date('2009-07-30 04:17:52');
 2009-07-30
> SELECT to_date('2016-12-31', 'yyyy-MM-dd');
 2016-12-31
TO_UNIX_TIMESTAMP
Function statement:
TO_UNIX_TIMESTAMP(<ts> date|timestamp|string[, <fmt> string])
Supported engines: SparkSQL and Presto
Usage instructions: Return the unix timestamp of ts.
Return type: bigint
Example:
> SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd');
 1460098800
TO_UTC_TIMESTAMP
Function statement:
-- SparkSQL
TO_UTC_TIMESTAMP(<ts> date|timestamp|string, <timezone> string)
﻿
-- Presto
TO_UTC_TIMESTAMP(<ts> date|timestamp|string|interger|double|decimal, <timezone> string)
Supported engines: SparkSQL and Presto
Usage instructions: Convert a timestamp in the given timezone to UTC.
Return type: timestamp
Example:
-- SparkSQL
> SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul');
 2016-08-30 15:00:00
-- Presto
> select to_utc_timestamp(10000, 'UTC');
 1970-01-01 08:00:10
TRUNC
Function statement:
TRUNC(<d> date|string, <fmt> string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the date value obtained by truncating the date d to the time unit specified by fmt.
Return type: date
Example:
> SELECT trunc('2019-08-04', 'week');
 2019-07-29
> SELECT trunc('2019-08-04', 'quarter');
 2019-07-01
> SELECT trunc('2009-02-12', 'MM');
 2009-02-01
> SELECT trunc('2015-10-27', 'YEAR');
 2015-01-01
DATE_TRUNC
Function statement:
DATE_TRUNC(<fmt> string, <ts> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the timestamp obtained by truncating the timestamp ts according to fmt.
Return type: timestamp
Example:
> SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359');
 2015-01-01 00:00:00
> SELECT date_trunc('MM', '2015-03-05T09:32:05.359');
 2015-03-01 00:00:00
> SELECT date_trunc('DD', '2015-03-05T09:32:05.359');
 2015-03-05 00:00:00
> SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359');
 2015-03-05 09:00:00
> SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456');
 2015-03-05 09:32:05.123
UNIX_TIMESTAMP
Function statement:
UNIX_TIMESTAMP([<ts> date|timestamp|string[, fmt]])
Supported engines: SparkSQL and Presto
Usage instructions: Return the UNIX timestamp of the current or specified time.
Return type: bigint
Example:
> SELECT unix_timestamp();
 1476884637
> SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd');
 1460041200
DAYOFWEEK
Function statement:
DAYOFWEEK(<d> date|timestamp|string)
Supported engines: parkSQL and Presto
Usage instructions: Return the day of the week for the date/timestamp d.
Return type: nteger
Example:
> SELECT dayofweek('2009-07-30');
 5
WEEKDAY
Function statement:
WEEKDAY(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the day of the week for the date/timestamp d.
Return type: integer
Example:
> SELECT weekday('2009-07-30');
 3
WEEKOFYEAR
Function statement:
WEEKOFYEAR(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the week of the year for the given date d.
Return type: integer
Example:
> SELECT weekofyear('2008-02-20');
 8
YEAR
Function statement:
YEAR(<d> date|timestamp|string)
Supported engines: SparkSQL and Presto
Usage instructions: Return the year of the date/timestamp d.
Return type: integer
Example:
> SELECT year('2016-07-30');
 2016
MAKE_DATE
Function statement:
MAKE_DATE(<year> integer, <month> integer, <day> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Create a date from the year, month, and day fields.
Return type: date
Example:
> SELECT make_date(2013, 7, 15);
 2013-07-15
> SELECT make_date(2019, 7, NULL);
 NULL
MAKE_TIMESTAMP
Function statement:
MAKE_TIMESTAMP(<year> integer, <month> integer, <day> integer, <hour> integer, <min> integer, <sec> integer|double|decimal[, <timezone> string])
Supported engines: SparkSQL and Presto
Usage instructions: Create a timestamp according to the given fields.
Return type: timestamp
Example:
> SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
 2014-12-28 06:30:45.887
> SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET');
 2014-12-27 21:30:45.887
> SELECT make_timestamp(2019, 6, 30, 23, 59, 60);
 2019-07-01 00:00:00
> SELECT make_timestamp(2019, 6, 30, 23, 59, 1);
 2019-06-30 23:59:01
> SELECT make_timestamp(null, 7, 22, 15, 30, 0);
 NULL
DATE_PART
Function statement:
DATE_PART(<field> string, <source> date|timestamp)
Supported engines: SparkSQL and Presto
Usage instructions: Extract a part of the date/timestamp.
Return type: integer|double
Example:
> SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456');
 2019
> SELECT date_part('week', timestamp '2019-08-12 01:00:00.123456');
 33
> SELECT date_part('doy', DATE'2019-08-12');
 224
> SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001');
 1.000001
DATE_FROM_UNIX_DATE
Function statement:
DATE_FROM_UNIX_DATE(<unix_timestamp> integer)
Supported engines: SparkSQL and Presto
Usage instructions: Create a date based on the number of days since 01-01-1970.
Return type: date
Example:
> SELECT date_from_unix_date(1);
 1970-01-02
UNIX_DATE
Function statement:
UNIX_DATE(<d> date)
Supported engines: SparkSQL and Presto
Usage instructions: Return the number of days since 01-01-1970.
Return type: integer
Example:
> SELECT unix_date(DATE("1970-01-02"));
 1
TIMESTAMP_SECONDS
Function statement:
TIMESTAMP_SECONDS(<sec> bigint|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Create a timestamp from the number of seconds since the UTC epoch.
Return type: timestamp
Example:
> SELECT timestamp_seconds(1230219000);
 2008-12-25 07:30:00
> SELECT timestamp_seconds(1230219000.123);
 2008-12-25 07:30:00.123
TIMESTAMP_MILLIS
Function statement:
TIMESTAMP_MILLIS(<milli> bigint|double|decimal)
Supported engines: SparkSQL and Presto
Usage instructions: Create a timestamp from the number of milliseconds since the UTC epoch.
Return type: timestamp
Example:
> SELECT timestamp_millis(1230219000123);
 2008-12-25 07:30:00.123
TIMESTAMP_MICROS
Function statement:
TIMESTAMP_MICROS(<micro> bigint)
Supported engines: SparkSQL and Presto
Usage instructions: Create a timestamp from the number of milliseconds since the UTC epoch.
Return type: timestamp
Example:
> SELECT timestamp_micros(1230219000123123);
 2008-12-25 07:30:00.123123
UNIX_SECONDS
Function statement:
UNIX_SECONDS(<ts> timestamp)
Supported engines: SparkSQL and Presto
Usage instructions: Return the number of seconds since 01-01-1970 00:00:00 UTC.
Return type: bigint
Example:
> SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z'));
 1
UNIX_MILLIS
Function statement:
UNIX_MILLIS(<ts> timestamp)
Supported engines: SparkSQL and Presto
Usage instructions: Return the number of milliseconds since 01-01-1970 00:00:00 UTC.
Return type: bigint
Example:
> SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z'));
 1000
UNIX_MICROS
Function statement:
UNIX_MICROS(<ts> timestamp)
Supported engine: SparkSQL
Usage instructions: Return the number of microseconds since 01-01-1970 00:00:00 UTC.
Return type: bigint
Example:
> SELECT unix_micros(timestamp '1970 00:00:00.001')
 1000
DATEADD/TIMESTAMP_ADD
Function statement:
TIMESTAMP_ADD(<date|timestamp> dt, <int> delta, <string> pattern)
TIMESTAMP_ADD(<date|timestamp> dt, <int> delta, <string> pattern)
Supported engine: SparkSQL
Engine version requirement: An engine purchased on or after December 7, 2023, or upgrade to the latest engine.
Usage instructions: Calculate the final time based on the specified unit and offset on the given date or time.
dt: Input date or time format. If it is null, null is returned.
delta: Required, int type. If it is null, null is returned.
pattern: Required, string type, unit. Pattern supports:
Year (standard y, compatible with -year and yyyy)
Month (standard M, compatible with -month, -mon, and MM)
Day (standard d, compatible with -day and dd)
Hour (standard H, compatible with -hour, hh, HH, and h)
Minute (standard m, compatible with mi and mm)
Second (standard s, compatible with ss)
Microsecond (standard S, compatible with SSS)
Other types are not allowed, otherwise an error will occur.
Note:
When the unit of delta is months, if adding the delta value to the month part does not cause a Day overflow, the Day value remains unchanged; otherwise, the Day value is set to the last day of the resulting month.
Note that the output time is in the default timezone.
Difference between the units for month M and minute m; supports both 12-hour format h and 24-hour format H, but it is recommended to use H.
Return type: timestamp
Example:
> SELECT timestamp_add(date '2016-12-31', -1, 'M');
2016-11-30T00:00:00.000+08:00
> SELECT timestamp_add(timestamp '2016-12-31 00:12:00', 1, 'm');
2016-12-31T00:13:00.000+08:00
> SELECT timestamp_add(timestamp '2016-12-31 00:00:00', -1, 'm');
2016-12-30T23:59:00.000+08:00
﻿
﻿
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

Sign Up

Log in

Recent Pages

Date and Time Functions

DATE

TIMESTAMP

ADD_MONTHS

CURRENT_DATE

CURRENT_TIMESTAMP

CURRENT_TIMEZONE

DATEDIFF

DATE_ADD

DATE_FORMAT

DATE_SUB

DAY

DAYOFYEAR

DAYOFMONTH

FROM_UNIXTIME

FROM_UTC_TIMESTAMP

HOUR

LAST_DAY

MINUTE

MONTH

MONTHS_BETWEEN

NEXT_DAY

NOW

QUARTER

SECOND

TO_TIMESTAMP

TO_DATE

TO_UNIX_TIMESTAMP

TO_UTC_TIMESTAMP

TRUNC

DATE_TRUNC

UNIX_TIMESTAMP

DAYOFWEEK

WEEKDAY

WEEKOFYEAR

YEAR

MAKE_DATE

MAKE_TIMESTAMP

DATE_PART

DATE_FROM_UNIX_DATE

UNIX_DATE

TIMESTAMP_SECONDS

TIMESTAMP_MILLIS

TIMESTAMP_MICROS

UNIX_SECONDS

UNIX_MILLIS

UNIX_MICROS

DATEADD/TIMESTAMP_ADD

Was this page helpful?

Was this page helpful?
