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 DateFunction 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 TimestampsFunction 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.0. 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.0. 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.0. 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.0. 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 TimesFunction 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 ValueFunction 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 DateFunction 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 FunctionsFunction 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