Functions for Working with Dates and Times

If month is omitted then day should take a value between 1 and 365 , otherwise it should take a value between 1 and 31 .

The return type of toStartOf* , toLastDayOf* , toMonday , timeSlot functions described below is determined by the configuration parameter enable_extended_results_for_datetime_functions which is 0 by default.

Behavior for

• enable_extended_results_for_datetime_functions = 0 :
• Functions toStartOfYear , toStartOfISOYear , toStartOfQuarter , toStartOfMonth , toStartOfWeek , toLastDayOfWeek , toLastDayOfMonth , toMonday return Date or DateTime .
• Functions toStartOfDay , toStartOfHour , toStartOfFifteenMinutes , toStartOfTenMinutes , toStartOfFiveMinutes , toStartOfMinute , timeSlot return DateTime . Though these functions can take values of the extended types Date32 and DateTime64 as an argument, passing them a time outside the normal range (year 1970 to 2149 for Date / 2106 for DateTime ) will produce wrong results.
• enable_extended_results_for_datetime_functions = 1 :
• Functions toStartOfYear , toStartOfISOYear , toStartOfQuarter , toStartOfMonth , toStartOfWeek , toLastDayOfWeek , toLastDayOfMonth , toMonday return Date or DateTime if their argument is a Date or DateTime , and they return Date32 or DateTime64 if their argument is a Date32 or DateTime64 .
• Functions toStartOfDay , toStartOfHour , toStartOfFifteenMinutes , toStartOfTenMinutes , toStartOfFiveMinutes , toStartOfMinute , timeSlot return DateTime if their argument is a Date or DateTime , and they return DateTime64 if their argument is a Date32 or DateTime64 .

toStartOfYear ​

Rounds down a date or date with time to the first day of the year. Returns the date as a Date object.

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The first day of the year of the input date/time. Date .

Example

Result:

toStartOfISOYear ​

