| century(date) | int | extract century from a date or timestamp or timestamptz | century(date '2020-03-22') | 21 |
| curdate() | date | get current date | curdate() | date '2020-03-22' |
| current_date() | date | get current date | current_date() | date '2020-03-22' |
| current_time() | timetz | get current time with time zone | current_time() | timetz '13:16:57.734639+01:00' |
| current_timestamp() | timestamptz | get current timestamp (date and time and fractions) with time zone | current_timestamp() | timestamptz '2020-03-22 13:16:57.734639+01:00' |
| curtime() | timetz | get current time with time zone | curtime() | timetz '13:16:57.734639+01:00' |
| sys.date_to_str(date, format_str) | clob | convert date value to a string using format_str. See format specifiers | sys.date_to_str(date '2020-03-22', '%D %d.%m.%Y'); | 03/22/20 22.03.2020 |
| sys.date_trunc(field_str, timestamp) | timestamp | Truncate a timestamp to granularity specified bij field_str. Allowed values for field_str are 'millennium', 'century', 'decade', 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'milliseconds', 'microseconds'. | sys.date_trunc('month', timestamp '2020-03-22 13:16:57.734639'); | timestamp '2020-03-01 00:00:00.000000' |
| "day"(dt_or_ts) | int | extract month day nr (1..31) of a date or timestamp or timestamptz. same as: dayofmonth(date) | "day"(date '2020-03-22') | 22 |
| "day"(sec_interval) | bigint | compute nr of days and truncate it to an integer | "day"(interval '3.23' second * (24 * 60 * 60)) | 3 |
| {fn DAYNAME(dt_or_ts)} | varchar | extract day name in current locale of a date or timestamp or timestamptz. This function requires the ODBC escape sequence notation. | select {fn DAYNAME({d '2020-03-22'})} | Sunday |
| dayofmonth(dt_or_ts) | int | extract month day nr (1..31) of a date or timestamp or timestamptz. same as: "day"(date) | dayofmonth(date '2020-03-22') | 22 |
| dayofweek(dt_or_ts) | int | extract week day nr (1..7) of a date or timestamp or timestamptz | dayofweek(date '2020-03-22') | 7 |
| dayofyear(dt_or_ts) | int | extract year day nr (1..366) of a date or timestamp or timestamptz | dayofyear(date '2020-03-22') | 82 |
| decade(dt_or_ts) | int | extract decade nr of a date or timestamp or timestamptz | decade(date '2027-03-22') | 202 |
| sys.epoch(int seconds) | timestamptz | add number of seconds to timestamp '1970-01-01 00:00:00.0' | sys.epoch(1234567890) | timestamptz '2009-02-14 01:31:30.000000+02:00' |
| sys.epoch(decimal(18,3) seconds) | timestamptz | add number of seconds to timestamp '1970-01-01 00:00:00.0' | sys.epoch(1234567890.456) | timestamptz '2009-02-14 01:31:30.456000+02:00' |
| sys.epoch(timestamp) | int | convert timestamp (or timestamptz) to nr of seconds since '1970-01-01 00:00:00.0' | sys.epoch(timestamp '2009-02-13 23:31:30.0') | 1234567890 |
| epoch_ms(dt_or_tm_or_ts_or_interval) | decimal(18,3) | convert date or time or timestamp or interval to number of milliseconds since '1970-01-01 00:00:00.0' | epoch_ms(timestamp '2009-02-13 23:31:30.0') | 1234567890 |
| extract(field from dt_value) | int | extract a specific field from a date or time or timestamp. Supported keywords for field are: CENTURY, DECADE, YEAR, QUARTER, MONTH, WEEK, DAY, DOW, DOY, HOUR, MINUTE and SECOND. | extract(doy from date '2027-03-22') | 81 |
| greatest(x, y) | same as input | greatest (highest) value of x and y (same as sql_max(x,y)) | greatest(date '2020-03-22', date '2020-03-25') | date '2020-03-25' |
| "hour"(tm_or_ts) | int | extract hour nr (0..23) of a time or timetz or timestamp or timestamptz or sec_interval | "hour"(timetz '15:35:02.002345+01:00') | 16 |
| least(x, y) | same as input | least (lowest) value of x and y (same as sql_min(x,y)) | least(time '15:15:15', time '16:16:16') | time '15:15:15' |
| local_timezone() | sec_interval | get local time zone as represented in seconds | local_timezone() | 7200 |
| localtime() | time | get current local time without time zone | localtime() | time '13:16:57.734639' |
| localtimestamp() | timestamp | get current local timestamp (date and time and fractions) without time zone | localtimestamp() | timestamp '2020-03-22 13:16:57.734639' |
| "minute"(tm_or_ts) | int | extract minutes (0..59) of a time or timetz or timestamp or timestamptz or sec_interval | "minute"(timetz '15:35:02.002345+01:00') | 35 |
| "month"(dt_or_ts) | int | extract month nr (1..12) of a date or timestamp or timestamptz or month_interval | "month"(date '2020-07-22') | 7 |
| {fn MONTHNAME(dt_or_ts)} | varchar | extract month name in current locale of a date or timestamp or timestamptz. This function requires the ODBC escape sequence notation. | select {fn MONTHNAME({d '2020-03-22'})} | March |
| now() | timestamptz | get current timestamp (date and time and fractions) with time zone | now() | timestamptz '2020-03-22 13:16:57.734639+01:00' |
| quarter(dt_or_ts) | int | extract quarter nr (1..4) of a date or timestamp or timestamptz | quarter(date '2020-07-22') | 3 |
| "second"(sec_interval) | int | extract seconds (0 .. 59) of a sec_interval | "second"(interval '24' second) | 24 |
| "second"(tm_or_ts) | decimal(9,6) | extract seconds (0.000 .. 59.999999) of a time or timetz or timestamp or timestamptz | "second"(timetz '15:35:02.002345+01:00') | 2.002345 |
| sql_add(dt_or_ts, month_interval) | same as arg 1 | add an integer month interval value to a date or timestamp or timestamptz | sql_add(date '2021-12-31', interval '2' month) | date '2022-02-28' |
| sql_add(dt_or_tm, sec_interval) | same as arg 1 | add a decimal seconds interval value to a date or time or timetz or timestamp or timestamptz | sql_add(time '15:35:50', interval '50' second) | time '15:36:40' |
| sql_max(x, y) | same as input | greatest (highest) value of x and y (same as greatest(x,y)) | sql_max(date '2020-03-22', date '2020-03-25') | date '2020-03-25' |
| sql_min(x, y) | same as input | least (lowest) value of x and y(same as least(x,y)) | sql_min(time '15:15:15', time '16:16:16') | time '15:15:15' |
| sql_sub(date, date) | int | subtract a date from a date returning nr of seconds between the two dates | sql_sub(date '2021-12-31', date '2021-12-11') | interval '20' day |
| sql_sub(dt_or_ts, month_interval) | same as arg 1 | subtract an integer month interval value from a date or timestamp or timestamptz | select sql_sub(date '2021-12-31', interval '-2' month) | date '2022-02-28' |
| sql_sub(dt_or_tm, sec_interval) | same as arg 1 | subtract a decimal seconds interval value from a date or time or timetz or timestamp or timestamptz | select sql_sub(time '15:35:50', interval '3:25' minute to second) | time '15:32:25' |
| sql_sub(time, time) | sec_interval | subtract a time from a time returning nr of seconds interval between the two times | sql_sub(time '9:00:00', time '17:00:00') | interval '-28800' second |
| sql_sub(timestamp, timestamp) | sec_interval | subtract a timestamp from a timestamp returning nr of seconds interval between the two timestamps | sql_sub(timestamp '2021-12-31 18:00:00', timestamp '2021-12-31 17:00:00') | interval '3600' second |
| sql_sub(timestamptz, timestamptz) | sec_interval | subtract a timestamptz from a timestamptz returning nr of seconds interval between the two timestamps with time zones | sql_sub(timestamptz '2021-12-31 18:00:00+02:00', timestamptz '2021-12-31 18:00:00-02:00') | interval '-14400' second |
| sql_sub(timetz, timetz) | sec_interval | subtract a timetz from a timetz returning nr of seconds interval between the two timetzs | sql_sub(timetz '18:00:00+02:00', timetz '18:00:00-02:00') | interval '-14400' second |
| sys.str_to_date(s, format_str) | date | parse string s using format_str and construct a date value. See format specifiers | sys.str_to_date('4/30/2021', '%m/%d/%Y') | date '2021-04-30' |
| sys.str_to_time(s, format_str) | timetz | parse string s using format_str and construct a time(0) with time zone value. See format specifiers | sys.str_to_time('09:30:49','%H:%M:%S') | time(0) with time zone '11:30:49+02:00' |
| sys.str_to_timestamp(s, format_str) | timestamptz | parse string s using format_str and construct a timestamp with time zone value. See format specifiers | sys.str_to_timestamp('4/30/2021 09:30:49','%m/%d/%Y %H:%M:%S') | timestamptz '2021-04-30 11:30:49.000000+02:00' |
| sys.time_to_str(timetz, format_str) | clob | convert time with time zone value to a string using format_str. See format specifiers | sys.time_to_str(timetz '19:30:49', '%l hour %M:%S %p') | ' 7 hour 30:49 PM' |
| sys.timestamp_to_str(timestamptz, format_str) | clob | convert timestamp with time zone value to a string using format_str. See format specifiers | sys.timestamp_to_str(timestamptz '2021-12-31 18:00:00', '%m/%d/%Y %l:%M:%S %p') | '12/31/2021 6:00:00 PM' |
| timestampadd(dt_ts_tm, interval) | timestamp or timestamptz | add interval (month or day or second) to a timestamp (with time zone) or date or time | select timestampadd(timestamp '2021-12-31 18:00:00', interval '2' month) | timestamp '2022-02-28 18:00:00' |
| timestampdiff(ts_tstz, ts_tstz) | interval second | returns difference of two timestamps or two timestamps with time zone | select timestampdiff(timestamp '2021-12-31 18:40:40', timestamp '2021-12-30 16:30:20') | interval '94220' second |
| timestampdiff_day(ts_dt_tz, ts_dt_tz) | integer | returns difference of two timestamps or timestamps with time zone or dates | select timestampdiff_day(timestamp '2021-12-31 18:40:40', timestamp '2021-12-20 16:30:20') | 11 |
| timestampdiff_hour(ts_dt_tz, ts_dt_tz) | bigint | returns difference of two timestamps or timestamps with time zone or dates | select timestampdiff_hour(timestamp '2021-12-31 18:40:40', timestamp '2021-12-20 16:30:20') | 266 |
| timestampdiff_min(ts_dt_tz, ts_dt_tz) | bigint | returns difference of two timestamps or timestamps with time zone or dates | select timestampdiff_min(timestamp '2021-12-31 18:40:40', timestamp '2021-12-31 16:30:20') | 130 |
| timestampdiff_month(ts_tm_tz, ts_tm_tz) | integer | returns difference of two timestamps or two timestamps with time zone | select timestampdiff_month(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20') | 10 |
| timestampdiff_quarter(ts_tm_tz, ts_tm_tz) | integer | returns difference of two timestamps or two timestamps with time zone | select timestampdiff_quarter(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20') | 3 |
| timestampdiff_sec(ts_dt_tz, ts_dt_tz) | bigint | returns difference of two timestamps or timestamps with time zone or dates | select timestampdiff_sec(timestamp '2021-12-31 18:40:40', timestamp '2021-12-31 16:30:20') | 7820 |
| timestampdiff_week(ts_tm_tz, ts_tm_tz) | integer | returns difference of two timestamps or two timestamps with time zone | select timestampdiff_week(timestamp '2021-12-31 18:40:40', timestamp '2021-02-20 16:30:20') | 44 |
| timestampdiff_year(ts_tm_tz, ts_tm_tz) | integer | returns difference of two timestamps or two timestamps with time zone | select timestampdiff_year(timestamp '2021-12-31 18:40:40', timestamp '2024-02-20 16:30:20') | -3 |
| usweekofyear(date) | int | extract us week number (1 .. 53) of a date. The US system has weeks from Sunday through Saturday, and partial weeks at the beginning and the end of the year. | usweekofyear(date '2020-03-22') | 13 |
| week(dt_or_ts) | int | extract ISO 8601 week number (1 .. 53) of a date or timestamp or timestamptz. same as: weekofyear(date) | week(date '2020-03-22') | 12 |
| weekofyear(dt_or_ts) | int | extract ISO 8601 week number (1 .. 53) of a date or timestamp or timestamptz. same as: week(date) | weekofyear(date '2020-03-22') | 12 |
| "year"(dt_or_ts) | int | extract year number of a date or timestamp or timestamptz or month_interval | "year"(date '2020-03-22') | 2020 |