Help Center/ MapReduce Service/ Component Operation Guide (LTS)/ Using HetuEngine/ Common HetuEngine SQL Syntax/ SQL Functions and Operators/ Date and Time Functions and Operators

Updated on 2024-10-25 GMT+08:00

View PDF

Date and Time Functions and Operators

Date and Time Operators

Operator	Example	Result
+	date '2012-08-08' + interval '2' day	2012-08-10
+	time '01:00' + interval '3' hour	04:00:00.000
+	timestamp '2012-08-08 01:00' + interval '29' hour	2012-08-09 06:00:00.000
+	timestamp '2012-10-31 01:00' + interval '1' month	2012-11-30 01:00:00.000
+	interval '2' day + interval '3' hour	2 03:00:00.000
+	interval '3' year + interval '5' month	3-5
-	date '2012-08-08' - interval '2' day	2012-08-06
-	time '01:00' - interval '3' hour	22:00:00.000
-	timestamp '2012-08-08 01:00' - interval '29' hour	2012-08-06 20:00:00.000
-	timestamp '2012-10-31 01:00' - interval '1' month	2012-09-30 01:00:00.000
-	interval '2' day - interval '3' hour	1 21:00:00.000
-	interval '3' year - interval '5' month	2-7

Time Zone Conversion

Operator: AT TIME ZONE sets the time zone of a timestamp.

SELECT timestamp '2012-10-31 01:00 UTC';-- 2012-10-31 01:00:00.000 UTC
SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'Asia/Singapore'; -- 2012-10-30 09:00:00.000 Asia/Singapore

Date/Time Functions

current_date -> date
Returns the current date (UTC time zone).
```
select current_date; -- 2020-07-25 
```

current_time -> time with time zone
Returns the current time (UTC time zone).
```
select current_time;-- 16:58:48.601+08:00
```
current_timestamp -> timestamp with time zone
Returns the current timestamp (current time zone).
```
select current_timestamp; -- 2020-07-25 11:50:27.350 Asia/Singapore
```
current_timezone() → varchar
Returns the current time zone.
```
select current_timezone();-- Asia/Singapore 
```
date(x) → date
Converts a date literal to a variable of the date type.
```
select date('2020-07-25');-- 2020-07-25
```

from_iso8601_timestamp(string) → timestamp with time zone

Converts a timestamp literal in ISO 8601 format into a timestamp variable with a time zone.

SELECT from_iso8601_timestamp('2020-05-11');-- 2020-05-11 00:00:00.000 Asia/Singapore
SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000 Asia/Singapore 
SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00');-- 2020-05-11 11:15:05.055 +01:00

from_iso8601_date(string) → date

Converts a date literal in ISO 8601 format into a variable of the date type.

SELECT from_iso8601_date('2020-05-11');-- 2020-05-11
SELECT from_iso8601_date('2020-W10');-- 2020-03-02
SELECT from_iso8601_date('2020-123');-- 2020-05-02

from_unixtime(unixtime) → timestamp with time zone

Converts a UNIX timestamp to a timestamp variable (current time zone).

Select FROM_UNIXTIME(1.595658735E9); -- 2020-07-25 14:32:15.000 Asia/Singapore
Select FROM_UNIXTIME(875996580); --1997-10-05 04:23:00.000 Asia/Singapore

from_unixtime(unixtime, string) → timestamp with time zone
Converts a UNIX timestamp into a timestamp variable. The time zone option can be contained.
```
select from_unixtime(1.595658735E9, 'Asia/Singapore');-- 2020-07-25 14:32:15.000 Asia/Singapore 
```
from_unixtime(unixtime, hours, minutes) → timestamp with time zone
Converts a UNIX timestamp to a timestamp variable with a time zone. hours and minutes indicate the time zone offsets.
```
select from_unixtime(1.595658735E9, 8, 30);-- 2020-07-25 14:32:15.000 +08:30
```
localtime -> time
Obtains the current time
```
select localtime;-- 14:16:13.096 
```
localtimestamp -> timestamp
Obtains the current timestamps.
```
select localtimestamp;-- 2020-07-25 14:17:00.567 
```
months_between(date1, date2) -> double
Return the number of months between date1 and date2. If date1 is later than date2, the result is a positive number. Otherwise, the result is a negative number. If the days of the two dates are the same, the result is an integer. Otherwise, the decimal part is calculated based on the difference between the hour, minute, and second (31 days of each month). The type of date1 and date2 can be date, timestamp, or a string in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format.
```
select months_between('2020-02-28 10:30:00', '2021-10-30');-- -20.05040323
select months_between('2021-01-30', '2020-10-30'); -- 3.0
```
now() → timestamp with time zone
Obtains the current time, which is the alias of current_timestamp.
```
select now();-- 2020-07-25 14:39:39.842 Asia/Singapore
```
unix_timestamp()
Obtains the current unix timestamp.
```
select unix_timestamp(); -- 1600930503
```