Rounds down a date or date with time to the first day of the ISO year, which can be different than a "regular" year. (See https://en.wikipedia.org/wiki/ISO_week_date .)

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The first day of the year of the input date/time. Date .

Example

Result:

toStartOfQuarter ​

Rounds down a date or date with time to the first day of the quarter. The first day of the quarter is either 1 January, 1 April, 1 July, or 1 October. Returns the date.

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The first day of the quarter of the given date/time. Date .

Example

Result:

toStartOfMonth ​

Rounds down a date or date with time to the first day of the month. Returns the date.

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The first day of the month of the given date/time. Date .

Example

Result:

Note

The behavior of parsing incorrect dates is implementation specific. ClickHouse may return zero date, throw an exception, or do "natural" overflow.

toLastDayOfMonth ​

Rounds a date or date with time to the last day of the month. Returns the date.

Syntax

Alias: LAST_DAY

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The last day of the month of the given date/time=. Date .

Example

Result:

toMonday ​

Rounds down a date or date with time to the nearest Monday. Returns the date.

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The date of the nearest Monday on or prior to the given date. Date .

Example

Result:

toStartOfWeek ​

Rounds a date or date with time down to the nearest Sunday or Monday. Returns the date. The mode argument works exactly like the mode argument in function toWeek() . If no mode is specified, it defaults to 0.

Syntax

Arguments

• t - a Date , Date32 , DateTime or DateTime64
• mode - determines the first day of the week as described in the toWeek() function
• timezone - Optional parameter, it behaves like any other conversion function

Returned value

• The date of the nearest Sunday or Monday on or prior to the given date, depending on the mode. Date .

Example

Result:

toLastDayOfWeek ​

Rounds a date or date with time up to the nearest Saturday or Sunday. Returns the date. The mode argument works exactly like the mode argument in function toWeek() . If no mode is specified, mode is assumed as 0.

Syntax

Arguments

• t - a Date , Date32 , DateTime or DateTime64
• mode - determines the last day of the week as described in the toWeek function
• timezone - Optional parameter, it behaves like any other conversion function

Returned value

• The date of the nearest Sunday or Monday on or after the given date, depending on the mode. Date .

Example

Result:

toStartOfDay ​

Rounds down a date with time to the start of the day.

Syntax

Arguments

• value - a Date , Date32 , DateTime or DateTime64

Returned value

• The start of the day of the given date/time. DateTime .

Example

Result:

toStartOfHour ​

Rounds down a date with time to the start of the hour.

Syntax

Arguments

• value - a DateTime or DateTime64

Returned value

• The start of the hour of the given date/time. DateTime .

Example

Result:

toStartOfMinute ​

Rounds down a date with time to the start of the minute.

Syntax

Arguments

• value - a DateTime or DateTime64

Returned value

• The start of the minute of the given date/time. DateTime .

Example

Result:

toStartOfSecond ​

Truncates sub-seconds.

Syntax

Arguments

• value — Date and time. DateTime64 .
• timezone — Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String .

Returned value

• Input value without sub-seconds. DateTime64 .

Examples

Query without timezone:

Result:

Query with timezone:

Result:

See also

• Timezone server configuration parameter.

toStartOfMillisecond ​

Rounds down a date with time to the start of the milliseconds.

Syntax

Arguments

• value — Date and time. DateTime64 .
• timezone — Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String .

Returned value

• Input value with sub-milliseconds. DateTime64 .

Examples

Query without timezone:

Result:

Query with timezone:

Result:

toStartOfMicrosecond ​

Rounds down a date with time to the start of the microseconds.

Syntax

Arguments

• value — Date and time. DateTime64 .
• timezone — Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String .

Returned value

• Input value with sub-microseconds. DateTime64 .

Examples

Query without timezone:

Result:

Query with timezone:

Result:

See also

• Timezone server configuration parameter.

toStartOfNanosecond ​

Rounds down a date with time to the start of the nanoseconds.

Syntax

Arguments

• value — Date and time. DateTime64 .
• timezone — Timezone for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String .

Returned value

• Input value with nanoseconds. DateTime64 .

Examples

Query without timezone:

Result:

Query with timezone:

Result:

See also

• Timezone server configuration parameter.

toStartOfFiveMinutes ​

Rounds down a date with time to the start of the five-minute interval.

Syntax

Arguments

• value - a DateTime or DateTime64

Returned value

• The start of the five-minute interval of the given date/time. DateTime .

Example

Result:

toStartOfTenMinutes ​

Rounds down a date with time to the start of the ten-minute interval.

Syntax

Arguments

• value - a DateTime or DateTime64

Returned value

• The start of the ten-minute interval of the given date/time. DateTime .

Example

Result:

toStartOfFifteenMinutes ​

Rounds down the date with time to the start of the fifteen-minute interval.

Syntax

Arguments

• value - a DateTime or DateTime64

Returned value

• The start of the fifteen-minute interval of the given date/time. DateTime .

Example

Result:

toStartOfInterval ​

This function generalizes other toStartOf*() functions with toStartOfInterval(date_or_date_with_time, INTERVAL x unit [, time_zone]) syntax. For example,

• toStartOfInterval(t, INTERVAL 1 YEAR) returns the same as toStartOfYear(t) ,
• toStartOfInterval(t, INTERVAL 1 MONTH) returns the same as toStartOfMonth(t) ,
• toStartOfInterval(t, INTERVAL 1 DAY) returns the same as toStartOfDay(t) ,
• toStartOfInterval(t, INTERVAL 15 MINUTE) returns the same as toStartOfFifteenMinutes(t) .

The calculation is performed relative to specific points in time:

Interval	Start
YEAR	year 0
QUARTER	1900 Q1
MONTH	1900 January
WEEK	1970, 1st week (01-05)
DAY	1970-01-01
HOUR	(*)
MINUTE	1970-01-01 00:00:00
SECOND	1970-01-01 00:00:00
MILLISECOND	1970-01-01 00:00:00
MICROSECOND	1970-01-01 00:00:00
NANOSECOND	1970-01-01 00:00:00

(*) hour intervals are special: the calculation is always performed relative to 00:00:00 (midnight) of the current day. As a result, only hour values between 1 and 23 are useful.

If unit WEEK was specified, toStartOfInterval assumes that weeks start on Monday. Note that this behavior is different from that of function toStartOfWeek in which weeks start by default on Sunday.

Syntax

Aliases: time_bucket , date_bin .

The second overload emulates TimescaleDB's time_bucket() function, respectively PostgreSQL's date_bin() function, e.g.

Result:

See Also

• date_trunc

toTimeWithFixedDate ​

Converts a date with time to a certain fixed date, while preserving the time.

Syntax

Alias: toTime - can be used only when the use_legacy_to_time setting is enabled.

Arguments

• date — Date to convert to a time. Date / DateTime / DateTime64 .
• timezone (optional) — Timezone for the returned value. String .

Returned value

• DateTime with date equated to 1970-01-02 while preserving the time. DateTime .
Note

If the date input argument contained sub-second components, they will be dropped in the returned DateTime value with second-accuracy.

Example

Query:

Result:

toRelativeYearNum ​

Converts a date, or date with time, to the number of years elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of years from a fixed reference point in the past. UInt16 .

Example

Query:

Result:

toRelativeQuarterNum ​

Converts a date, or date with time, to the number of quarters elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of quarters from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeMonthNum ​

Converts a date, or date with time, to the number of months elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of months from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeWeekNum ​

Converts a date, or date with time, to the number of weeks elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of weeks from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeDayNum ​

Converts a date, or date with time, to the number of days elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of days from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeHourNum ​

Converts a date, or date with time, to the number of hours elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of hours from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeMinuteNum ​

Converts a date, or date with time, to the number of minutes elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of minutes from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toRelativeSecondNum ​

Converts a date, or date with time, to the number of the seconds elapsed since a certain fixed point in the past.

Syntax

Arguments

• date — Date or date with time. Date / DateTime / DateTime64 .

Returned value

• The number of seconds from a fixed reference point in the past. UInt32 .

Example

Query:

Result:

toISOYear ​

Converts a date, or date with time, to the ISO year as a UInt16 number.

Syntax

Arguments

• value — The value with date or date with time. Date , Date32 , DateTime or DateTime64

Returned value

• The input value converted to a ISO year number. UInt16 .

Example

Query:

Result:

toISOWeek ​

Converts a date, or date with time, to a UInt8 number containing the ISO Week number.

Syntax

Arguments

• value — The value with date or date with time.

Returned value

• value converted to the current ISO week number. UInt8 .

Example

Query:

Response:

toWeek ​

This function returns the week number for date or datetime. The two-argument form of toWeek() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the default mode is 0.

toISOWeek() is a compatibility function that is equivalent to toWeek(date,3) .

The following table describes how the mode argument works.

Mode	First day of week	Range	Week 1 is the first week ...
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year
8	Sunday	1-53	contains January 1
9	Monday	1-53	contains January 1

For mode values with a meaning of "with 4 or more days this year," weeks are numbered according to ISO 8601:1988:

If the week containing January 1 has 4 or more days in the new year, it is week 1.

Otherwise, it is the last week of the previous year, and the next week is week 1.

For mode values with a meaning of "contains January 1", the week contains January 1 is week 1. It does not matter how many days in the new year the week contained, even if it contained only one day. I.e. if the last week of December contains January 1 of the next year, it will be week 1 of the next year.

Syntax

Alias: WEEK

Arguments

• t – Date or DateTime.
• mode – Optional parameter, Range of values is [0,9], default is 0.
• Timezone – Optional parameter, it behaves like any other conversion function.

The first argument can also be specified as String in a format supported by parseDateTime64BestEffort() . Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.

Example

toYearWeek ​

Returns year and week for a date. The year in the result may be different from the year in the date argument for the first and the last week of the year.

The mode argument works like the mode argument to toWeek() . For the single-argument syntax, a mode value of 0 is used.

toISOYear() is a compatibility function that is equivalent to intDiv(toYearWeek(date,3),100) .

Caution

The week number returned by toYearWeek() can be different from what the toWeek() returns. toWeek() always returns week number in the context of the given year, and in case toWeek() returns 0 , toYearWeek() returns the value corresponding to the last week of previous year. See prev_yearWeek in example below.

Syntax

Alias: YEARWEEK

The first argument can also be specified as String in a format supported by parseDateTime64BestEffort() . Support for string arguments exists only for reasons of compatibility with MySQL which is expected by certain 3rd party tools. As string argument support may in future be made dependent on new MySQL-compatibility settings and because string parsing is generally slow, it is recommended to not use it.

Example

toDaysSinceYearZero ​

Returns for a given date, the number of days passed since 1 January 0000 in the proleptic Gregorian calendar defined by ISO 8601 . The calculation is the same as in MySQL's TO_DAYS() function.

Syntax

Alias: TO_DAYS

Arguments

• date — The date to calculate the number of days passed since year zero from. Date , Date32 , DateTime or DateTime64 .
• time_zone — A String type const value or an expression represent the time zone. String types

Returned value

The number of days passed since date 0000-01-01. UInt32 .

Example

Result:

See Also

• fromDaysSinceYearZero

fromDaysSinceYearZero ​

Returns for a given number of days passed since 1 January 0000 the corresponding date in the proleptic Gregorian calendar defined by ISO 8601 . The calculation is the same as in MySQL's FROM_DAYS() function.

The result is undefined if it cannot be represented within the bounds of the Date type.

Syntax

Alias: FROM_DAYS

Arguments

• days — The number of days passed since year zero.

Returned value

The date corresponding to the number of days passed since year zero. Date .

Example

Result:

See Also

• toDaysSinceYearZero

fromDaysSinceYearZero32 ​

Like fromDaysSinceYearZero but returns a Date32 .

age ​

Returns the unit component of the difference between startdate and enddate . The difference is calculated using a precision of 1 nanosecond. E.g. the difference between 2021-12-29 and 2022-01-01 is 3 days for day unit, 0 months for month unit, 0 years for year unit.

For an alternative to age , see function date_diff .

Syntax

Arguments

unit — The type of interval for result. String . Possible values:

• nanosecond , nanoseconds , ns
• microsecond , microseconds , us , u
• millisecond , milliseconds , ms
• second , seconds , ss , s
• minute , minutes , mi , n
• hour , hours , hh , h
• day , days , dd , d
• week , weeks , wk , ww
• month , months , mm , m
• quarter , quarters , qq , q
• year , years , yyyy , yy

startdate — The first time value to subtract (the subtrahend). Date , Date32 , DateTime or DateTime64 .

enddate — The second time value to subtract from (the minuend). Date , Date32 , DateTime or DateTime64 .

timezone — Timezone name (optional). If specified, it is applied to both startdate and enddate . If not specified, timezones of startdate and enddate are used. If they are not the same, the result is unspecified. String .

Returned value

Difference between enddate and startdate expressed in unit . Int .

Example

Result:

Result:

date_diff ​

Returns the count of the specified unit boundaries crossed between the startdate and the enddate . The difference is calculated using relative units, e.g. the difference between 2021-12-29 and 2022-01-01 is 3 days for unit day (see toRelativeDayNum ), 1 month for unit month (see toRelativeMonthNum ) and 1 year for unit year (see toRelativeYearNum ).

If unit week was specified, date_diff assumes that weeks start on Monday. Note that this behavior is different from that of function toWeek() in which weeks start by default on Sunday.

For an alternative to date_diff , see function age .

Syntax

Aliases: dateDiff , DATE_DIFF , timestampDiff , timestamp_diff , TIMESTAMP_DIFF .

Arguments

unit — The type of interval for result. String . Possible values:

• nanosecond , nanoseconds , ns
• microsecond , microseconds , us , u
• millisecond , milliseconds , ms
• second , seconds , ss , s
• minute , minutes , mi , n
• hour , hours , hh , h
• day , days , dd , d
• week , weeks , wk , ww
• month , months , mm , m
• quarter , quarters , qq , q
• year , years , yyyy , yy

startdate — The first time value to subtract (the subtrahend). Date , Date32 , DateTime or DateTime64 .

enddate — The second time value to subtract from (the minuend). Date , Date32 , DateTime or DateTime64 .

timezone — Timezone name (optional). If specified, it is applied to both startdate and enddate . If not specified, timezones of startdate and enddate are used. If they are not the same, the result is unspecified. String .

Returned value

Difference between enddate and startdate expressed in unit . Int .

Example

Result:

Result:

date_trunc ​

Truncates date and time data to the specified part of date.

Syntax

Alias: dateTrunc .

Arguments

unit — The type of interval to truncate the result. String Literal . Possible values:

• nanosecond - Compatible only with DateTime64
• microsecond - Compatible only with DateTime64
• millisecond - Compatible only with DateTime64
• second
• minute
• month
• quarter

unit argument is case-insensitive.

value — Date and time. Date , Date32 , DateTime or DateTime64 .

timezone — Timezone name for the returned value (optional). If not specified, the function uses the timezone of the value parameter. String .

Returned value

If the unit argument is Year, Quarter, Month, or Week,

• and the value argument is Date32 or DateTime64, then Date32 is returned,
• otherwise, Date is returned.

If the unit argument is Day, Hour, Minute, or Second,

• and the value argument is Date32 or DateTime64, then DateTime64 is returned,
• otherwise, DateTime is returned.

If the unit argument is Millisecond, Microsecond, or Nanosecond, then DateTime64 with scale 3 or 6 or 9 (depending on the unit argument) is returned.

Example

Query without timezone:

Result:

Query with the specified timezone:

Result:

See Also

• toStartOfInterval

date_add ​

Adds the time interval or date interval to the provided date or date with time.

If the addition results in a value outside the bounds of the data type, the result is undefined.

Syntax

Alternative syntax:

Aliases: dateAdd , DATE_ADD .

Arguments

unit — The type of interval to add. Note: This is not a String and must therefore not be quoted. Possible values:

• second
• minute
• month
• quarter

value — Value of interval to add. Int .

date — The date or date with time to which value is added. Date , Date32 , DateTime or DateTime64 .

Returned value

Date or date with time obtained by adding value , expressed in unit , to date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

Result:

See Also

• addDate

date_sub ​

Subtracts the time interval or date interval from the provided date or date with time.

If the subtraction results in a value outside the bounds of the data type, the result is undefined.

Syntax

Alternative syntax:

Aliases: dateSub , DATE_SUB .

Arguments

unit — The type of interval to subtract. Note: This is not a String and must therefore not be quoted.

Possible values:

• second
• minute
• month
• quarter

value — Value of interval to subtract. Int .

date — The date or date with time from which value is subtracted. Date , Date32 , DateTime or DateTime64 .

Returned value

Date or date with time obtained by subtracting value , expressed in unit , from date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

Result:

See Also

• subDate

timestamp_add ​

Adds the specified time value with the provided date or date time value.

If the addition results in a value outside the bounds of the data type, the result is undefined.

Syntax

Aliases: timeStampAdd , TIMESTAMP_ADD .

Arguments

date — Date or date with time. Date , Date32 , DateTime or DateTime64 .

value — Value of interval to add. Int .

unit — The type of interval to add. String . Possible values:

• second
• minute
• month
• quarter

Returned value

Date or date with time with the specified value expressed in unit added to date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

timestamp_sub ​

Subtracts the time interval from the provided date or date with time.

If the subtraction results in a value outside the bounds of the data type, the result is undefined.

Syntax

Aliases: timeStampSub , TIMESTAMP_SUB .

Arguments

unit — The type of interval to subtract. String . Possible values:

• second
• minute
• month
• quarter

value — Value of interval to subtract. Int .

date — Date or date with time. Date , Date32 , DateTime or DateTime64 .

Returned value

Date or date with time obtained by subtracting value , expressed in unit , from date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

addDate ​

Adds the time interval to the provided date, date with time or String-encoded date / date with time.

If the addition results in a value outside the bounds of the data type, the result is undefined.

Syntax

Arguments

• date — The date or date with time to which interval is added. Date , Date32 , DateTime , DateTime64 , or String
• interval — Interval to add. Interval .

Returned value

Date or date with time obtained by adding interval to date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

Alias: ADDDATE

See Also

• date_add

subDate ​

Subtracts the time interval from the provided date, date with time or String-encoded date / date with time.

If the subtraction results in a value outside the bounds of the data type, the result is undefined.

Syntax

Arguments

• date — The date or date with time from which interval is subtracted. Date , Date32 , DateTime , DateTime64 , or String
• interval — Interval to subtract. Interval .

Returned value

Date or date with time obtained by subtracting interval from date . Date , Date32 , DateTime or DateTime64 .

Example

Result:

Alias: SUBDATE

See Also

• date_sub

now ​

Returns the current date and time at the moment of query analysis. The function is a constant expression.

Alias: current_timestamp .

Syntax

Arguments

• timezone — Timezone name for the returned value (optional). String .

Returned value

• Current date and time. DateTime .

Example

Query without timezone:

Result:

Query with the specified timezone:

Result:

now64 ​

Returns the current date and time with sub-second precision at the moment of query analysis. The function is a constant expression.

Syntax

Arguments

• scale - Tick size (precision): 10 ^-precision seconds. Valid range: [ 0 : 9 ]. Typically, are used - 3 (default) (milliseconds), 6 (microseconds), 9 (nanoseconds).
• timezone — Timezone name for the returned value (optional). String .

Returned value

• Current date and time with sub-second precision. DateTime64 .

Example

Result:

nowInBlock ​

Returns the current date and time at the moment of processing of each block of data. In contrast to the function now , it is not a constant expression, and the returned value will be different in different blocks for long-running queries.

It makes sense to use this function to generate the current time in long-running INSERT SELECT queries.

Syntax

Arguments

• timezone — Timezone name for the returned value (optional). String .

Returned value

• Current date and time at the moment of processing of each block of data. DateTime .

Example

Result:

today ​

Returns the current date at moment of query analysis. It is the same as 'toDate(now())' and has aliases: curdate , current_date .

Syntax

Arguments

Returned value

• Current date. DateTime .

Example

Query:

Result :

Running the query above on the 3rd of March 2024 would have returned the following response:

yesterday ​

Accepts zero arguments and returns yesterday's date at one of the moments of query analysis. The same as 'today() - 1'.

timeSlot ​

Round the time to the start of a half-an-hour length interval.

Syntax

Arguments

• time — Time to round to the start of a half-an-hour length interval. DateTime / Date32 / DateTime64 .
• time_zone — A String type const value or an expression representing the time zone. String .
Note

Though this function can take values of the extended types Date32 and DateTime64 as an argument, passing it a time outside the normal range (year 1970 to 2149 for Date / 2106 for DateTime ) will produce wrong results.

Return type

• Returns the time rounded to the start of a half-an-hour length interval. DateTime .

Example

Query:

Result:

toYYYYMM ​

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

This function is the opposite of function YYYYMMDDToDate() .

Example

Result:

toYYYYMMDD ​

Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 10000 + MM * 100 + DD). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

