Skip to main content

Date/Time functions and operators

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 and month interval .

Date/Time operators

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'

 

Date/Time functions

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
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". select 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
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 '', '')  
week(dt_or_ts) int extract week nr (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 week nr (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 nr of a date or timestamp or timestamptz or month_interval "year"(date '2020-03-22') 2020

 

Date/Time pseudo columns

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'

See also: Date/Time Pseudo Columns