Date & Time Functions

This page provides a comprehensive overview of Date & Time functions in Databend, organized by functionality for easy reference.

Current Date & Time Functions

Function	Description	Example
NOW	Returns the current date and time	`NOW()` → `2024-06-04 17:42:31.123456`
CURRENT_TIMESTAMP	Returns the current date and time	`CURRENT_TIMESTAMP()` → `2024-06-04 17:42:31.123456`
TODAY	Returns the current date	`TODAY()` → `2024-06-04`
TOMORROW	Returns tomorrow's date	`TOMORROW()` → `2024-06-05`
YESTERDAY	Returns yesterday's date	`YESTERDAY()` → `2024-06-03`

Function	Description	Example
YEAR	Extracts the year from a date	`YEAR('2024-06-04')` → `2024`
MONTH	Extracts the month from a date	`MONTH('2024-06-04')` → `6`
DAY	Extracts the day from a date	`DAY('2024-06-04')` → `4`
QUARTER	Extracts the quarter from a date	`QUARTER('2024-06-04')` → `2`
WEEK / WEEKOFYEAR	Extracts the week number from a date	`WEEK('2024-06-04')` → `23`
EXTRACT	Extracts a part from a date	`EXTRACT(MONTH FROM '2024-06-04')` → `6`
DATE_PART	Extracts a part from a date	`DATE_PART('month', '2024-06-04')` → `6`
YEARWEEK	Returns year and week number	`YEARWEEK('2024-06-04')` → `202423`
MILLENNIUM	Returns the millennium from a date	`MILLENNIUM('2024-06-04')` → `3`

Function	Description	Example
DATE	Converts a value to DATE type	`DATE('2024-06-04')` → `2024-06-04`
TO_DATE	Converts a string to DATE type	`TO_DATE('2024-06-04')` → `2024-06-04`
TO_DATETIME	Converts a string to DATETIME type	`TO_DATETIME('2024-06-04 12:30:45')` → `2024-06-04 12:30:45`
TO_TIMESTAMP	Converts a string to TIMESTAMP type	`TO_TIMESTAMP('2024-06-04 12:30:45')` → `2024-06-04 12:30:45`
TO_UNIX_TIMESTAMP	Converts a date to Unix timestamp	`TO_UNIX_TIMESTAMP('2024-06-04')` → `1717516800`
TO_YYYYMM	Formats date as YYYYMM	`TO_YYYYMM('2024-06-04')` → `202406`
TO_YYYYMMDD	Formats date as YYYYMMDD	`TO_YYYYMMDD('2024-06-04')` → `20240604`
TO_YYYYMMDDHH	Formats date as YYYYMMDDHH	`TO_YYYYMMDDHH('2024-06-04 12:30:45')` → `2024060412`
TO_YYYYMMDDHHMMSS	Formats date as YYYYMMDDHHMMSS	`TO_YYYYMMDDHHMMSS('2024-06-04 12:30:45')` → `20240604123045`
DATE_FORMAT	Formats a date according to a format string	`DATE_FORMAT('2024-06-04', '%Y-%m-%d')` → `'2024-06-04'`

Function	Description	Example
DATE_ADD	Adds a time interval to a date	`DATE_ADD(DAY, 7, '2024-06-04')` → `2024-06-11`
DATE_SUB	Subtracts a time interval from a date	`DATE_SUB(MONTH, 1, '2024-06-04')` → `2024-05-04`
ADD INTERVAL	Adds an interval to a date	`'2024-06-04' + INTERVAL 1 DAY` → `2024-06-05`
SUBTRACT INTERVAL	Subtracts an interval from a date	`'2024-06-04' - INTERVAL 1 MONTH` → `2024-05-04`
DATE_DIFF	Returns the difference between two dates	`DATE_DIFF(DAY, '2024-06-01', '2024-06-04')` → `3`
TIMESTAMP_DIFF	Returns the difference between two timestamps	`TIMESTAMP_DIFF(HOUR, '2024-06-04 10:00:00', '2024-06-04 15:00:00')` → `5`
MONTHS_BETWEEN	Returns the number of months between two dates	`MONTHS_BETWEEN('2024-06-04', '2024-01-04')` → `5`
DATE_BETWEEN	Checks if a date is between two other dates	`DATE_BETWEEN('2024-06-04', '2024-06-01', '2024-06-10')` → `true`

Function	Description	Example
DATE_TRUNC	Truncates a timestamp to a specified precision	`DATE_TRUNC('month', '2024-06-04')` → `2024-06-01`
TO_START_OF_DAY	Returns the start of the day	`TO_START_OF_DAY('2024-06-04 12:30:45')` → `2024-06-04 00:00:00`
TO_START_OF_HOUR	Returns the start of the hour	`TO_START_OF_HOUR('2024-06-04 12:30:45')` → `2024-06-04 12:00:00`
TO_START_OF_MINUTE	Returns the start of the minute	`TO_START_OF_MINUTE('2024-06-04 12:30:45')` → `2024-06-04 12:30:00`
TO_START_OF_MONTH	Returns the start of the month	`TO_START_OF_MONTH('2024-06-04')` → `2024-06-01`
TO_START_OF_QUARTER	Returns the start of the quarter	`TO_START_OF_QUARTER('2024-06-04')` → `2024-04-01`
TO_START_OF_YEAR	Returns the start of the year	`TO_START_OF_YEAR('2024-06-04')` → `2024-01-01`
TO_START_OF_WEEK	Returns the start of the week	`TO_START_OF_WEEK('2024-06-04')` → `2024-06-03`

Function	Description	Example
LAST_DAY	Returns the last day of the month	`LAST_DAY('2024-06-04')` → `2024-06-30`
NEXT_DAY	Returns the date of the next specified day of week	`NEXT_DAY('2024-06-04', 'SUNDAY')` → `2024-06-09`
PREVIOUS_DAY	Returns the date of the previous specified day of week	`PREVIOUS_DAY('2024-06-04', 'MONDAY')` → `2024-06-03`

Function	Description	Example
TIMEZONE	Returns the current timezone	`TIMEZONE()` → `'UTC'`
TIME_SLOT	Returns time slots	`TIME_SLOT('2024-06-04 12:30:45', 15, 'MINUTE')` → `2024-06-04 12:30:00`