Date & Time Functions

📄️ DATE_PART

Retrieves the designated portion of a date, time, or timestamp.

📄️ DATE_ADD

Add the time interval or date interval to the provided date or date with time (timestamp/datetime).

Databend does not provide a Datediff function yet, but it supports direct arithmetic operations on dates and times. For example, you can use the expression TO_DATE(NOW())-2 to obtain the date from two days ago.

📄️ DATE_FORMAT

Converts a date value to a specific string format. To customize the format of date and time in Databend, you can utilize specifiers. These specifiers allow you to define the desired format for date and time values. For a comprehensive list of supported specifiers, see Formatting Date and Time.

📄️ DATE_SUB

Subtract the time interval or date interval from the provided date or date with time (timestamp/datetime).

📄️ DATE_TRUNC

Truncates a date, time, or timestamp value to a specified precision. For example, if you truncate 2022-07-07 to MONTH, the result will be 2022-07-01; if you truncate 2022-07-07 0101.123456 to SECOND, the result will be 2022-07-07 0101.000000.

📄️ EXTRACT

Retrieves the designated portion of a date, time, or timestamp.

📄️ NOW

Returns the current date and time.

📄️ SUBTRACT TIME INTERVAL

Subtract time interval function

📄️ TIME_SLOT

Rounds the time to the half hour.

📄️ TIMEZONE

Returns the timezone for the current connection.

📄️ TO_MONTH

Convert a date or date with time (timestamp/datetime) to a UInt8 number containing the month number (1-12).

📄️ TO_DATE

TO_DATE converts an expression to a date format.

📄️ TO_DATETIME

TODATETIME is an alias of TOTIMESTAMP. See TO_TIMESTAMP.

📄️ TODAY

Returns current date.

📄️ TO_DAY_OF_MONTH

Converts a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the month (1-31).

📄️ TO_DAY_OF_WEEK

Converts a date or date with time (timestamp/datetime) to a UInt8 number containing the number of the day of the week (Monday is 1, and Sunday is 7).

📄️ TO_DAY_OF_YEAR

Converts a date or date with time (timestamp/datetime) to a UInt16 number containing the number of the day of the year (1-366).

📄️ TO_HOUR

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the hour in 24-hour time (0-23).

📄️ TO_MINUTE

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the minute of the hour (0-59).

📄️ TO_MONDAY

Round down a date or date with time (timestamp/datetime) to the nearest Monday.

📄️ TOMORROW

Returns tomorrow date, same as today() + 1.

📄️ TO_QUARTER

Retrieves the quarter (1, 2, 3, or 4) from a given date or timestamp.

📄️ TO_SECOND

Converts a date with time (timestamp/datetime) to a UInt8 number containing the number of the second in the minute (0-59).

📄️ TO_START_OF_DAY

Rounds down a date with time (timestamp/datetime) to the start of the day.

📄️ TO_START_OF_FIFTEEN_MINUTES

Rounds down the date with time (timestamp/datetime) to the start of the fifteen-minute interval.

📄️ TO_START_OF_FIVE_MINUTES

Rounds down a date with time (timestamp/datetime) to the start of the five-minute interval.

📄️ TO_START_OF_HOUR

Rounds down a date with time (timestamp/datetime) to the start of the hour.

📄️ TO_START_OF_ISO_YEAR

Returns the first day of the ISO year for a date or a date with time (timestamp/datetime).

📄️ TO_START_OF_MINUTE

Rounds down a date with time (timestamp/datetime) to the start of the minute.

📄️ TO_START_OF_MONTH

Rounds down a date or date with time (timestamp/datetime) to the first day of the month.

📄️ TO_START_OF_QUARTER

Rounds down a date or date with time (timestamp/datetime) to the first day of the quarter.

📄️ TO_START_OF_SECOND

Rounds down a date with time (timestamp/datetime) to the start of the second.

📄️ TO_START_OF_TEN_MINUTES

Rounds down a date with time (timestamp/datetime) to the start of the ten-minute interval.

📄️ TO_START_OF_WEEK

Returns the first day of the week for a date or a date with time (timestamp/datetime).

📄️ TO_START_OF_YEAR

Returns the first day of the year for a date or a date with time (timestamp/datetime).

📄️ TO_TIMESTAMP

TO_TIMESTAMP converts an expression to a date with time (timestamp/datetime).

📄️ TO_UNIX_TIMESTAMP

Converts a timestamp in a date/time format to a Unix timestamp format. A Unix timestamp represents the number of seconds that have elapsed since January 1, 1970, at 0000 UTC.

📄️ TO_WEEK_OF_YEAR

Calculates the week number within a year for a given date.

📄️ TO_YEAR

Converts a date or date with time (timestamp/datetime) to a UInt16 number containing the year number (AD).

📄️ TO_YYYYMM

Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number.

📄️ TO_YYYYMMDD

Converts a date or date with time (timestamp/datetime) to a UInt32 number containing the year and month number (YYYY 10000 + MM 100 + DD).

📄️ TO_YYYYMMDDHH

Formats a given date or timestamp into a string representation in the format "YYYYMMDDHH" (Year, Month, Day, Hour).

📄️ TO_YYYYMMDDHHMMSS

Convert a date or date with time (timestamp/datetime) to a UInt64 number containing the year and month number (YYYY 10000000000 + MM 100000000 + DD 1000000 + hh 10000 + mm * 100 + ss).

📄️ YESTERDAY

Returns yesterday date, same as today() - 1.

📄️ ADD TIME INTERVAL