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(now(), now_add_1_min:now()[+1 min]);
now now_add_1_min
2020-04-30 12:26:30.354 +0300 2020-04-30 12:27:30.354 +0300

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(now(), time_subtract_1_sec:now()[-1 sec]);
now time_subtract_1_sec
2020-04-30 12:28:25.235 +0300 2020-04-30 12:28:24.235 +0300

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(now(), now_truncated_1_h:now()[1 hour]);
now now_truncated_1_h
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_ADD_DAY

TIME_ADD_DAY(timestamp, numeric_expr)

Adds numeric_expr of days to timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_year_from_now:TIME_ADD_DAY(NOW(), 365));
now a_year_from_now
2020-07-06 13:17:13.828 +0300 2021-07-06 13:17:13.828 +0300

TIME_ADD_WEEK

TIME_ADD_WEEK(timestamp, numeric_expr)

Adds numeric_expr of weeks to timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), about_a_year_from_now:TIME_ADD_WEEK(NOW(), 52))
now about_a_year_from_now
2020-07-06 13:20:14.238 +0300 2021-07-05 13:20:14.238 +0300

TIME_ADD_MONTH

TIME_ADD_MONTH(timestamp, numeric_expr)

Adds numeric_expr of months to timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), about_a_year_from_now:TIME_ADD_MONTH(NOW(), 12))
now about_a_year_from_now
2020-07-06 13:21:23.209 +0300 2021-07-06 13:21:23.209 +0300

TIME_ADD_YEAR

TIME_ADD_YEAR(timestamp, numeric_expr)

Adds numeric_expr of months to timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_century_from_now:TIME_ADD_YEAR(NOW(), 100))
now a_century_from_now
2020-07-06 13:23:22.497 +0300 2120-07-06 13:23:22.497 +0300

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_SUB_DAY

TIME_SUB_DAY(timestamp, numeric_expr)

Subtracts numeric_expr days from timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_day_ago:TIME_SUB_DAY(NOW(), 1));
now a_day_ago
2020-07-06 13:26:22.474 +0300 2020-07-05 13:26:22.474 +0300

TIME_SUB_WEEK

TIME_SUB_DAY(timestamp, numeric_expr)

Subtracts numeric_expr weeks from timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_week_ago:TIME_SUB_WEEK(NOW(), 1));
now a_week_ago
2020-07-06 13:27:33.045 +0300 2020-06-29 13:27:33.045 +0300

TIME_SUB_MONTH

TIME_SUB_MONTH(timestamp, numeric_expr)

Subtracts numeric_expr months from timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_month_ago:TIME_SUB_MONTH(NOW(), 1));
now a_month_ago
2020-07-06 13:29:10.589 +0300 2020-06-06 13:29:10.589 +0300

TIME_SUB_YEAR

TIME_SUB_YEAR(timestamp, numeric_expr)

Subtracts numeric_expr years from timestamp and returns resulting TIMESTAMP value.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), a_century_ago:TIME_SUB_YEAR(NOW(), 100));
now a_century_ago
2020-07-06 13:30:25.365 +0300 1920-07-06 13:30:25.365 +0200

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_SUBMOD_DAY

TIME_SUBMOD_DAY(timestamp, numeric_expr)

Returns timestamp truncated to the precision of numeric_expr days.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), trunc_day:TIME_SUBMOD_DAY(NOW(), 1));
now trunc_day
2020-07-06 13:36:36.265 +0300 2020-07-06 00:00:00.000 +0300

TIME_SUBMOD_WEEK

TIME_SUBMOD_WEEK(timestamp, numeric_expr)

Returns timestamp truncated to the precision of numeric_expr weeks.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), trunc_4_week:TIME_SUBMOD_WEEK(NOW(), 4));
now trunc_4_week
2020-07-06 13:38:37.376 +0300 2020-06-15 00:00:00.000 +0300

TIME_SUBMOD_MONTH

TIME_SUBMOD_WEEK(timestamp, numeric_expr)

Returns timestamp truncated to the precision of numeric_expr months.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), trunc_1_month:TIME_SUBMOD_MONTH(NOW(), 1));
now trunc_1_month
2020-07-06 13:43:27.733 +0300 2020-07-01 00:00:00.000 +0300

TIME_SUBMOD_YEAR

TIME_SUBMOD_YEAR(timestamp, numeric_expr)

Returns timestamp truncated to the precision of numeric_expr years.

output_type:TIMESTAMP

Example:

1
dual | select(now:NOW(), trunc_1_year:TIME_SUBMOD_YEAR(NOW(), 1));
now trunc_1_year
2020-07-06 13:44:38.594 +0300 2020-01-01 00:00:00.000 +0200

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

WEEK_OF_YEAR

WEEK_OF_YEAR(timestamp_expr)

Returns an integer representing the week of the year of timestamp_expr.

output_type:INTEGER

Example:

1
dual | select(WEEK_OF_YEAR(T('2017-01-22 15:00:00')));
week_of_year
3