to_iso8601(x) → varchar

Converts x into a character string in the ISO 8601 format. x can be DATE or TIMESTAMP [with time zone].

select to_iso8601(date '2020-07-25'); -- 2020-07-25
select to_iso8601(timestamp '2020-07-25 15:22:15.214'); -- 2020-07-25T15:22:15.214

to_milliseconds(interval) → bigint
Obtains the number of milliseconds since 00:00 on the current day.
```
select to_milliseconds(interval '8' day to second);-- 691200000
```

to_unixtime(timestamp) → double

Converts the timestamp to the UNIX time.

select to_unixtime(cast('2020-07-25 14:32:15.147' as timestamp));-- 1.595658735147E9

trunc(string date, string format) →string
Truncates a date value based on the format. The supported format is MONTH/MON/MM or YEAR/YYYY/YY, QUARTER/Q
```
select trunc(date '2020-07-08','yy');-- 2020-01-01
select trunc(date '2020-07-08','MM');-- 2020-07-01
```
You can use the parentheses () when using the following SQL standard functions:
- current_date
- current_time
- current_timestamp
- localtime
- Localtimestamp
For example: select current_date ();

Truncation Function

Similar to the operation of reserving decimal places, the date_trunc function supports the following units:

Unit	Value After Truncation
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

In the preceding example, the timestamp 2001-08-22 03:04:05.321 is used as the input.

date_trunc(unit, x) → [same as input]

Returns the value of x after the unit.

select date_trunc('hour', timestamp '2001-08-22 03:04:05.321'); -- 2001-08-22 03:00:00.000

Interval Functions

The functions in this chapter support the following interval units:

Unit	Description
second	Seconds
minute	Minutes
hour	Hours
day	Days
week	Weeks
month	Months
quarter	Quarters of a year
year	Years

date_add(unit, value, timestamp) → [same as input]

Add value units to timestamp. If you want to perform the subtraction operation, you can assign a negative value to the value.

SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 00:01:26
SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 09:00:00
SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC');-- 2020-02-29 00:00:00 UTC

date_diff(unit, timestamp1, timestamp2) → bigint

Returns the value of timestamp2 minus timestamp1. The unit of the value is unit.

The value of unit is a character string. For example, day, week, and year.

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');-- 86400
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');-- 24
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');-- 1
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400123

adddate(date, bigint)→ [same as input]
Description: Date addition. The input type can be date or timestamp, indicating that the date is added or deducted. If the input type is subtraction, the value of bigint is negative.
```
select ADDDATE(timestamp '2020-07-04 15:22:15.124',-5);-- 2020-06-29 15:22:15.124 
select ADDDATE(date '2020-07-24',5); -- 2020-07-29
```

Duration Function

The duration can use the following units:

Unit	Description
ns	nanosecond
us	microsecond
ms	millisecond
s	second
m	minute
h	hour
d	day

parse_duration(string) → interval

SELECT parse_duration('42.8ms'); -- 0 00:00:00.043
SELECT parse_duration('3.81 d'); -- 3 19:26:24.000
SELECT parse_duration('5m'); -- 0 00:05:00.000

MySQL Date Functions

The formatted strings that are compatible with the MySQL date_parse and str_to_date methods in this section.

date_format(timestamp, format) → varchar

Uses format to format timestamp.

select date_format(timestamp '2020-07-22 15:00:15', '%Y/%m/%d');-- 2020/07/22

date_parse(string, format) → timestamp
Parses the date literals using format.
```
select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 
```

The following table is based on the MySQL manual and describes various format descriptors.

