Date and time functions

CURRENT_TIME

Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

CURRENT_DATE

Returns the current date in the session time zone, in a value of datatype DATE

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE

Cast strings to timestamps

Note that datetime values displayed in EBX (UI and export files) are in local time zone, whereas the timestamp literals created by the SQL parser are in UTC; therefore if the server is not in UTC, you might need to create the literals on the local time zone to avoid this mismatch and get the expected results (see third example). You should also consider if the datetime value has millisecond precision (which is not displayed by EBX UI) in order to write your filter (see last example).

SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=TIMESTAMP'2017-06-28 18:06:12' :  0

SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=CAST('2017-06-28 18:06:12' AS TIMESTAMP) :  0

SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=CAST('2017-06-28 18:06:12 Europe/Paris' AS TIMESTAMP WITH LOCAL TIME ZONE) :  1

SELECT COUNT(*) FROM employee e WHERE e."ebx-metadata"."system"."update_time">=TIMESTAMP'2023-08-28 09:56:55' AND e."ebx-metadata"."system"."update_time"<TIMESTAMP'2023-08-28 09:56:56' :  1

YEAR(date)

Extracts and returns the value of the year from a datetime value expression. Returns an integer.

SELECT YEAR(TIMESTAMP '1971-07-20 09:34:21') :  1971

SELECT YEAR(DATE '1968-07-20') :  1968

SELECT YEAR(hiringDate) FROM employee WHERE name='Smith' :  2015

SELECT YEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  2017

QUARTER(date)

Extracts and returns the value of the quarter from a datetime value expression. Returns an integer between 1 and 4.

SELECT QUARTER(TIMESTAMP '1971-07-20 09:34:21') :  3

SELECT QUARTER(hiringDate) FROM employee WHERE name='Smith' :  4

SELECT QUARTER(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  2

QUARTER(date)

Extracts and returns the value of the month from a datetime value expression. Returns an integer between 1 and 12.

SELECT MONTH(TIMESTAMP '1971-07-20 09:34:21') :  7

SELECT MONTH(hiringDate) FROM employee WHERE name='Smith' :  10

SELECT MONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  6

WEEK(date)

Extracts and returns the value of the week from a datetime value expression. Returns an integer between 1 and 53.

SELECT WEEK(TIMESTAMP '1971-07-20 09:34:21') :  29

SELECT WEEK(hiringDate) FROM employee WHERE name='Smith' :  42

SELECT WEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  26

DAYOFYEAR(date)

Extracts and returns the value of the day of year from a datetime value expression. Returns an integer between 1 and 366.

SELECT DAYOFYEAR(TIMESTAMP '1971-07-20 09:34:21') :  201

SELECT DAYOFYEAR(hiringDate) FROM employee WHERE name='Smith' :  287

SELECT DAYOFYEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  179

DAYOFMONTH(date)

Extracts and returns the value of the day of month from a datetime value expression. Returns an integer between 1 and 31.

SELECT DAYOFMONTH(TIMESTAMP '1971-07-20 09:34:21') :  20

SELECT DAYOFMONTH(hiringDate) FROM employee WHERE name='Smith' :  14

SELECT DAYOFMONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  28

DAYOFWEEK(date)

Extracts and returns the value of the day of week from a datetime value expression. Returns an integer between 1 and 7.

SELECT DAYOFWEEK(TIMESTAMP '1971-07-20 09:34:21') :  3

SELECT DAYOFWEEK(hiringDate) FROM employee WHERE name='Smith' :  4

SELECT DAYOFWEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  4

HOUR(date)

Extracts and returns the value of the hours from a datetime value expression. Returns an integer between 0 and 23.

SELECT HOUR(TIMESTAMP '1971-07-20 09:34:21') :  9

SELECT HOUR(TIME '10:34:21') :  10

SELECT HOUR(workStart) FROM employee WHERE name='Smith' :  8

SELECT HOUR(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  16

MINUTE(date)

Extracts and returns the value of the minutes from a datetime value expression. Returns an integer between 0 and 59.

SELECT MINUTE(TIMESTAMP '1971-07-20 09:34:21') :  34

SELECT MINUTE(TIME '10:35:21') :  35

SELECT MINUTE(workStart) FROM employee WHERE name='Smith' :  0

SELECT MINUTE(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  6

SECOND(date)

Extracts and returns the value of the seconds from a datetime value expression. Returns an integer between 0 and 59.

SELECT HOUR(TIMESTAMP '1969-07-20 09:34:21') :  9

SELECT SECOND(TIME '10:34:22') :  22

SELECT SECOND(workStart) FROM employee WHERE name='Smith' :  0

SELECT SECOND(lastProfileUpdateTime) FROM employee WHERE name='Smith' :  12