Date/Time Functions

The time and date functions are a collection of functions used to handle and manipulate time and date data.They include operations such as getting the current time, formatting dates, comparing times, and calculating time intervals.Time and date functions can help you handle time-related tasks, such as calculating date differences, scheduling tasks, and calendar functions, etc.

now

Return the current UTC timestamp.

now()

View now() Example

SELECT now();
+--------------------------------+
| now()                          |
+--------------------------------+
| 2024-03-11T09:52:26.574620673Z |
+--------------------------------+

current_date

Return the current UTC date.

current_date()

View current_date Example

SELECT current_date();
+----------------+
| current_date() |
+----------------+
| 2024-03-11     |
+----------------+

current_time

Return the current UTC time.

current_time()

View current_date Example

SELECT current_time();
+--------------------+
| current_time()     |
+--------------------+
| 11:30:04.708668926 |
+--------------------+

date_bin

Calculate the time interval and return the starting point of the interval closest to the specified timestamp.By grouping rows into time-based 'bins' or 'windows' and applying aggregation or selector functions, downsample time series data to each window using date_bin.

date_bin(interval, expression, origin-timestamp)

Parameters	Description
interval
expression	The time expression to operate.Can be a constant, column, or function.
origin-timestamp	Optional.The starting point for determining the bin boundaries.If not specified, it defaults to 1970-01-01T00:00:00Z (UNIX epoch in UTC).

Please refer to duration units for supported interval time units

View date_bin Example

If you bin the data into 15-minute intervals, entering the timestamp 2024-01-01T18:18:18Z will be updated to the start time of its 15-minute bin: 2024-01-01T18:15:00Z.

SELECT date_bin(INTERVAL '15' MINUTE, TIMESTAMP '2024-01-01T18:18:18Z');
+-----------------------------------------------------------------------------+
| date_bin(IntervalMonthDayNano("900000000000"),Utf8("2024-01-01T18:18:18Z")) |
+-----------------------------------------------------------------------------+
| 2024-01-01T18:15:00                                                         |
+-----------------------------------------------------------------------------+

date_trunc

Truncate the timestamp value to the specified precision.

date_trunc(precision, expression)

Parameters	Description
precision	The precision of the truncated time.Support: year, quarter, month, week, day, hour, minute, second.
expression	The time expression to operate.Can be a constant, column, or function.

Alias: datetrunc

View date_trunc Example

SELECT date_trunc('month', time) AS month, avg(temperature) AS avg_temperature FROM air GROUP BY month;
+---------------------+-------------------+
| month               | avg_temperature   |
+---------------------+-------------------+
| 2023-02-01T00:00:00 | 65.09259672619048 |
| 2023-03-01T00:00:00 | 65.00373418686176 |
| 2023-01-01T00:00:00 | 64.96063701923077 |
+---------------------+-------------------+

date_part

Returns the specified part of the date in integer form.

date_part(part, expression)

Options	Description
part	A part of the date to be returned.Support: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond, dow, doy, epoch.
expression	The time expression to operate.Can be a constant, column, or function.

Alias: datepart

View date_part Example

Extract the month from the date.

SELECT date_part('month', '2024-05-13') AS month;

extract

Return subfields from a time value in integer form.Similar to date_part, but with different parameters.

extract(field FROM source)

Options	Description
field	A part of the date to be returned.Support: year, quarter, month, week, day, hour, minute, second, millisecond, microsecond, nanosecond, dow, doy, epoch.
source	The time expression to operate.Can be a constant, column, or function.

View extract Example

Extract the month from the date.

SELECT extract(month FROM  '2024-05-13') AS month;

to_timestamp

Converts the value to a timestamp (YYYY-MM-DDT00:00:00Z).Supports string, integer, unsigned integer, and double-precision floating-point number types as input.If [Chrono format] is not provided, the string will be parsed according to RFC3339 (e.g. 2023-07-20T05:44:00).Integers, unsigned integers, and double-precision floating-point numbers are interpreted as the number of seconds since the Unix epoch (1970-01-01T00:00:00Z).Return the corresponding timestamp.

tip