Format Descriptor	Description
%a	Day in a week (Sun .. Sat)
%b	Month (Jan .. Dec)
%c	Month (1 .. 12)
%D	Day of the month (0th, 1st, 2nd, 3rd, ...)
%d	Day in the month (01.. 31) (Two digits. Zeros are added before the single digits.)
%e	Day in the month (1 .. 31)
%f	Seconds after the decimal point (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999)
%H	Hour (00 .. 23)
%h	Hour (01.. 12)
%I	Hour (01.. 12)
%i	Minute, number (00 .. (59)
%j	Day of the year (001 .. 366)
%k	Hour (0 .. 23)
%l	Hour (1.. 12)
%M	Month name (January .. December)
%m	Month, number (01 .. 12)
%p	AM or PM
%r	Time in the 12-hour format (hh:mm:ss followed by AM or PM)
%S	Second (00 .. 59)
%s	Second (00 .. 59)
%T	Time in the 24-hour format (hh:mm:ss)
%U	Week (00 .. 53) Sunday is the first day of a week.
%u	Week (00 .. 53) Monday is the first day of a week.
%V	Week (01.. 53) Sunday is the first day of a week. Used together with %X.
%v	Week (01 .. 53) Monday is the first day of a week. Used together with %X.
%W	Day of the week (Sunday .. Saturday)
%w	Day of the week (0.. 6) Sunday is the first day of a week.
%X	Year, a four-digit number. The first day is Sunday.
%x	Year, a four-digit number. The first day is Monday.
%Y	Year, a four-digit number.
%y	Year. The value is a two-digit number ranging from 1970 to 2069.
%%	Indicates the character '%'.

Example:

select date_format(timestamp '2020-07-25 15:04:00.124','%j day of a year with English suffix (0th, 1st, 2nd, 3rd...),%m month %d day,%p %T %W');
                       _col0                       
---------------------------------------------------
 207th day of the year, 25th day of July, PM 15:04:00 Saturday
(1 row)

These format descriptors are not supported: %D, %U, %u, %V, %w, %X.

date_format(timestamp, format) → varchar
Uses format to format timestamp.
date_parse(string, format) → timestamp
Parses the timestamp character string.
```
select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 
```

Java Date Functions

The formatting strings used in this section are compatible with the Java SimpleDateFormat style.

format_datetime(timestamp, format) → varchar
Uses format to format timestamp.

parse_datetime(string, format) → timestamp with time zone

Format a string to timestamp with time zone in a specified format.

select parse_datetime('1960/01/22 03:04', 'yyyy/MM/dd HH:mm');
                 _col0                 
---------------------------------------
 1960-01-22 03:04:00.000 Asia/Shanghai 
(1 row)

Common Extraction Functions

Domain	Description
YEAR	year()
QUARTER	quarter()
MONTH	month()
WEEK	week()
DAY	day()
DAY_OF_MONTH	day_of_month()
DAY_OF_WEEK	day_of_week()
DOW	day_of_week()
DAY_OF_YEAR	day_of_year()
DOY	day_of_year()
YEAR_OF_WEEK	year_of_week()
YOW	year_of_week()
HOUR	hour()
MINUTE	minute()
SECOND	second()
TIMEZONE_HOUR	timezone_hour()
TIMEZONE_MINUTE	timezone_minute()

Example:

select second(timestamp '2020-02-12 15:32:33.215');-- 33
select timezone_hour(timestamp '2020-02-12 15:32:33.215');-- 8

MONTHNAME(date)

Description: Obtains the month name.

SELECT monthname(timestamp '2019-09-09 12:12:12.000');-- SEPTEMBER
SELECT monthname(date '2019-07-09');--JULY

extract(field FROM x) → bigint

Description: Returns the field from x. For details about the corresponding field, see the table in this document.

select extract(YOW FROM timestamp '2020-02-12 15:32:33.215');-- 2020
select extract(SECOND FROM timestamp '2020-02-12 15:32:33.215');-- 33
select extract(DOY FROM timestamp '2020-02-12 15:32:33.215');--43

Function	Example	Description
SECONDS_ADD(TIMESTAMP date, INT seconds)	SELECT seconds_add(timestamp '2019-09-09 12:12:12.000', 10);	The time is added in seconds.
SECONDS_SUB(TIMESTAMP date, INT seconds)	SELECT seconds_sub(timestamp '2019-09-09 12:12:12.000', 10);	Subtracts time in seconds.
MINUTES_ADD(TIMESTAMP date, INT minutes)	SELECT MINUTES_ADD(timestamp '2019-09-09 12:12:12.000', 10);	Add the time in the unit of minute.
MINUTES_SUB(TIMESTAMP date, INT minutes)	SELECT MINUTES_SUB(timestamp '2019-09-09 12:12:12.000', 10);	The time is subtracted in the unit of minute.
HOURS_ADD(TIMESTAMP date, INT hours)	SELECT HOURS_ADD(timestamp '2019-09-09 12:12:12.000', 1);	Add the time in the unit of hour.
HOURS_SUB(TIMESTAMP date, INT hours)	SELECT HOURS_SUB(timestamp '2019-09-09 12:12:12.000', 1);	The time is subtracted in hours.

last_day(timestamp) -> date

Description: Returns the last day of each month based on the specified timestamp.

SELECT last_day(timestamp '2019-09-09 12:12:12.000');--  2019-09-30
SELECT last_day(date '2019-07-09');--2019-07-31

add_months(timestamp) -> [same as input]
Description: Returns the correct date by adding the specified date to the specified month.
```
SELECT add_months(timestamp'2019-09-09 00:00:00.000', 11);-- 2020-08-09 00:00:00.000
```

next_day() (timestamp, string) -> date

Description: Returns the next day of the specified weekday based on the specified date.

SELECT next_day(timestamp'2019-09-09 00:00:00.000', 'monday');-- 2019-09-16 00:00:00.000
SELECT next_day(date'2019-09-09', 'monday');-- 2019-09-16

numtoday(integer) -> BIGINT
Description: Converts transferred integer values to values of the day type, for example, BIGINT.
```
SELECT numtoday(2);-- 2
```