Example

Result:

toYYYYMMDDhhmmss ​

Converts a date or date with time to a UInt64 number containing the year and month number (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss). Accepts a second optional timezone argument. If provided, the timezone must be a string constant.

Example

Result:

YYYYMMDDToDate ​

Converts a number containing the year, month and day number to a Date .

This function is the opposite of function toYYYYMMDD() .

The output is undefined if the input does not encode a valid Date value.

Syntax

Arguments

• yyyymmdd - A number representing the year, month and day. Integer , Float or Decimal .

Returned value

• a date created from the arguments. Date .

Example

Result:

YYYYMMDDToDate32 ​

Like function YYYYMMDDToDate() but produces a Date32 .

YYYYMMDDhhmmssToDateTime ​

Converts a number containing the year, month, day, hours, minute and second number to a DateTime .

The output is undefined if the input does not encode a valid DateTime value.

This function is the opposite of function toYYYYMMDDhhmmss() .

Syntax

Arguments

• yyyymmddhhmmss - A number representing the year, month and day. Integer , Float or Decimal .
• timezone - Timezone for the returned value (optional).

Returned value

• a date with time created from the arguments. DateTime .

Example

Result:

YYYYMMDDhhmmssToDateTime64 ​

Like function YYYYMMDDhhmmssToDate() but produces a DateTime64 .

