Date and Time

Operators

timestamp_value[+ amount time_unit]

Adds amount of time_units to timestamp_value.

Time units:
ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type:TIMESTAMP

Example:

1
dual | select(t, t_add:t[+1 min]);
t t_add
2019-09-18 11:57:58.533 +0000 2019-09-18 11:58:58.533 +0000

timestamp_value[- amount time_unit]

Subtracts amount of time_units from timestamp_value.

Time units:
ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type:TIMESTAMP

Example:

1
dual | select(t, t_sub:t[-1 sec]);
t t_sub
2019-09-18 12:00:32.888 +0000 2019-09-18 12:00:31.888 +0000

timestamp_value[amount time_unit]

timestamp_value[% amount time_unit]

Truncates the timestamp_value to amount of time_units.

Time units:
ms or millis, sec or s, min or m, hour or h, day or d, week or w
output_type:TIMESTAMP

Example:

1
dual | select(t, t_trunc:t[1 hour]);
t t_trunc
2019-09-18 12:04:45.839 +0000 2019-09-18 12:00:00.000 +0000

DAY

DAY(timestamp_expr)

Returns an integer representing the day of the month from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(DAY(T('2017-01-22 15:00:00')));
day
22

DAY_OF_WEEK

DAY_OF_WEEK(timestamp_expr)

Returns an integer representing 1 - 7 day of the week from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(DAY_OF_WEEK(T('2017-01-22 15:00:00')));
day_of_week
7

DAY_OF_YEAR

DAY_OF_YEAR(timestamp_expr)

Returns integer representing 1 - 365 day of the year (or 366 in the leap year) from timestamp_expr

output_type:INTEGER

Example:

1
dual | select(DAY_OF_YEAR(T('2017-05-22 15:00:00')));
day_of_year
142

HOUR

HOUR(timestamp_expr)

Returns an integer representing 0 - 23 hour of the day from timestamp_expr

output_type:INTEGER

Example:

1
dual | select(HOUR(T('2016-09-23 17:58:00')));
hour
17

MINUTE

MINUTE(timestamp_expr)

Returns an integer representing 0 - 59 minute of the hour from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(MINUTE(T('2016-09-23 17:58:00')));
minute
58

MONTH

MONTH(timestamp_expr)

Returns an integer representing 1 - 12 month of the year from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select (MONTH(T('2016-09-23 17:58:00')));
month
9

NOW

NOW()

Returns the query execution start timestamp.

output_type:TIMESTAMP

Example:

1
dual(5) | select(t, NOW());
t now
2019-09-18 12:22:34.036 +0000 2019-09-18 12:22:34.036 +0000
2019-09-18 12:22:34.037 +0000 2019-09-18 12:22:34.036 +0000
2019-09-18 12:22:34.038 +0000 2019-09-18 12:22:34.036 +0000
2019-09-18 12:22:34.039 +0000 2019-09-18 12:22:34.036 +0000
2019-09-18 12:22:34.040 +0000 2019-09-18 12:22:34.036 +0000

NANO_NOW

NANO_NOW()

Returns the query execution start timestamp with a nanosecond precision.

output_type:TIMESTAMP_NANO

Example:

1
dual(5) | select(t, NANO_NOW());
t nano_now
2019-09-18 12:24:54.410 +0000 2019-09-18 12:24:54.410000000 +0000
2019-09-18 12:24:54.411 +0000 2019-09-18 12:24:54.410000000 +0000
2019-09-18 12:24:54.412 +0000 2019-09-18 12:24:54.410000000 +0000
2019-09-18 12:24:54.413 +0000 2019-09-18 12:24:54.410000000 +0000
2019-09-18 12:24:54.414 +0000 2019-09-18 12:24:54.410000000 +0000

SECOND

SECOND(timestamp_expr)

Returns integer representing 0 - 59 second of the minute from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(SECOND(T('2016-09-23 17:58:32')));
second
32

STR_SEC_TO_TIME

STR_SEC_TO_TIME(seconds_str)

Converts seconds_str seconds, elapsed from Unix epoch to TIMESTAMP.

output_type:TIMESTAMP

Example:

1
dual | select(STR_SEC_TO_TIME("1552603315"));
str_sec_to_time
2019-03-14 22:41:55.000 +0000

