These apply to MonetDB SQL temporal types: date, time, time with time zone, timestamp and timestamp with time zone.
Some functions apply to MonetDB SQL type: second interval, day interval and month interval.
| Operator | Description | Example | Result |
|---|---|---|---|
| + | add seconds to a date or time or timestamp | date '2020-09-28' + (interval '1' second * (7 * 24 * 60 * 60)) | date '2020-10-05' |
| - | subtract seconds from a date or time | date '2020-09-28' - (interval '1' second * (7 * 24 * 60 * 60)) | date '2020-09-21' |
| - | subtract months from a date or timestamp | date '2020-09-28' - month_interval '7' | date '2020-02-28' |
| - | subtract two dates | date '2020-09-28' - date '2020-02-28' | interval '7 months' |
| - | subtract two times | time '14:35:45' - time '02:12:24' | interval '12 hours 23 minutes 31 seconds' |
NOTE: Some examples and results in below table are UNDER CONSTRUCTION
| Function | Return type | Description | Example | Result |
|---|---|---|---|---|
| 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 | current_time() | timetz '13:16:57.734639+01:00' |
| sys.date_to_str(date, format_str) | clob | convert date value to a string using format_str | 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 |
| 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(1000) | timestamptz '1970-01-01 02:00:01+02:00' |
| sys.epoch(bigint milliseconds) | timestamptz | add number of milliseconds to timestamp '1970-01-01 00:00:00.0' | sys.epoch(1234567890) | timestamptz '2009-02-14 01:31:30+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') | 82 |
| "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') | 15 |
| local_timezone() | sec_interval | get local time zone as represented in seconds | local_timezone() | 3600 |
| 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 |
| 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 '', interval month '') | |
| 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(date '', interval second '') | |
| sql_sub(date, date) | int | subtract a date from a date returning nr of seconds between the two dates | sql_sub(date '', date '') | |
| sql_sub(dt_or_ts, month_interval) | same as arg 1 | subtract an integer month interval value from a date or timestamp or timestamptz | sql_sub(date '', interval month '') | |
| 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 | sql_sub(date '', interval second '') | |
| sql_sub(time, time) | sec_interval | subtract a time from a time returning nr of seconds interval between the two times | sql_sub(time '', time '') | |
| sql_sub(timestamp, timestamp) | sec_interval | subtract a timestamp from a timestamp returning nr of seconds interval between the two timestamps | sql_sub(timestamp '', timestamp '') | |
| sql_sub(timestamptz, timestamptz) | sec_interval | subtract a timestamptz from a timestamptz returning nr of seconds interval between the two timestamptzs | sql_sub(timestamptz '', timestamptz '') | |
| sql_sub(timetz, timetz) | sec_interval | subtract a timetz from a timetz returning nr of seconds interval between the two timetzs | sql_sub(timetz '', timetz '') | |
| sys.str_to_date(s, format_str) | date | parse string s using format_str and construct a date value | 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 value | sys.str_to_time('09:30:49','%H:%M:%S') | timetz '11:30:49+02:00' |
| sys.str_to_timestamp(s, format_str) | timestamptz | parse string s using format_str and construct a timestamp value | 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(time, format_str) | clob | convert time value to a string using format_str | sys.time_to_str(time '', '') | |
| sys.timestamp_to_str(timestamp, format_str) | clob | convert timestamp value to a string using format_str | sys.timestamp_to_str(timestamp '', '') | |
| 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 |
| Pseudo column | Description | Example | Result |
|---|---|---|---|
| CURRENT_DATE | get current date | select current_date | date '2020-02-02' |
| CURRENT_TIME | get current time (with time zone) | select current_time | timetz '15:35:02.073522+01:00' |
| CURRENT_TIMESTAMP | get current timestamp (with time zone) | select current_timestamp | timestamptz '2020-02-02 15:35:02.002345+01:00' |
| CURRENT_TIMEZONE | get current time zone as seconds interval | select current_timezone | 3600.000 |
| NOW | same as: CURRENT_TIMESTAMP | select now | timestamptz '2020-02-02 15:35:02.002345+01:00' |
| LOCALTIME | get current local time without time zone | select localtime | time '15:35:02.073522' |
| LOCALTIMESTAMP | get current local timestamp without time zone | select localtimestamp | timestamp '2020-02-02 15:35:02.073522' |