Accepts an additional, optional precision parameter after the timezone parameter.

changeYear ​

Changes the year component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the year. Integer .

Returned value

• The same type as date_or_datetime .

Example

Result:

changeMonth ​

Changes the month component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the month. Integer .

Returned value

• Returns a value of same type as date_or_datetime .

Example

Result:

changeDay ​

Changes the day component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the day. Integer .

Returned value

• Returns a value of same type as date_or_datetime .

Example

Result:

changeHour ​

Changes the hour component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the hour. Integer .

Returned value

• Returns a value of same type as date_or_datetime . If the input is a Date , return DateTime . If the input is a Date32 , return DateTime64 .

Example

Result:

changeMinute ​

Changes the minute component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the minute. Integer .

Returned value

• Returns a value of same type as date_or_datetime . If the input is a Date , return DateTime . If the input is a Date32 , return DateTime64 .

Example

Result:

changeSecond ​

Changes the second component of a date or date time.

Syntax

Arguments

• date_or_datetime - a Date , Date32 , DateTime or DateTime64
• value - a new value of the second. Integer .

Returned value

• Returns a value of same type as date_or_datetime . If the input is a Date , return DateTime . If the input is a Date32 , return DateTime64 .

Example

Result:

addYears ​

Adds a specified number of years to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of years to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of years to add. (U)Int* , Float* .

