Operation | Description | Example |
---|---|---|
interval * double precision → interval | Multiplies an interval by a double. | real '6.1' * interval '1' second → 00:00:06.1 interval '1' second * real '6.1' → 00:00:06.1 |
interval / double precision → interval | Divides an interval by a double. Error is thrown for division by zero. | interval '12 days' / 4.2 → 2 days 20:34:17.143 interval '14000' / int '14' → 00:16:40 |
interval + interval → interval | Adds an interval to an interval. | interval '20' hour + interval '10' hour → 30:00:00 |
interval - interval → interval | Subtracts an interval from an interval. | interval '20' hour - interval '10' hour → 10:00:00 |
time + interval → time | Adds an interval to a time. | time '18:20:49' + interval '1 hour' → 19:20:49 |
time - interval → time | Subtracts an interval from a time. | time '18:20:49' - interval '2 hours' → 16:20:49 |
time - time → interval | Subtracts a time from a time. | time '18:20:49' - time '16:07:16' → 02:13:33 |
Operation | Description | Example |
---|---|---|
timestamp + interval → timestamp | Adds an interval to a timestamp. | '2022-03-13 01:00:00'::timestamp + interval '24' hour → 2022-03-14 01:00:00 |
timestamp - interval → timestamp | Subtracts an interval from a timestamp. | '2022-03-14 01:00:00'::timestamp - interval '24' hour → 2022-03-13 01:00:00 |
timestamp - timestamp → interval | Subtracts a timestamp from a timestamp. | '2022-03-13 03:00:00'::timestamp - '2022-03-13 01:00:00' → 02:00:00 |
date + int → date | Adds a number of days to a date. | date '2022-06-23' + 4 → 2022-06-27 4 + Date '2022-06-23' → 2022-06-27 |
date - int → date | Subtracts a number of days from a date. | date '2022-06-23' - 4 → 2022-06-19 |
date - date → int | Subtracts a date from a date. | date '2020-03-01' - '2020-02-01' → 29 |
date + interval → timestamp | Adds an interval to a date. | date '2022-04-08' + interval '10 hour' → 2022-04-08 10:00:00 |
date - interval → timestamp | Subtracts an interval from a date. | date '2022-04-08' - interval '10 hour' → 2022-04-07 14:00:00 |
date + time → timestamp | Adds a time to a date. | date '2022-06-23' + time '19:24:00' → 2022-06-23 19:24:00 time '19:24:00' + date '2022-06-23' → 2022-06-23 19:24:00 |
Operation | Description | Example |
---|---|---|
timestamp AT TIME ZONE time_zone → timestamptz timestamptz AT TIME ZONE time_zone → timestamp | Converts times from timestamp to timestamptz (i.e., timestamp with time zone) or timestamptz to timestamp. Invalid local time during daylight saving forward is not supported. Ambiguous local time during daylight saving backward is interpreted as after the transition. | '2021-12-31 16:00:00'::timestamp AT TIME ZONE 'us/pacific' → 2022-01-01 00:00:00+00:00 '2022-01-01 00:00:00Z'::timestamptz AT TIME ZONE 'us/pacific' → 2021-12-31 16:00:00 |
timestamptz + interval → timestamptz | Adds a fixed interval to a timestamp with time zone. See note below. | '2022-03-13 01:00:00Z'::timestamp with time zone + interval '24' hour → 2022-03-14 01:00:00+00:00 |
timestamptz - interval → timestamptz | Subtracts a fixed interval from a timestamp with time zone. See note below. | '2022-03-14 01:00:00Z'::timestamp with time zone - interval '24' hour → 2022-03-13 01:00:00+00:00 |
timestamptz - timestamptz → interval | Subtracts a timestamp with time zone from a timestamp with time zone and converts 24-hour intervals into days. | '2023-07-30 13:22:00-05:00'::timestamptz - '2023-07-29 13:22:00-04:00'::timestamptz → 1 day 01:00:00 |
current_timestamp
current_timestamp
is an alias of now
. For streaming queries, it can only be used with WHERE, HAVING, and ON clauses. For more information, see Temporal filters. This constraint does not apply to batch queries.
date_part
precision_string
can be year, month, day, hour, minute, second, doy, dow, millisecond, microsecond, epoch, millennium, century, decade, isoyear, quarter, week, isodow, or julian.
date/time_value
can be a date, timestamp, time, timestamptz, or interval.
If date/time_value
is timestamptz, it is recommended that time_zone
is also specified, otherwise time_zone
will default to the session time zone.
As date_part()
returns values of type double precision, this can result in a loss of precision; therefore, using extract()
is recommended.
date_trunc
date/time_value
to a specified precision_string
.
precision_string
can be microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, or millennium.
date/time_value
can be timestamp, timestamptz (i.e., timestamp with time zone), or interval.
If date/time_value
is timestamptz, it is recommended that time_zone
is also specified, otherwise time_zone
will default to the session time zone.
precision_string
value ‘week’ is not supported for interval.
extract
extract ( field from source )
field
can be year, month, day, hour, minute, second, doy, dow, millisecond, microsecond, epoch, millennium, century, decade, isoyear, quarter, week, isodow, or julian.
source
can be the date, timestamp, timestamptz, time, or interval.
If time_zone
is specified, source
should be of type timestamptz.
extract( epoch )
make_date
make_time
make_timestamp
now
now()
can only be used with WHERE, HAVING, and ON clauses. For more information, see Temporal filters. This constraint does not apply to batch queries.
proctime
to_char
to_date
to_timestamp
to_timestamp ( seconds_double_precision )
to_timestamp ( string, timestamp_format )
to_char
, to_timestamp
, and to_date
, the format needs to be specified by using the supported template patterns. Any text that is not a template pattern is simply copied verbatim.
Please see the table below for the template patterns supported in RisingWave.
Pattern | Description |
---|---|
HH24 or hh24 | hour of day (00–23) |
HH12 or hh12 | hour of day (01–12) |
HH or hh | hour of day (01–12) |
AM, PM | meridiem indicator (without periods) |
am, pm | meridiem indicator (without periods) |
MI or mi | minute (00–59) |
SS or ss | second (00–59) |
YYYY or yyyy | year (4 or more digits) |
YY or yy | last 2 digits of year |
IYYY or iyyy | ISO 8601 week-numbering year (4 or more digits) |
IY or iy | last 2 digits of ISO 8601 week-numbering year |
MM or mm | month number (01–12) |
Month | full capitalized month name (blank-padded to 9 chars) |
Mon | abbreviated capitalized month name (3 chars in English) |
DD or dd | day of month (01–31) |
US or us | microsecond (000000–999999) |
MS or ms | millisecond (000–999) |
TZH:TZM or tzh:tzm | time-zone hours and minutes |
TZHTZM or tzhtzm | time-zone hours and minutes |
TZH or tzh | Time-zone hours. This pattern works only in to_timestamp. |
pg_sleep
pg_sleep()
function makes the current session’s process sleep until the given number of seconds have elapsed. Fractional-second delays can be specified.
pg_sleep_for
pg_sleep_for
is a convenience function to allow the sleep time to be specified as an interval.