tencent cloud

All product documents
Data Lake Compute
DocumentationData Lake ComputeSQL StatementSuperSQL StatementFunctionsUnified FunctionsDate and Time Functions
Date and Time Functions
Download PDF
Last updated: 2024-08-07 17:32:05
Date and Time Functions
Last updated: 2024-08-07 17:32:05
Download PDF

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


Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No

Feedback

Contact Us

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

Contact Us
Technical Support

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

Submit a Ticket
7x24 Phone Support