Returned value

• Returns date plus num years. Date / Date32 / DateTime / DateTime64 .

Example

addQuarters ​

Adds a specified number of quarters to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of quarters to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of quarters to add. (U)Int* , Float* .

Returned value

• Returns date plus num quarters. Date / Date32 / DateTime / DateTime64 .

Example

addMonths ​

Adds a specified number of months to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of months to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of months to add. (U)Int* , Float* .

Returned value

• Returns date plus num months. Date / Date32 / DateTime / DateTime64 .

Example

addWeeks ​

Adds a specified number of weeks to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of weeks to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of weeks to add. (U)Int* , Float* .

Returned value

• Returns date plus num weeks. Date / Date32 / DateTime / DateTime64 .

Example

addDays ​

Adds a specified number of days to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of days to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of days to add. (U)Int* , Float* .

Returned value

• Returns date plus num days. Date / Date32 / DateTime / DateTime64 .

Example

addHours ​

Adds a specified number of days to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of hours to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of hours to add. (U)Int* , Float* .

Returned value

• Returns date plus num hours. Date / Date32 / DateTime / DateTime64 .

Example

addMinutes ​

Adds a specified number of minutes to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of minutes to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of minutes to add. (U)Int* , Float* .

Returned value

• Returns date plus num minutes. Date / Date32 / DateTime / DateTime64 .