to_timestamp returns a timestamp (in nanoseconds).The range supported for integer input is between -9223372037 and 9223372036.The range supported for string input is between 1677-09-21T00:12:44.0 and 2262-04-11T23:47:16.0.Please use to_timestamp_seconds for inputs that are beyond the supported range.

to_timestamp(expression[, ..., format_n])

Options	Description
expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.
format_n	Optional Chrono format string used to parse the expression.Will attempt formats in the order they appear and return the first successful format.If no format can successfully parse the expression, an error will be returned.

View to_timestamp Example

select to_timestamp('2023-01-31T09:26:56.123456789-05:00');
+-----------------------------------------------------------+
| to_timestamp(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-----------------------------------------------------------+
| 2023-01-31T14:26:56.123456789                             |
+-----------------------------------------------------------+

to_timestamp_millis

Converts the value to a timestamp (YYYY-MM-DDT00:00:00.000Z).Supports string, integer, and unsigned integer types as input.If [Chrono format] is not provided, the string will be parsed according to RFC3339 (e.g. 2023-07-20T05:44:00).Integers and unsigned integers are interpreted as the number of milliseconds since the Unix epoch (1970-01-01T00:00:00Z).Return the corresponding timestamp.

to_timestamp_millis(expression[, ..., format_n])

Options	Description
expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.
format_n	Optional Chrono format string used to parse the expression.Will attempt formats in the order they appear and return the first successful format.If no format can successfully parse the expression, an error will be returned.

View to_timestamp_millis Example

select to_timestamp_millis('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_millis(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123                                          |
+------------------------------------------------------------------+

to_timestamp_micros

Converts the value to a timestamp (YYYY-MM-DDT00:00:00.000000Z).Supports string, integer, and unsigned integer types as input.If [Chrono format] is not provided, the string will be parsed according to RFC3339 (e.g. 2023-07-20T05:44:00).Integers and unsigned integers are interpreted as the number of microseconds since the Unix epoch (1970-01-01T00:00:00Z).Return the corresponding timestamp.

to_timestamp_micros(expression[, ..., format_n])

Options	Description
expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.
format_n	Optional Chrono format string used to parse the expression.Will attempt formats in the order they appear and return the first successful format.If no format can successfully parse the expression, an error will be returned.

View to_timestamp_micros Example

select to_timestamp_micros('2023-01-31T09:26:56.123456789-05:00');
+------------------------------------------------------------------+
| to_timestamp_micros(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+------------------------------------------------------------------+
| 2023-01-31T14:26:56.123456                                       |
+------------------------------------------------------------------+

to_timestamp_seconds

Converts the value to a timestamp (YYYY-MM-DDT00:00:00.000Z).Supports string, integer, and unsigned integer types as input.If [Chrono format] is not provided, the string will be parsed according to RFC3339 (e.g. 2023-07-20T05:44:00).Integers and unsigned integers are interpreted as the number of seconds since the Unix epoch (1970-01-01T00:00:00Z).Return the corresponding timestamp.

to_timestamp_seconds(expression[, ..., format_n])

Options	Description
expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.
format_n	Optional Chrono format string used to parse the expression.Will attempt formats in the order they appear and return the first successful format.If no format can successfully parse the expression, an error will be returned.

View to_timestamp_seconds Example

select to_timestamp_seconds('2023-01-31T09:26:56.123456789-05:00');
+-------------------------------------------------------------------+
| to_timestamp_seconds(Utf8("2023-01-31T09:26:56.123456789-05:00")) |
+-------------------------------------------------------------------+
| 2023-01-31T14:26:56                                               |
+-------------------------------------------------------------------+

from_unixtime

Converts the integer to RFC3339 timestamp format (YYYY-MM-DDT00:00:00.000000000Z).Integers and unsigned integers are interpreted as the number of microseconds since the Unix epoch (1970-01-01T00:00:00Z), returning the corresponding timestamp.

from_unixtime(expression)

Options	Description
expression	Expression to operate on.Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT from_unixtime(1672531200);
+----------------------------------+
| from_unixtime(Int64(1672531200)) |
+----------------------------------+
| 2023-01-01T00:00:00              |
+----------------------------------+