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 |
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 |