Example

addSeconds ​

Adds a specified number of seconds to a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to add specified number of seconds to. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of seconds to add. (U)Int* , Float* .

Returned value

• Returns date plus num seconds. Date / Date32 / DateTime / DateTime64 .

Example

addMilliseconds ​

Adds a specified number of milliseconds to a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to add specified number of milliseconds to. DateTime / DateTime64 , String .
• num : Number of milliseconds to add. (U)Int* , Float* .

Returned value

• Returns date_time plus num milliseconds. DateTime64 .

Example

addMicroseconds ​

Adds a specified number of microseconds to a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to add specified number of microseconds to. DateTime / DateTime64 , String .
• num : Number of microseconds to add. (U)Int* , Float* .

Returned value

• Returns date_time plus num microseconds. DateTime64 .

Example

addNanoseconds ​

Adds a specified number of microseconds to a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to add specified number of nanoseconds to. DateTime / DateTime64 , String .
• num : Number of nanoseconds to add. (U)Int* , Float* .

Returned value

• Returns date_time plus num nanoseconds. DateTime64 .

Example

addInterval ​

Adds an interval to another interval or tuple of intervals.

Syntax

Parameters

• interval_1 : First interval or tuple of intervals. interval , tuple ( interval ).
• interval_2 : Second interval to be added. interval .

Returned value

• Returns a tuple of intervals. tuple ( interval ).
Note

Intervals of the same type will be combined into a single interval. For instance if toIntervalDay(1) and toIntervalDay(2) are passed then the result will be (3) rather than (1,1) .

Example

Query:

Result:

addTupleOfIntervals ​

Consecutively adds a tuple of intervals to a Date or a DateTime.

Syntax

Parameters

• date : First interval or interval of tuples. date / date32 / datetime / datetime64 .
• intervals : Tuple of intervals to add to date . tuple ( interval ).

Returned value

• Returns date with added intervals . date / date32 / datetime / datetime64 .

Example

Query:

Result:

subtractYears ​

Subtracts a specified number of years from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of years from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of years to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num years. Date / Date32 / DateTime / DateTime64 .

Example

subtractQuarters ​

Subtracts a specified number of quarters from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of quarters from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of quarters to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num quarters. Date / Date32 / DateTime / DateTime64 .

Example

subtractMonths ​

Subtracts a specified number of months from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of months from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of months to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num months. Date / Date32 / DateTime / DateTime64 .

Example

subtractWeeks ​

Subtracts a specified number of weeks from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of weeks from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of weeks to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num weeks. Date / Date32 / DateTime / DateTime64 .

Example

subtractDays ​

Subtracts a specified number of days from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of days from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of days to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num days. Date / Date32 / DateTime / DateTime64 .

Example

subtractHours ​

Subtracts a specified number of hours from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of hours from. Date / Date32 / Datetime / Datetime64 , String .
• num : Number of hours to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num hours. Date / Date32 / Datetime / DateTime64 .

Example

subtractMinutes ​

Subtracts a specified number of minutes from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of minutes from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of minutes to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num minutes. Date / Date32 / DateTime / DateTime64 .

Example

subtractSeconds ​

Subtracts a specified number of seconds from a date, a date with time or a string-encoded date / date with time.

Syntax

Parameters

• date : Date / date with time to subtract specified number of seconds from. Date / Date32 / DateTime / DateTime64 , String .
• num : Number of seconds to subtract. (U)Int* , Float* .

Returned value

• Returns date minus num seconds. Date / Date32 / DateTime / DateTime64 .

Example

subtractMilliseconds ​

Subtracts a specified number of milliseconds from a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to subtract specified number of milliseconds from. DateTime / DateTime64 , String .
• num : Number of milliseconds to subtract. (U)Int* , Float* .

Returned value

• Returns date_time minus num milliseconds. DateTime64 .

Example

subtractMicroseconds ​

Subtracts a specified number of microseconds from a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to subtract specified number of microseconds from. DateTime / DateTime64 , String .
• num : Number of microseconds to subtract. (U)Int* , Float* .

Returned value

• Returns date_time minus num microseconds. DateTime64 .

Example

subtractNanoseconds ​

Subtracts a specified number of nanoseconds from a date with time or a string-encoded date with time.

Syntax

Parameters

• date_time : Date with time to subtract specified number of nanoseconds from. DateTime / DateTime64 , String .
• num : Number of nanoseconds to subtract. (U)Int* , Float* .

Returned value

• Returns date_time minus num nanoseconds. DateTime64 .

Example

subtractInterval ​

Adds a negated interval to another interval or tuple of intervals.

Syntax

Parameters

• interval_1 : First interval or interval of tuples. interval , tuple ( interval ).
• interval_2 : Second interval to be negated. interval .

Returned value

• Returns a tuple of intervals. tuple ( interval ).
Note

Intervals of the same type will be combined into a single interval. For instance if toIntervalDay(2) and toIntervalDay(1) are passed then the result will be (1) rather than (2,1)

Example

Query:

Result:

subtractTupleOfIntervals ​

Consecutively subtracts a tuple of intervals from a Date or a DateTime.

Syntax

Parameters

• date : First interval or interval of tuples. Date / Date32 / DateTime / DateTime64 .
• intervals : Tuple of intervals to subtract from date . tuple ( interval ).

Returned value

• Returns date with subtracted intervals . Date / Date32 / DateTime / DateTime64 .

