Date Time Functions & 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, day interval and month interval.

Date/Time operators

OperatorDescriptionExampleResult
+add seconds to a date or time or timestampdate '2020-09-28' + (interval '1' second * (7 * 24 * 60 * 60))date '2020-10-05'
-subtract seconds from a date or timedate '2020-09-28' - (interval '1' second * (7 * 24 * 60 * 60))date '2020-09-21'
-subtract months from a date or timestampdate '2020-09-28' - month_interval '7'date '2020-02-28'
-subtract two datesdate '2020-09-28' - date '2020-02-28'interval '7 months'
-subtract two timestime '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

FunctionReturn typeDescriptionExampleResult
century(date)intextract century from a date or timestamp or timestamptzcentury(date '2020-03-22')21
curdate()dateget current datecurdate()date '2020-03-22'
current_date()dateget current datecurrent_date()date '2020-03-22'
current_time()timetzget current time with time zonecurrent_time()timetz '13:16:57.734639+01:00'
current_timestamp()timestamptzget current timestamp (date and time and fractions) with time zonecurrent_timestamp()timestamptz '2020-03-22 13:16:57.734639+01:00'
curtime()timetzget current time with time zonecurrent_time()timetz '13:16:57.734639+01:00'
sys.date_to_str(date, format_str)clobconvert date value to a string using format_strsys.date_to_str(date '2020-03-22', '%D %d.%m.%Y');03/22/20 22.03.2020
sys.date_trunc(field_str, timestamp)timestampTruncate 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)intextract 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)bigintcompute nr of days and truncate it to an integer"day"(interval '3.23' second * (24 * 60 * 60))3
dayofmonth(dt_or_ts)intextract 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)intextract week day nr (1..7) of a date or timestamp or timestamptzdayofweek(date '2020-03-22')7
dayofyear(dt_or_ts)intextract year day nr (1..366) of a date or timestamp or timestamptzdayofyear(date '2020-03-22')82
decade(dt_or_ts)intextract decade nr of a date or timestamp or timestamptzdecade(date '2027-03-22')202
sys.epoch(int seconds)timestamptzadd 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)timestamptzadd 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)intconvert 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)intextract 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)intextract 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_intervalget local time zone as represented in secondslocal_timezone()3600
localtime()timeget current local time without time zonelocaltime()time '13:16:57.734639'
localtimestamp()timestampget current local timestamp (date and time and fractions) without time zonelocaltimestamp()timestamp '2020-03-22 13:16:57.734639'
"minute"(tm_or_ts)intextract 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)intextract month nr (1..12) of a date or timestamp or timestamptz or month_interval"month"(date '2020-07-22')7
now()timestamptzget current timestamp (date and time and fractions) with time zonenow()timestamptz '2020-03-22 13:16:57.734639+01:00'
quarter(dt_or_ts)intextract quarter nr (1..4) of a date or timestamp or timestamptzquarter(date '2020-07-22')3
"second"(sec_interval)intextract 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 1add an integer month interval value to a date or timestamp or timestamptzsql_add(date '', interval month '')
sql_add(dt_or_tm, sec_interval)same as arg 1add a decimal seconds interval value to a date or time or timetz or timestamp or timestamptzsql_add(date '', interval second '')
sql_sub(date, date)intsubtract a date from a date returning nr of seconds between the two datessql_sub(date '', date '')
sql_sub(dt_or_ts, month_interval)same as arg 1subtract an integer month interval value from a date or timestamp or timestamptzsql_sub(date '', interval month '')
sql_sub(dt_or_tm, sec_interval)same as arg 1subtract a decimal seconds interval value from a date or time or timetz or timestamp or timestamptzsql_sub(date '', interval second '')
sql_sub(time, time)sec_intervalsubtract a time from a time returning nr of seconds interval between the two timessql_sub(time '', time '')
sql_sub(timestamp, timestamp)sec_intervalsubtract a timestamp from a timestamp returning nr of seconds interval between the two timestampssql_sub(timestamp '', timestamp '')
sql_sub(timestamptz, timestamptz)sec_intervalsubtract a timestamptz from a timestamptz returning nr of seconds interval between the two timestamptzssql_sub(timestamptz '', timestamptz '')
sql_sub(timetz, timetz)sec_intervalsubtract a timetz from a timetz returning nr of seconds interval between the two timetzssql_sub(timetz '', timetz '')
sys.str_to_date(s, format_str)dateparse string s using format_str and construct a date valuesys.str_to_date('4/30/2021', '%m/%d/%Y')date '2021-04-30'
sys.str_to_time(s, format_str)timetzparse string s using format_str and construct a time valuesys.str_to_time('09:30:49','%H:%M:%S')timetz '11:30:49+02:00'
sys.str_to_timestamp(s, format_str)timestamptzparse string s using format_str and construct a timestamp valuesys.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)clobconvert time value to a string using format_strsys.time_to_str(time '', '')
sys.timestamp_to_str(timestamp, format_str)clobconvert timestamp value to a string using format_strsys.timestamp_to_str(timestamp '', '')
usweekofyear(date)intextract 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)intextract 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)intextract 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)intextract year number of a date or timestamp or timestamptz or month_interval"year"(date '2020-03-22')2020

Date/Time pseudo columns

Pseudo columnDescriptionExampleResult
CURRENT_DATEget current dateselect current_datedate '2020-02-02'
CURRENT_TIMEget current time (with time zone)select current_timetimetz '15:35:02.073522+01:00'
CURRENT_TIMESTAMPget current timestamp (with time zone)select current_timestamptimestamptz '2020-02-02 15:35:02.002345+01:00'
CURRENT_TIMEZONEget current time zone as seconds intervalselect current_timezone3600.000
NOWsame as: CURRENT_TIMESTAMPselect nowtimestamptz '2020-02-02 15:35:02.002345+01:00'
LOCALTIMEget current local time without time zoneselect localtimetime '15:35:02.073522'
LOCALTIMESTAMPget current local timestamp without time zoneselect localtimestamptimestamp '2020-02-02 15:35:02.073522'