最近更新时间:2022.02.28 12:33:07
首次发布时间:2022.02.28 12:33:07
请注意:
下文中的一些示例引用自 ClickHouse 社区文档 并经过一定修改确保可以在 ByteHouse 中正常使用。
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
addDays(date, interval) addDays(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addDays(date, 1) AS add_days_with_date, addDays(date_time, 1) AS add_days_with_date_time
┌─add_days_with_date─┬─add_days_with_date_time─┐ │ 2018-01-02 │ 2018-01-02 00:00:00 │ └────────────────────┴─────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
addHours(date, interval) addHours(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addHours(date, 1) AS add_hours_with_date, addHours(date_time, 1) AS add_hours_with_date_time
┌─add_hours_with_date─┬─add_hours_with_date_time─┐ │ 2018-01-01 01:00:00 │ 2018-01-01 01:00:00 │ └─────────────────────┴──────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
addMinutes(date, interval) addMinutes(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addMinutes(date, 1) AS add_minutes_with_date, addMinutes(date_time, 1) AS add_minutes_with_date_time
┌─add_minutes_with_date─┬─add_minutes_with_date_time─┐ │ 2018-01-01 00:01:00 │ 2018-01-01 00:01:00 │ └───────────────────────┴────────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime. For example:
Syntax
addMonths(date, interval) addMonths(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addMonths(date, 1) AS add_months_with_date, addMonths(date_time, 1) AS add_months_with_date_time
┌─add_months_with_date─┬─add_months_with_date_time─┐ │ 2018-02-01 │ 2018-02-01 00:00:00 │ └──────────────────────┴───────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
addQuarters(date, interval) addQuarters(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addQuarters(date, 1) AS add_quarters_with_date, addQuarters(date_time, 1) AS add_quarters_with_date_time
┌─add_quarters_with_date─┬─add_quarters_with_date_time─┐ │ 2018-04-01 │ 2018-04-01 00:00:00 │ └────────────────────────┴─────────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime. For example:
Syntax
addSeconds(date, interval) addSeconds(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addSeconds(date, 1) AS add_seconds_with_date, addSeconds(date_time, 1) AS add_seconds_with_date_time
┌─add_seconds_with_date─┬─add_seconds_with_date_time─┐ │ 2018-01-01 00:00:01 │ 2018-01-01 00:00:01 │ └───────────────────────┴────────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
addWeeks(date, interval) addWeeks(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addWeeks(date, 1) AS add_weeks_with_date, addWeeks(date_time, 1) AS add_weeks_with_date_time
┌─add_weeks_with_date─┬─add_weeks_with_date_time─┐ │ 2018-01-08 │ 2018-01-08 00:00:00 │ └─────────────────────┴──────────────────────────┘
Function adds a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime. For example:
Syntax
addYears(date, interval) addYears(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2018-01-01') AS date, toDateTime('2018-01-01 00:00:00') AS date_time SELECT addYears(date, 1) AS add_years_with_date, addYears(date_time, 1) AS add_years_with_date_time
┌─add_years_with_date─┬─add_years_with_date_time─┐ │ 2019-01-01 │ 2019-01-01 00:00:00 │ └─────────────────────┴──────────────────────────┘
Returns the difference between two dates or dates with time values.
Syntax
dateDiff('unit', startdate, enddate, [timezone])
Arguments
unit
— The type of interval for result. String.startdate
— The first time value to subtract (the subtrahend). Date or DateTime.enddate
— The second time value to subtract from (the minuend). Date or DateTime.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.Example
SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'));
┌─dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00'))─┐ │ 25 │ └────────────────────────────────────────────────────────────────────────────────────────┘
Adds the time interval or date interval to the provided date or date with time.
Syntax
date_add(date, value) date_add(datetime, value [,timezone])
Arguments
date
— The date or date with time to which value
is added. Date or DateTime.timezone
- The timezone argument is allowed only when the 1st argument has the type DateTimeReturned value
Date or date with time obtained by adding value
.
Type: Date or DateTime.
Example
SELECT date_add(toDate('2018-01-01'),3);
┌─date_add(toDate('2018-01-01'), 3)─┐ │ 2018-01-04 │ └───────────────────────────────────┘
SELECT date_add(toDateTime('2018-01-01 00:00:00'), 3, 'UTC');
┌─date_add(toDateTime('2018-01-01 00:00:00'), 3, 'UTC')─┐ │ 2018-01-03 16:00:00 │ └───────────────────────────────────────────────────────┘
Subtracts the time interval or date interval from the provided date or date with time.
Syntax
date_sub(date, value) date_sub(datetime, value [,timezone])
Arguments
date
— The date or date with time to which value
is added. Date or DateTime.timezone
- The timezone argument is allowed only when the 1st argument has the type DateTimeReturned value
Date or date with time obtained by subtracting value
.
Type: Date or DateTime.
Example
SELECT date_sub(toDate('2018-01-01'),3);
┌─date_sub(toDate('2018-01-01'), 3)─┐ │ 2017-12-29 │ └───────────────────────────────────┘
SELECT date_sub(toDateTime('2018-01-01 00:00:00'), 3, 'UTC');
┌─date_sub(toDateTime('2018-01-01 00:00:00'), 3, 'UTC')─┐ │ 2017-12-28 16:00:00 │ └───────────────────────────────────────────────────────┘
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.
Syntax
formatDateTime(Time, Format\[, Timezone\])
Arguments
Time
- Date/DateTimeFormat
- Format patternReplacement 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 |
---|---|---|
%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) | 2 |
%F | short YYYY-MM-DD date, equivalent to %Y-%m-%d | 2018-01-02 |
%H | hour in 24h format (00-23) | 22 |
%I | hour in 12h format (01-12) | 10 |
%j | day of the year (001-366) | 002 |
%m | month as a decimal number (01-12) | 01 |
%M | minute (00-59) | 33 |
%n | new-line character (‘’) | |
%p | AM or PM designation | PM |
%R | 24-hour HH:MMtime, equivalent to %H:%M | 22:33 |
%S | second (00-59) | 44 |
%t | horizontal-tab character (’) | |
%T | ISO8601 time format (HH:MM:SS), equivalent to %H:%M:%S | 22:33:44 |
%u | ISO8601 weekday as number with Monday as 1 (1-7) | 2 |
%V | ISO8601 week number (01-53) | 01 |
%w | weekday as a decimal number with Sunday as 0 (0-6) | 2 |
%y | Year, last two digits (00-99) | 18 |
%Y | Year | 2018 |
%% | a % sign | % |
Returned value(s)
Returns time and date values according to the determined format.
Example
SELECT formatDateTime(toDate('2010-01-04'), '%d');
┌─formatDateTime(toDate('2010-01-04'), '%d')─┐ │ 04 │ └────────────────────────────────────────────┘ ```<!-- TODO: NOT SUPPORT BY BYTEYARD --> <!-- ## from_unixtime Function converts Unix timestamp to a calendar date and a time of a day. When there is only a single argument of Integer type, it acts in the same way as toDateTime and return DateTime type. **Example** ```sql SELECT FROM_UNIXTIME(423543535);
┌─FROM_UNIXTIME(423543535)─┐ │ 1983-06-04 10:58:55 │ └──────────────────────────┘
When there are two arguments: first is an Integer or DateTime, second is a constant format string — it acts in the same way as formatDateTime and return String type.
For example:
SELECT FROM_UNIXTIME(1234334543, '%Y-%m-%d %R:%S') AS DateTime;
┌─DateTime────────────┐ │ 2009-02-11 14:42:23 │ └─────────────────────┘
-->
Returns the current date and time.
Syntax
now()
Returned value
Type: Datetime.
Example
SELECT now();
┌─now()───────────────┐ │ 2021-08-11 14:35:47 │ └─────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractDays(date, interval) subtractDays(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractDays(date, 1) AS subtract_days_with_date, subtractDays(date_time, 1) AS subtract_days_with_date_time
┌─subtract_days_with_date─┬─subtract_days_with_date_time─┐ │ 2018-12-31 │ 2018-12-31 00:00:00 │ └─────────────────────────┴──────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractHours(date, interval) subtractHours(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractHours(date, 1) AS subtract_hours_with_date, subtractHours(date_time, 1) AS subtract_hours_with_date_time
┌─subtract_hours_with_date─┬─subtract_hours_with_date_time─┐ │ 2002-11-25 │ 2018-12-31 23:00:00 │ └──────────────────────────┴───────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractMinutes(date, interval) subtractMinutes(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractMinutes(date, 1) AS subtract_minutes_with_date, subtractMinutes(date_time, 1) AS subtract_minutes_with_date_time
┌─subtract_minutes_with_date─┬─subtract_minutes_with_date_time─┐ │ 2012-08-04 │ 2018-12-31 23:59:00 │ └────────────────────────────┴─────────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractMonths(date, interval) subtractMonths(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractMonths(date, 1) AS subtract_months_with_date, subtractMonths(date_time, 1) AS subtract_months_with_date_time
┌─subtract_months_with_date─┬─subtract_months_with_date_time─┐ │ 2018-12-01 │ 2018-12-01 00:00:00 │ └───────────────────────────┴────────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractQuarters(date, interval) subtractQuarters(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractQuarters(date, 1) AS subtract_quarters_with_date, subtractQuarters(date_time, 1) AS subtract_quarters_with_date_time
┌─subtract_quarters_with_date─┬─subtract_quarters_with_date_time─┐ │ 2018-10-01 │ 2018-10-01 00:00:00 │ └─────────────────────────────┴──────────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractSeconds(date, interval) subtractSeconds(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractSeconds(date, 1) AS subtract_seconds_with_date, subtractSeconds(date_time, 1) AS subtract_seconds_with_date_time
┌─subtract_seconds_with_date─┬─subtract_seconds_with_date_time─┐ │ 2012-10-02 │ 2018-12-31 23:59:59 │ └────────────────────────────┴─────────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractWeeks(date, interval) subtractWeeks(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractWeeks(date, 1) AS subtract_weeks_with_date, subtractWeeks(date_time, 1) AS subtract_weeks_with_date_time
┌─subtract_weeks_with_date─┬─subtract_weeks_with_date_time─┐ │ 2018-12-25 │ 2018-12-25 00:00:00 │ └──────────────────────────┴───────────────────────────────┘
Function subtract a Date/DateTime interval to a Date/DateTime and then return the Date/DateTime.
Syntax
subtractYears(date, interval) subtractYears(datetime, interval)
Arguments
date/datetime
- Date/DateTimeinterval
- Date/DateTimeReturned value
Example
WITH toDate('2019-01-01') AS date, toDateTime('2019-01-01 00:00:00') AS date_time SELECT subtractYears(date, 1) AS subtract_years_with_date, subtractYears(date_time, 1) AS subtract_years_with_date_time
┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐ │ 2018-01-01 │ 2018-01-01 00:00:00 │ └──────────────────────────┴───────────────────────────────┘
Rounds the time to the half hour.
This function is specific to Yandex.Metrica, since half an hour is the minimum amount of time for breaking a session into two sessions if a tracking tag shows a single user’s consecutive pageviews that differ in time by strictly more than this amount. This means that tuples (the tag ID, user ID, and time slot) can be used to search for pageviews that are included in the corresponding session.
Syntax
timeSlot(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT timeSlot(toDateTime('2018-01-01 01:01:01'))
┌─timeSlot(toDateTime('2018-01-01 01:01:01'))─┐ │ 2018-01-01 01:00:00 │ └─────────────────────────────────────────────┘
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: a constant UInt32, set to 1800 by default.
This is necessary for searching for pageviews in the corresponding session.
Syntax
timeSlots(StartTime, Duration,[, Size])
Arguments
StartTime
- DateTimeDuration
- Duration in seconds, UInt32Size
- Size of the interval.Returned value
Example
SELECT timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))
## toDayOfMonth┌─timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600))─┐ │ [2012-01-01 12:00:00, 2012-01-01 12:30:00] │ └─────────────────────────────────────────────────────────────┘
Converts a date or date with time to a UInt8 number containing the number of the day of the month (1-31).
Syntax
toDayOfMonth(date) toDayOfMonth(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toDayOfMonth(toDate('2021-08-12'))
┌─toDayOfMonth(toDate('2021-08-12'))─┐ │ 12 │ └────────────────────────────────────┘
SELECT toDayOfMonth(toDateTime('2021-08-12 00:00:00'))
┌─toDayOfMonth(toDateTime('2021-08-12 00:00:00'))─┐ │ 12 │ └─────────────────────────────────────────────────┘
Converts a date or date with time to a UInt8 number containing the number of the day of the week (Monday is 1, and Sunday is 7).
Syntax
toDayOfWeek(date) toDayOfWeek(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toDayOfWeek(toDate('2021-08-12'))
┌─toDayOfWeek(toDate('2021-08-12'))─┐ │ 4 │ └───────────────────────────────────┘
SELECT toDayOfWeek(toDateTime('2021-08-12 00:00:00'))
┌─toDayOfWeek(toDateTime('2021-08-12 00:00:00'))─┐ │ 4 │ └────────────────────────────────────────────────┘
Converts a date or date with time to a UInt16 number containing the number of the day of the year (1-366).
Syntax
toDayOfYear(date) toDayOfYear(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toDayOfWeek(toDate('2021-08-12'))
┌─toDayOfWeek(toDate('2021-08-12'))─┐ │ 4 │ └───────────────────────────────────┘
SELECT toDayOfWeek(toDateTime('2021-08-12 00:00:00'))
┌─toDayOfWeek(toDateTime('2021-08-12 00:00:00'))─┐ │ 4 │ └────────────────────────────────────────────────┘
Converts a date with time to a UInt8 number containing the number of the hour in 24-hour time (0-23).
This function assumes that if clocks are moved ahead, it is by one hour and occurs at 2 a.m., and if clocks are moved back, it is by one hour and occurs at 3 a.m. (which is not always true – even in Moscow the clocks were twice changed at a different time).
Syntax
toHour(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toHour(toDateTime('2021-08-12 00:00:00'))
┌─toHour(toDateTime('2021-08-12 00:00:00'))─┐ │ 0 │ └───────────────────────────────────────────┘
Converts a date or date with time to a UInt8 number containing the ISO Week number.
Syntax
toISOWeek(date) toISOWeek(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toISOWeek(toDate('2021-08-12'))
┌─toISOWeek(toDate('2021-08-12'))─┐ │ 32 │ └─────────────────────────────────┘
SELECT toISOWeek(toDateTime('2021-08-12 00:00:00'))
┌─toISOWeek(toDateTime('2021-08-12 00:00:00'))─┐ │ 32 │ └──────────────────────────────────────────────┘
Converts a date or date with time to a UInt16 number containing the ISO Year number.
Syntax
toISOYear(date) toISOYear(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toISOYear(toDate('2021-08-12'))
┌─toISOYear(toDate('2021-08-12'))─┐ │ 2021 │ └─────────────────────────────────┘
SELECT toISOYear(toDateTime('2021-08-12 00:00:00'))
┌─toISOYear(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021 │ └──────────────────────────────────────────────┘
Converts a date with time to a UInt8 number containing the number of the minute of the hour (0-59).
Syntax
toMinute(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toMinute(toDateTime('2021-08-12 00:00:00'))
┌─toMinute(toDateTime('2021-08-12 00:00:00'))─┐ │ 0 │ └─────────────────────────────────────────────┘
Rounds down a date or date with time to the nearest Monday.
Returns the date.
Syntax
toMonday(date) toMonday(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toMonday(toDate('2021-08-12'))
┌─toMonday(toDate('2021-08-12'))─┐ │ 2021-08-09 │ └────────────────────────────────┘
SELECT toMonday(toDateTime('2021-08-12 00:00:00'))
┌─toMonday(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021-08-09 │ └─────────────────────────────────────────────┘
Converts a date or date with time to a UInt8 number containing the month number (1-12).
Syntax
toMonth(date) toMonth(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toMonday(toDate('2021-08-12'))
┌─toMonday(toDate('2021-08-12'))─┐ │ 2021-08-09 │ └────────────────────────────────┘
SELECT toMonday(toDateTime('2021-08-12 00:00:00'))
┌─toMonday(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021-08-09 │ └─────────────────────────────────────────────┘
Converts a date or date with time to a UInt8 number containing the quarter number.
Syntax
toQuarter(date) toQuarter(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toQuarter(toDate('2021-08-12'))
┌─toQuarter(toDate('2021-08-12'))─┐ │ 3 │ └─────────────────────────────────┘
SELECT toQuarter(toDateTime('2021-08-12 00:00:00'))
┌─toQuarter(toDateTime('2021-08-12 00:00:00'))─┐ │ 3 │ └──────────────────────────────────────────────┘
Converts a date with time or date to the number of the day, starting from a certain fixed point in the past.
Syntax
toRelativeDayNum(date) toRelativeDayNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeDayNum(toDate('2021-08-12'))
┌─toRelativeDayNum(toDate('2021-08-12'))─┐ │ 18851 │ └────────────────────────────────────────┘
SELECT toRelativeDayNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeDayNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 18851 │ └─────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the hour, starting from a certain fixed point in the past.
Syntax
toRelativeHourNum(date) toRelativeHourNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeHourNum(toDate('2021-08-12'))
┌─toRelativeHourNum(toDate('2021-08-12'))─┐ │ 452416 │ └─────────────────────────────────────────┘
SELECT toRelativeHourNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeHourNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 452416 │ └──────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the minute, starting from a certain fixed point in the past.
Syntax
toRelativeMinuteNum(date) toRelativeMinuteNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeMinuteNum(toDate('2021-08-12'))
┌─toRelativeMinuteNum(toDate('2021-08-12'))─┐ │ 27144960 │ └───────────────────────────────────────────┘
SELECT toRelativeMinuteNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeMinuteNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 27144960 │ └────────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the month, starting from a certain fixed point in the past.
Syntax
toRelativeMonthNum(date) toRelativeMonthNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeMonthNum(toDate('2021-08-12'))
┌─toRelativeMonthNum(toDate('2021-08-12'))─┐ │ 24260 │ └──────────────────────────────────────────┘
SELECT toRelativeMonthNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeMonthNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 24260 │ └───────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the quarter, starting from a certain fixed point in the past.
Syntax
toRelativeQuarterNum(date) toRelativeQuarterNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeQuarterNum(toDate('2021-08-12'))
┌─toRelativeQuarterNum(toDate('2021-08-12'))─┐ │ 8086 │ └────────────────────────────────────────────┘
SELECT toRelativeQuarterNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeQuarterNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 8086 │ └─────────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the second, starting from a certain fixed point in the past.
Syntax
toRelativeSecondNum(date) toRelativeSecondNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeSecondNum(toDate('2021-08-12'))
┌─toRelativeSecondNum(toDate('2021-08-12'))─┐ │ 1628697600 │ └───────────────────────────────────────────┘
SELECT toRelativeSecondNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeSecondNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 1628697600 │ └────────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the week, starting from a certain fixed point in the past.
Syntax
toRelativeWeekNum(date) toRelativeWeekNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeWeekNum(toDate('2021-08-12'))
┌─toRelativeWeekNum(toDate('2021-08-12'))─┐ │ 2693 │ └─────────────────────────────────────────┘
SELECT toRelativeWeekNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeWeekNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 2693 │ └──────────────────────────────────────────────────────┘
Converts a date with time or date to the number of the year, starting from a certain fixed point in the past.
Syntax
toRelativeYearNum(date) toRelativeYearNum(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toRelativeYearNum(toDate('2021-08-12'))
┌─toRelativeYearNum(toDate('2021-08-12'))─┐ │ 2021 │ └─────────────────────────────────────────┘
SELECT toRelativeYearNum(toDateTime('2021-08-12 00:00:00'))
┌─toRelativeYearNum(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021 │ └──────────────────────────────────────────────────────┘
Converts a date with time to a UInt8 number containing the number of the second in the minute (0-59).
Leap seconds are not accounted for.
Syntax
toSecond(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toSecond(toDateTime('2021-08-12 00:00:00'))
┌─toSecond(toDateTime('2021-08-12 00:00:00'))─┐ │ 0 │ └─────────────────────────────────────────────┘
Rounds down a date with time to the start of the day.
Syntax
toStartOfDay(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfDay(toDateTime('2021-08-12 08:00:00'))
┌─toStartOfDay(toDateTime('2021-08-12 08:00:00'))─┐ │ 2021-08-12 00:00:00 │ └─────────────────────────────────────────────────┘
Rounds down the date with time to the start of the fifteen-minute interval.
Syntax
toStartOfFifteenMinutes(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfFifteenMinutes(toDateTime('2021-08-12 00:10:00'))
┌─toStartOfFifteenMinutes(toDateTime('2021-08-12 00:10:00'))─┐ │ 2021-08-12 00:00:00 │ └────────────────────────────────────────────────────────────┘
Rounds down a date with time to the start of the five-minute interval.
Syntax
toStartOfFiveMinute(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfFiveMinute(toDateTime('2021-08-12 00:09:00'))
┌─toStartOfFiveMinute(toDateTime('2021-08-12 00:09:00'))─┐ │ 2021-08-12 00:05:00 │ └────────────────────────────────────────────────────────┘
Rounds down a date with time to the start of the hour.
Syntax
toStartOfHour(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfHour(toDateTime('2021-08-12 01:09:00'))
┌─toStartOfHour(toDateTime('2021-08-12 01:09:00'))─┐ │ 2021-08-12 01:00:00 │ └──────────────────────────────────────────────────┘
Rounds down a date or date with time to the first day of ISO year.
Returns the date.
Syntax
toStartOfISOYear(date) toStartOfISOYear(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toStartOfISOYear(toDate('2021-08-12'))
┌─toStartOfISOYear(toDate('2021-08-12'))─┐ │ 2021-01-04 │ └────────────────────────────────────────┘
SELECT toStartOfISOYear(toDateTime('2021-08-12 00:00:00'))
┌─toStartOfISOYear(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021-01-04 │ └─────────────────────────────────────────────────────┘
This is a generalization of other functions named toStartOf*
. 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)
etc.
Syntax
toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])
Arguments
time_or_data
- Date/DateTimex unit
- x is UInt*, unit can be second, minute, hour, day, month, year, quarterReturned value
Example
SELECT toStartOfInterval(toDateTime('2021-08-12 01:09:00'), INTERVAL 2 minute)
┌─toStartOfInterval(toDateTime('2021-08-12 01:09:00'), toIntervalMinute(2))─┐ │ 2021-08-12 01:08:00 │ └───────────────────────────────────────────────────────────────────────────┘
Rounds down a date with time to the start of the minute.
Syntax
toStartOfMinute(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfMinute(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfMinute(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-08-12 00:01:00 │ └────────────────────────────────────────────────────┘
Rounds down a date or date with time to the first day of the month.
Returns the date.
Syntax
toStartOfMonth(date) toStartOfMonth(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toStartOfMonth(toDate('2021-08-12'))
┌─toStartOfMonth(toDate('2021-08-12'))─┐ │ 2021-08-01 │ └──────────────────────────────────────┘
SELECT toStartOfMonth(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfMonth(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-08-01 │ └───────────────────────────────────────────────────┘
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
toStartOfQuarter(date) toStartOfQuarter(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toStartOfQuarter(toDate('2021-08-12'))
┌─toStartOfQuarter(toDate('2021-08-12'))─┐ │ 2021-07-01 │ └────────────────────────────────────────┘
SELECT toStartOfQuarter(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfQuarter(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-07-01 │ └─────────────────────────────────────────────────────┘
Rounds down a date with time to the start of the ten-minute interval.
Syntax
toStartOfTenMinutes(datetime)
Arguments
datetime
- DateTimeReturned value
Example
SELECT toStartOfTenMinutes(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfTenMinutes(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-08-12 00:00:00 │ └────────────────────────────────────────────────────────┘
Rounds down a date or date with time to the nearest Sunday or Monday by mode.
Returns the date.
The mode argument works exactly like the mode argument to toWeek(). For the single-argument syntax, a mode value of 0 is used.
Syntax
toStartOfWeek(date\[,mode\]) toStartOfWeek(datetime\[,mode\])
Arguments
date/datetime
- Date/DateTimemode
- Refer toWeek mode.Returned value
Example
SELECT toStartOfWeek(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfWeek(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-08-08 │ └──────────────────────────────────────────────────┘
Rounds down a date or date with time to the first day of the year.
Returns the date.
Syntax
toStartOfYear(date) toStartOfYear(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toStartOfYear(toDateTime('2021-08-12 00:01:30'))
┌─toStartOfYear(toDateTime('2021-08-12 00:01:30'))─┐ │ 2021-01-01 │ └──────────────────────────────────────────────────┘
Converts a date with time to a certain fixed date, while preserving the time.
Syntax
toTime(date) toTime(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toTime(toDateTime('2021-08-12 00:01:30'))
┌─toTypeName(toTime(toDateTime('2021-08-12 00:01:30')))─┐ │ DateTime('PRC') │ └───────────────────────────────────────────────────────┘
Converts time or date and time to the specified time zone. The time zone is an attribute of the Date
and DateTime
data types. The internal value (number of seconds) of the table field or of the resultset's column does not change, the column's type changes and its string representation changes accordingly.
Syntax
toTimezone(value, timezone)
Arguments
value
— Time or date and time. Date/DateTime.timezone
— Timezone for the returned value. String.Returned value
Example
SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc, toTypeName(time_utc) AS type_utc, toInt32(time_utc) AS int32utc, toTimeZone(time_utc, 'Asia/Yekaterinburg') AS time_yekat, toTypeName(time_yekat) AS type_yekat, toInt32(time_yekat) AS int32yekat, toTimeZone(time_utc, 'US/Samoa') AS time_samoa, toTypeName(time_samoa) AS type_samoa, toInt32(time_samoa) AS int32samoa FORMAT Vertical;
Row 1: ────── time_utc: 2019-01-01 00:00:00 type_utc: DateTime('UTC') int32utc: 1546300800 time_yekat: 2019-01-01 05:00:00 type_yekat: DateTime('Asia/Yekaterinburg') int32yekat: 1546300800 time_samoa: 2018-12-31 13:00:00 type_samoa: DateTime('US/Samoa') int32samoa: 1546300800
toTimeZone(time_utc, 'Asia/Yekaterinburg')
changes the DateTime('UTC')
type to DateTime('Asia/Yekaterinburg')
. The value (Unixtimestamp) 1546300800 stays the same, but the string representation (the result of the toString() function) changes from time_utc: 2019-01-01 00:00:00
to time_yekat: 2019-01-01 05:00:00
.
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.
Syntax
toWeek(date, [, mode][, Timezone])
Arguments
date
– 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.Returned value
Example
SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9;
┌─date───────┬─week0─┬─week1─┬─week9─┐ │ 2016-12-27 │ 52 │ 52 │ 1 │ └────────────┴───────┴───────┴───────┘
Converts a date or date with time to a UInt32 number containing the year and month number (YYYY * 100 + MM).
Syntax
toYYYYMM(date) toYYYYMM(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toYYYYMM(toDate('2021-08-12'))
┌─toYYYYMM(toDate('2021-08-12'))─┐ │ 202108 │ └────────────────────────────────┘
SELECT toYYYYMM(toDateTime('2021-08-12 00:00:00'))
┌─toYYYYMM(toDateTime('2021-08-12 00:00:00'))─┐ │ 202108 │ └─────────────────────────────────────────────┘
Converts a date or date with time to a UInt32 number containing the year and month, and day (YYYY * 10000 + MM * 100 + DD).
Syntax
toYYYYMMDD(date) toYYYYMMDD(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toYYYYMMDD(toDate('2021-08-12'))
┌─toYYYYMMDD(toDate('2021-08-12'))─┐ │ 20210812 │ └──────────────────────────────────┘
SELECT toYYYYMMDD(toDateTime('2021-08-12 00:00:00'))
┌─toYYYYMMDD(toDateTime('2021-08-12 00:00:00'))─┐ │ 20210812 │ └───────────────────────────────────────────────┘
Converts a date or date with time to a UInt64 number containing the year, month, day, hour,minutes and seconds (YYYY * 10000000000 + MM * 100000000 + DD * 1000000 + hh * 10000 + mm * 100 + ss).
Syntax
toYYYYMMDDhhmmss(date) toYYYYMMDDhhmmss(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toYYYYMMDDhhmmss(toDate('2021-08-12'))
┌─toYYYYMMDDhhmmss(toDate('2021-08-12'))─┐ │ 20210812000000 │ └────────────────────────────────────────┘
SELECT toYYYYMMDDhhmmss(toDateTime('2021-08-12 00:00:00'))
┌─toYYYYMMDDhhmmss(toDateTime('2021-08-12 00:00:00'))─┐ │ 20210812000000 │ └─────────────────────────────────────────────────────┘
Converts a date or date with time to a UInt16 number containing the year number (AD).
Syntax
toYear(date) toYear(datetime)
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toYear(toDate('2021-08-12'))
┌─toYear(toDate('2021-08-12'))─┐ │ 2021 │ └──────────────────────────────┘
SELECT toYear(toDateTime('2021-08-12 00:00:00'))
┌─toYear(toDateTime('2021-08-12 00:00:00'))─┐ │ 2021 │ └───────────────────────────────────────────┘
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 exactly 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)
.
Syntax
toYearWeek(date[,mode]) toYearWeek(datetime[,mode])
Arguments
date/datetime
- Date/DateTimemode
- Refer toWeek mode.Returned value
Example
SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9;
┌─date───────┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┐ │ 2016-12-27 │ 201652 │ 201652 │ 201701 │ └────────────┴───────────┴───────────┴───────────┘
Accepts zero arguments and returns the current date at one of the moments of request execution.
The same as ‘toDate(now())’.
Syntax
today()
Returned value
Example
SELECT today()
Alias to ToWeek
Alias to toYear
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 exactly 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)
.
Syntax
toYearWeek(date[,mode])
Arguments
date/datetime
- Date/DateTimeReturned value
Example
SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9;
┌─date───────┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┐ │ 2016-12-27 │ 201652 │ 201652 │ 201701 │ └────────────┴───────────┴───────────┴───────────┘
Accepts zero arguments and returns yesterday’s date at one of the moments of request execution.
The same as ‘today() - 1’.
Syntax
yesterday()
Returned value
Example
SELECT yesterday()