Example

Query:

Result:

timeSlots ​

For a time interval starting at 'StartTime' and continuing for 'Duration' seconds, it returns an array of moments in time, consisting of points from this interval rounded down to the 'Size' in seconds. 'Size' is an optional parameter set to 1800 (30 minutes) by default. This is necessary, for example, when searching for pageviews in the corresponding session. Accepts DateTime and DateTime64 as 'StartTime' argument. For DateTime, 'Duration' and 'Size' arguments must be UInt32 . For 'DateTime64' they must be Decimal64 . Returns an array of DateTime/DateTime64 (return type matches the type of 'StartTime'). For DateTime64, the return value's scale can differ from the scale of 'StartTime' --- the highest scale among all given arguments is taken.

Syntax

Example

Result:

formatDateTime ​

Formats a Time according to the given Format string. Format is a constant expression, so you cannot have multiple formats for a single result column.

formatDateTime uses MySQL datetime format style, refer to https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format .

The opposite operation of this function is parseDateTime .

Alias: DATE_FORMAT .

Syntax

Returned value(s)

Returns time and date values according to the determined format.

Replacement fields

Using replacement fields, you can define a pattern for the resulting string. "Example" column shows formatting result for 2018-01-02 22:33:44 .

Placeholder	Description	Example
%a	abbreviated weekday name (Mon-Sun)	Mon
%b	abbreviated month name (Jan-Dec)	Jan
%c	month as an integer number (01-12), see 'Note 4' below	01
%C	year divided by 100 and truncated to integer (00-99)	20
%d	day of the month, zero-padded (01-31)	02
%D	Short MM/DD/YY date, equivalent to %m/%d/%y	01/02/18
%e	day of the month, space-padded ( 1-31), see 'Note 5' below	2
%f	fractional second, see 'Note 1' and 'Note 2' below	123456
%F	short YYYY-MM-DD date, equivalent to %Y-%m-%d	2018-01-02
%g	two-digit year format, aligned to ISO 8601, abbreviated from four-digit notation	18
%G	four-digit year format for ISO week number, calculated from the week-based year defined by the ISO 8601 standard, normally useful only with %V	2018
%h	hour in 12h format (01-12)	09
%H	hour in 24h format (00-23)	22
%i	minute (00-59)	33
%I	hour in 12h format (01-12)	10
%j	day of the year (001-366)	002
%k	hour in 24h format (00-23), see 'Note 4' below	14
%l	hour in 12h format (01-12), see 'Note 4' below	09
%m	month as an integer number (01-12)	01
%M	full month name (January-December), see 'Note 3' below	January
%n	new-line character ('')
%p	AM or PM designation	PM
%Q	Quarter (1-4)	1
%r	12-hour HH :MM AM/PM time, equivalent to %h:%i %p	10:30 PM
%R	24-hour HH :MM time, equivalent to %H:%i	22:33
%s	second (00-59)	44
%S	second (00-59)	44
%t	horizontal-tab character (')
%T	ISO 8601 time format (HH:MM :SS ), equivalent to %H:%i:%S	22:33:44
%u	ISO 8601 weekday as number with Monday as 1 (1-7)	2
%V	ISO 8601 week number (01-53)	01
%w	weekday as a integer number with Sunday as 0 (0-6)	2
%W	full weekday name (Monday-Sunday)	Monday
%y	Year, last two digits (00-99)	18
%Y	Year	2018
%z	Time offset from UTC as +HHMM or -HHMM	-0500
%%	a % sign	%

Note 1: In ClickHouse versions earlier than v23.4, %f prints a single zero (0) if the formatted value is a Date, Date32 or DateTime (which have no fractional seconds) or a DateTime64 with a precision of 0. The previous behavior can be restored using setting formatdatetime_f_prints_single_zero = 1 .

Note 2: In ClickHouse versions earlier than v25.1, %f prints as many digits as specified by the scale of the DateTime64 instead of fixed 6 digits. The previous behavior can be restored using setting formatdatetime_f_prints_scale_number_of_digits= 1 .

Note 3: In ClickHouse versions earlier than v23.4, %M prints the minute (00-59) instead of the full month name (January-December). The previous behavior can be restored using setting formatdatetime_parsedatetime_m_is_month_name = 0 .

Note 4: In ClickHouse versions earlier than v23.11, function parseDateTime required leading zeros for formatters %c (month) and %l / %k (hour), e.g. 07 . In later versions, the leading zero may be omitted, e.g. 7 . The previous behavior can be restored using setting parsedatetime_parse_without_leading_zeros = 0 . Note that function formatDateTime by default still prints leading zeros for %c and %l / %k to not break existing use cases. This behavior can be changed by setting formatdatetime_format_without_leading_zeros = 1 .

Note 5: In ClickHouse versions earlier than v25.5, function parseDateTime required for formatter %e that single-digit days are space padded, e.g. 3 . In later versions, space padding is optional, e.g. 3 and 3 work. To retain the previous behaviour, set setting parsedatetime_e_requires_space_padding = 1 . Similarly, formatter %e in function formatDateTime previously space-padded single-printed unconditionally whereas it now prints them without leading whitespace. To retain the previous behavior, set setting formatdatetime_e_with_space_padding = 1 .

Example

Result:

Result:

Additionally, the formatDateTime function can take a third String argument containing the name of the time zone. Example: Asia/Istanbul . In this case, the time is formatted according to the specified time zone.

Example

See Also

• formatDateTimeInJodaSyntax

formatDateTimeInJodaSyntax ​

Similar to formatDateTime, except that it formats datetime in Joda style instead of MySQL style. Refer to https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html .

The opposite operation of this function is parseDateTimeInJodaSyntax .

Replacement fields

Using replacement fields, you can define a pattern for the resulting string.

Placeholder	Description	Presentation	Examples
G	era	text	AD
C	century of era (>=0)	number	20
Y	year of era (>=0)	year	1996
x	weekyear (not supported yet)	year	1996
w	week of weekyear (not supported yet)	number	27
e	day of week	number	2
E	day of week	text	Tuesday; Tue
y	year	year	1996
D	day of year	number	189
M	month of year	month	July; Jul; 07
d	day of month	number	10
a	halfday of day	text	PM
K	hour of halfday (0~11)	number	0
h	clockhour of halfday (1~12)	number	12
H	hour of day (0~23)	number	0
k	clockhour of day (1~24)	number	24
m	minute of hour	number	30
s	second of minute	number	55
S	fraction of second	number	978
z	time zone	text	Eastern Standard Time; EST
Z	time zone offset	zone	-0800; -0812
'	escape for text	delimiter
''	single quote	literal	'

Example

Result:

dateName ​

Returns specified part of date.

Syntax

Arguments

• date_part — Date part. Possible values: 'year', 'quarter', 'month', 'week', 'dayofyear', 'day', 'weekday', 'hour', 'minute', 'second'. String .
• date — Date. Date , Date32 , DateTime or DateTime64 .
• timezone — Timezone. Optional. String .

Returned value

• The specified part of date. String

Example

Result:

monthName ​

Returns name of the month.

Syntax

Arguments

• date — Date or date with time. Date , DateTime or DateTime64 .

Returned value

• The name of the month. String

Example

Result:

fromUnixTimestamp ​

This function converts a Unix timestamp to a calendar date and a time of a day.

It can be called in two ways:

When given a single argument of type Integer , it returns a value of type DateTime , i.e. behaves like toDateTime .

Alias: FROM_UNIXTIME .

Example:

Result:

When given two or three arguments where the first argument is a value of type Integer , Date , Date32 , DateTime or DateTime64 , the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type String , i.e. it behaves like formatDateTime . In this case, MySQL's datetime format style is used.

Example:

Result:

See Also

• fromUnixTimestampInJodaSyntax

fromUnixTimestampInJodaSyntax ​

Same as fromUnixTimestamp but when called in the second way (two or three arguments), the formatting is performed using Joda style instead of MySQL style.

Example:

Result:

toModifiedJulianDay ​

Converts a Proleptic Gregorian calendar date in text form YYYY-MM-DD to a Modified Julian Day number in Int32. This function supports date from 0000-01-01 to 9999-12-31 . It raises an exception if the argument cannot be parsed as a date, or the date is invalid.

Syntax

Arguments

• date — Date in text form. String or FixedString .

Returned value

• Modified Julian Day number. Int32 .

Example

Result:

toModifiedJulianDayOrNull ​

Similar to toModifiedJulianDay() , but instead of raising exceptions it returns NULL .

Syntax

Arguments

• date — Date in text form. String or FixedString .

Returned value

• Modified Julian Day number. Nullable(Int32) .

Example

Result:

fromModifiedJulianDay ​

Converts a Modified Julian Day number to a Proleptic Gregorian calendar date in text form YYYY-MM-DD . This function supports day number from -678941 to 2973483 (which represent 0000-01-01 and 9999-12-31 respectively). It raises an exception if the day number is outside of the supported range.

Syntax

Arguments

• day — Modified Julian Day number. Any integral types .

Returned value

• Date in text form. String

Example

Result:

fromModifiedJulianDayOrNull ​

Similar to fromModifiedJulianDayOrNull() , but instead of raising exceptions it returns NULL .

Syntax

Arguments

• day — Modified Julian Day number. Any integral types .

Returned value

• Date in text form. Nullable(String)

Example

Result:

toUTCTimestamp ​

Convert DateTime/DateTime64 type value from other time zone to UTC timezone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks.

Syntax

Arguments

• time_val — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types
• time_zone — A String type const value or an expression represent the time zone. String types

Returned value

• DateTime/DateTime64 in text form

Example

Result:

fromUTCTimestamp ​

Convert DateTime/DateTime64 type value from UTC timezone to other time zone timestamp. This function is mainly included for compatibility with Apache Spark and similar frameworks.

Syntax

Arguments

• time_val — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types
• time_zone — A String type const value or an expression represent the time zone. String types

Returned value

• DateTime/DateTime64 in text form

Example

Result:

UTCTimestamp ​

Returns the current date and time at the moment of query analysis. The function is a constant expression.

Note

This function gives the same result that now('UTC') would. It was added only for MySQL support and now is the preferred usage.

Syntax

Alias: UTC_timestamp .

Returned value

• Returns the current date and time at the moment of query analysis. DateTime .

Example

Query:

Result:

timeDiff ​

Returns the difference between two dates or dates with time values. The difference is calculated in units of seconds. It is same as dateDiff and was added only for MySQL support. dateDiff is preferred.

Syntax

Arguments *

• first_datetime — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types
• second_datetime — A DateTime/DateTime64 type const value or an expression . DateTime/DateTime64 types

Returned value

The difference between two dates or dates with time values in seconds.

Example

Query: