Built-in Functions → Date Functions

The date functions adjust the values of type date or timestamp. Here is a list of supported date functions:

Functions Adding or Subtracting Time From a Date

Function	Signature	Description
addMilliseconds()	`addMilliseconds(date exp, int milliseconds)`	Returns the sum of `date exp` and `int milliseconds` as a `date` value
addSeconds()	`addSeconds(date exp, int seconds)`	Returns the sum of `date exp` and `int seconds` as a `date` value
addMinutes()	`addMinutes(date exp, long minutes)`	Returns a modified date after the specified minutes have been added
addHours()	`addHours(date exp, int hours)`	Returns the sum of `date exp` and `int hours` as a `date` value
addDays()	`addDays(date exp, int days)`	Returns a new date after adding an integer number of days to a given date
addMonths()	`addMonths(date exp, int months)`	Returns the sum of `date exp` and `int months` as a `date` value
addQuarters()	`addQuarters(date_timestamp exp, int quarters)`	Returns a modified `date` or `timestamp` after the specified quarters have been added
addYears()	`addYears(date exp, int years)`	Returns the sum of `date exp` and `int years` as a `date` value

Function	Signature	Description
date()	`date(string exp)`	Returns a `date` value converted from a `string`
formatDate()	`formatDate(timestamp expr t, string format)`	Returns the date as a string formatted according to specification

Functions for Returning Time Field Values from Strings and Timestamps

Function	Signature	Description
minute()	`minute(field date)`	Returns the minute field of a `date` or `timestamp` value
hour()	`hour(field date)`	Returns the hour field of a `date` or `timestamp` value
day()	`day(date exp)`	Returns the day of the month
month()	`month(date exp)`	Returns the month field of a `date` or `timestamp` value
year()	`year(date exp)`	Returns the year field of a `date` or `timestamp` value
yearMonth	`yearMonth()`	Available starting with 2024.1.x. Returns the current year and month as an integer. In the returned value, the first four digits represent the year while the last two digits represent the month.
yearMonth(date_timestamp exp)	`yearMonth(date_timestamp exp)`	Available starting with 2024.1.x. Returns the year and month of a given date or timestamp column or expression as an integer. In the returned value, the first four digits represent the year while the last two digits represent the month.
yearQuarter	`yearQuarter()`	Available starting with 2024.1.x. Returns the current year and quarter as an integer. In the returned value, the first four digits represent the year while the last two digits represent the quarter.
yearQuarter(date_timestamp exp)	`yearQuarter(date_timestamp exp)`	Available starting with 2024.1.x. Returns the year and quarter of a given date or timestamp column or expression as an integer. In the returned value, the first four digits represent the year while the last two digits represent the quarter.

Functions for Returning the Interval Between Two Times

Function	Signature	Description
daysBetween	`daysBetween(date exp, date exp)`	Returns the number of days between two dates, day1 and day2
timeBetween	`timeBetween(timestamp endTime, timestamp startTime)`	Returns the interval between two given times, endTime and startTime

Functions for Returning Day or Month Number from a Timestamp or Date Value

Function	Signature	Description
weekday()	`weekday(date exp)`	Returns the week number from the year in the argument
weeknum()	`weeknum(date exp)`	Returns the day number of the week in the argument

Functions for Returning the Start or End Date

Function	Signature	Description
quarterStartDate()	`quarterStartDate()`	Returns the date of the first day of the current quarter
quarterEndDate()	`quarterEndDate()`	Returns the date of the last day of the current quarter
monthStartDate()	`monthStartDate()`	Returns the date of the first day of the current month
monthEndDate()	`monthEndDate()`	Returns the date of the last day of the current month
weekStartDate()	`weekStartDate()`	Returns the date of the first day of the current week
weekEndDate()	`weekEndDate()`	Returns the date of the last day of the current week

Miscellaneous Functions

Function	Signature	Description
removeTime()	`removeTime(Timestamp expression)`	Returns a `timestamp` value with the time fields removed
dateTrunc()	`dateTrunc(date exp, string part)`	Returns `date exp` truncated to remove specified smaller units
formatDuration()	`formatDuration(int duration)`	Converts a time value into a duration

Content

Functions Adding or Subtracting Time From a Date

Functions for Returning Time Field Values from Strings and Timestamps

Functions for Returning the Interval Between Two Times

Functions for Returning Day or Month Number from a Timestamp or Date Value

Functions for Returning the Start or End Date

Miscellaneous Functions