STR_SEC_TO_TIME_NANO

STR_SEC_TO_TIME_NANO(seconds_str)

Converts seconds_str seconds, elapsed from Unix epoch to TIMESTAMP_NANO.

output_type:TIMESTAMP_NANO

Example:

1
dual | select(STR_SEC_TO_TIME_NANO("1552603315"));
str_sec_to_time_nano
2019-03-14 22:41:55.000000000 +000

STR_TO_TIME

STR_TO_TIME(string)

Converts string to TIMESTAMP. Argument string must be in one of the:

  • “yyyy-MM-dd HH:mm:ss”
  • “yyyy-MM-dd HH:mm:ss Z”
  • “yyyy-MM-dd HH:mm:ss.SSS”
  • “yyyy-MM-dd HH:mm:ss.SSS Z”
output_type:TIMESTAMP

Example:

1
dual | select(STR_TO_TIME('2016-09-23 17:58:00'));
t
2016-09-23 17:58:00.000 +0000

STR_TO_TIME_NANO

STR_TO_TIME_NANO(string)

Converts string to TIMESTAMP. Argument string must be in one of the:

  • “yyyy-MM-dd HH:mm:ss”
  • “yyyy-MM-dd HH:mm:ss Z”
  • “yyyy-MM-dd HH:mm:ss.SSS”
  • “yyyy-MM-dd HH:mm:ss.SSS Z”
output_type:TIMESTAMP_NANO

Example:

1
dual | select(STR_TO_TIME_NANO('2016-09-23 17:58:00'));
str_to_time_nano
2016-09-23 17:58:00.000000000 +0000

SYS_TIME

SYS_TIME()

Returns the current timestamp from the operating system clock.

output_type:TIMESTAMP

Example:

1
dual | select(SYS_TIME());
sys_time
2019-09-18 12:50:14.703 +0000

SYS_NANO_TIME

SYS_NANO_TIME()

Returns current timestamp from the operating system clock with a nanosecond precision.

output_type:TIMESTAMP_NANO

Example:

1
dual | select(SYS_NANO_TIME());
sys_nano_time
2019-09-17 19:26:08.581760066 +0000

TIME_ADD

TIME_ADD(timestamp, millis)

Adds millis of milliseconds to timestamp.

output_type:TIMESTAMP

Example:

1
dual(2) | select(t, TIME_ADD(t, 1000));
t time_add
2019-09-18 13:00:00.843 +0000 2019-09-18 13:00:01.843 +0000
2019-09-18 13:00:00.844 +0000 2019-09-18 13:00:01.844 +0000

TIME_SUB

TIME_SUB(timestamp, millis)

Subtracts millis milliseconds from timestamp.

output_type:TIMESTAMP

Example:

1
dual(2) | select(t, TIME_SUB(t, 60*1000));
t time_sub
2019-09-18 13:03:15.298 +0000 2019-09-18 13:02:15.298 +0000
2019-09-18 13:03:15.299 +0000 2019-09-18 13:02:15.299 +0000

TIME_SUBMOD

TIME_SUBMOD(timestamp, millis)

Returns timestamp modulo millis - i.e truncates timestamp to the precision of millis milliseconds.

output_type:TIMESTAMP

Example Truncate timestamp to the precision of hour:

1
dual | select(TIME_SUBMOD(T('2016-09-23 17:58:00'), 60*60*1000));
time_submod
2016-09-23 17:00:00.000 +0000

TIME_TO_STR

TIME_TO_STR(timestamp, format_str)

Converts timestamp to string as specified by format_str.

output_type:STRING

Example:

1
dual | select(TIME_TO_STR(t, "EEEE, MMMM d 'anno domini' yyyy") );
time_to_str
Wednesday, September 18 anno domini 2019

TO_STR_SEC

TO_STR_SEC(timestamp)

Converts timestamp to string of floating-point seconds elapsed from Unix epoch (the digits in the fraction part represent milliseconds).

output_type:STRING

Example:

1
dual | select(t, TO_STR_SEC(t));
t to_str_sec
2019-09-18 13:25:24.438 +0000 1568813124.438

YEAR

YEAR(timestamp_expr)

Returns an integer representing year from timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(YEAR(T('2017-01-22 15:00:00')));
year
2017