ClickHouse SQL Date Processing Functions and Case Sharing
Applicable Scenarios
High-performance datasets and ClickHouse direct connection datasets.
Notes
- All functions involving hours, minutes, and seconds (DateTime fields) may require the
timezoneparameter. The time zone format is usually a UTC time zone or a geographic IANA identifier, such asEurope/Moscow. If no time zone is specified, conversion defaults to the server time zone, such as Coordinated Universal Time (UTC). - Some functions are case-sensitive. Mixed-case functions are ClickHouse-specific functions and must be used exactly as written, such as
toYear. Functions written entirely in uppercase or lowercase are compatible with other databases and are case-insensitive, such asYEAR. - The following list does not include all available functions. It contains common functions compiled based on the BI v22 version currently in use. For more functions, see the ClickHouse official documentation: Dates and Times.
Supported Date Functions
Time or Date Extraction Functions
A) The source field is a date or datetime/timestamp, and the return value is not a date.
Purpose | Function | Example | Result |
Extract the year | toYear() / toISOYear() /YEAR() | toYear('2018-12-11 11:12:13') | 2018 |
Extract the quarter number | toQuarter() / QUARTER() | toQuarter('2018-12-11 11:12:13') | 4 |
Extract the month | toMonth() / MONTH() | toMonth('2018-12-11 11:12:13') | 12 |
monthName(date) | monthName('2018-12-11 11:12:13') | December | |
Extract the day of the month (1-31) | toDayOfMonth() / DAYOFMONTH() / DAY() | toDayOfMonth('2019-12-03') | 3 |
Extract the day of the year (1-365) | toDayOfYear() / DAYOFYEAR() | toDayOfYear('2019-12-03') | 337 |
Extract the day of the week | toDayOfWeek()/DAYOFWEEK(); Monday is 1 and Sunday is 7 | toDayOfWeek('2019-12-03') | 2 |
Extract the hour | toHour() / HOUR() | toHour('2018-12-11 11:12:13') | 11 |
Extract the minute | toMinute() / MINUTE() | toMinute('2018-12-11 11:12:13') | 12 |
Extract the second | toSecond() / SECOND() | toSecond('2018-12-11 11:12:13') | 13 |
Extract the ISO week number (starts on Monday; week 1 must contain more than 3 days of the current year) | toISOWeek() / toWeek(date,3) | toISOWeek('2019-12-03') | 49 |
Extract hours, minutes, and seconds | formatDateTime(Time, Format) | formatDateTime('2018-12-11 11:12:13','%T') | 11:12:13 |
Extract a specific part of a datetime | dateName(date_part,date) | dateName('weekday','2018-12-11 11:12:13') | Tuesday |
B) The source field is a date or datetime/timestamp, and the return value is a date or time.
Purpose | Function | Example | Result |
Get the Monday of the current week | toMonday() | toMonday('2019-12-03 09:00:00') | 2019-12-02 |
Get the first day of the current week. [,mode] defaults to 0. | toStartOfWeek(t[,mode]) | toStartOfWeek('2019-12-03 09:00:00',3) | 2019-12-02 |
Get the first day of the current month | toStartOfMonth() | toStartOfMonth('2019-12-03 09:00:00') | 2019-12-01 |
Get the first day of the current quarter | toStartOfQuarter() | toStartOfQuarter('2019-12-03 09:00:00') | 2019-10-01 |
Get the first day of the current year | toStartOfYear() | toStartOfYear('2019-12-03 09:00:00') | 2019-01-01 |
toStartOfISOYear() | toStartOfISOYear('2019-12-03 09:00:00') | 2018-12-31 | |
Truncate the datetime to the day; later parts are reset to zero | toStartOfDay() | toStartOfDay('2019-12-03 09:00:00') | 2019-12-03 00:00:00 |
Truncate the datetime to the hour; later parts are reset to zero | toStartOfHour(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:00:00 |
Truncate the datetime to the minute; later parts are reset to zero | toStartOfMinute(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:51:00 |
Round a DateTime down to the nearest five-minute interval | toStartOfFiveMinute(value[, timezone]) | toStartOfFiveMinute('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
Round a DateTime down to the nearest ten-minute interval | toStartOfTenMinutes(value[, timezone]) | toStartOfTenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
Round a DateTime down to the nearest fifteen-minute interval | toStartOfFifteenMinutes(value[, timezone]) | toStartOfFifteenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:45:00 |
Round a DateTime down to the nearest custom interval | toStartOfInterval(time_or_data, interval x unit[,time_zone]) | toStartOfInterval('2021-11-30 13:51:35',INTERVAL 20 minute,'Asia/Shanghai') | 2021-11-30 13:40:00 |
Round time down to the half hour | timeSlot() | timeSlot('2021-12-02 16:39:09','Asia/Shanghai') | 2021-12-02 16:30:00 |
Truncate a datetime to a specific part; later parts are reset to zero. Returns Date/DateTime. | date_trunc(unit,value[, timezone] / dateTrunc(unit,value[, timezone] | date_trunc('hour', '2021-12-02 16:39:09')dateTrunc('week', '2021-12-02 16:39:09') | 2021-12-02 16:00:00 |
Date or Datetime Generation Functions
| Purpose | Function | Result |
| Generate the current datetime; a time zone can be specified | now() | 2021-12-01 20:00:00 |
| Generate today's date | today() | 2021-12-01 |
| Generate yesterday's date | yesterday() / today() - 1 | 2021-11-30 |
| Generate the current timestamp | toUnixTimestamp(now()) | 1638388800 |
Date and Time Calculation
Purpose | Function | Example | Result |
Add or subtract date and time values | +/- interval n unit' (the middle numeric value n cannot reference another field) | '2021-07-30 15:48:08' - interval 1 year | 2020-07-30 15:48:08 |
'2021-07-30 15:48:08' + interval 2 hour | 2021-07-30 17:48:08 | ||
Add or subtract numbers directly. For date values, the number is treated as days; for datetime values, it is treated as seconds. | '2021-07-30 15:48:08' - 10 | 2021-07-30 15:47:52 | |
'2021-07-30' - 10 | 2021-07-20 | ||
toInterval(Year|Quarter|Month|Week|Day|Hour|Minute|Second) (number). number is a positive integer duration and can reference another int field. | '2021-07-30'+toIntervalDay(7) | 2021-08-06 | |
'2021-07-30 15:48:08' - toIntervalHour(7) | 2021-07-30 08:48:08 | ||
Calculate a future date | addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters | addHours('2019-12-03 09:00:00', 1, 'Asia/Shanghai') | 2019-12-03 10:00:00 |
addWeeks('2019-12-03',1) | 2019-12-10 | ||
Calculate a past date | subtractYears, subtractMonths, subtractWeeks, subtractDays, subtractours, subtractMinutes, subtractSeconds, subtractQuarters | subtractQuarters('2021-12-03',1) | 2021-09-03 |
subtractDays('2019-12-03 09:00:00',3,'Asia/Shanghai') | 2019-11-30 09:00:00 | ||
Calculate the date/time difference and return an int | dateDiff('unit', startdate, enddate, [timezone]) | dateDiff('month', '2020-12-02', '2021-11-30') | 11 |
dateDiff('hour','2021-11-30 08:00:00', '2021-11-30 17:36:08','Asia/Shanghai') | 9 |
Date and Datetime Conversion
Purpose | Function | Example | Result |
Convert a string date to a date type | toDate() | toDate('2009-07-30 04:17:52') | 2009-07-30 |
cast(str,'date')/cast(str as date) | cast('2009-07-30 04:17:52','date') | ||
Convert a string date or timestamp to a datetime type | toDateTime() | toDateTime('2022-01-01 13:00:00','Asia/Shanghai') | 2022-01-01 13:00:00 |
cast(str,'datetime')/cast(str as datetime) | cast(today() as datetime) | 2021-11-30 08:00:00 | |
Convert numeric, date, and other formats to strings | toString() | toString('2021-07-30 15:48:08') | 2021-07-30 15:48:08 |
cast(time,'String') /cast(time as String) | cast('2021-07-30 15:48:08' as String) | ||
Convert a datetime to a timestamp | toUnixTimestamp(time[, timezone]) | toUnixTimestamp('2019-12-03 09:00:00') | 1575334800 |
Convert a datetime format. The result is usually a string or numeric type. | formatDateTime(Time, Format \ [,Timezone\]) | formatDateTime('2021-12-02 15:48:52', '%Y/%m/%d %I:%M','Asia/Shanghai') | 2021/12/02 03:48 |
Convert a datetime format. The result is numeric. | toYYYYMM() | toYYYYMM('2021-12-02') | 202112 |
toYYYYMMDD() | toYYYYMMDD('2021-12-02') | 20211202 | |
toYYYYMMDDhhmmss() | toYYYYMMDDhhmmss('2021-12-02 16:00:09','Asia/Shanghai') | 20211202160009 | |
toYearWeek(date[,mode]) | toYearWeek('2019-12-03') | 201949 | |
Time zone offset conversion | toTimeZone(Time, Timezone) | toTimeZone('2021-12-02 16:00:09','US/Samoa') | 2021-12-01 21:00:09 |
Convert a String datetime to the DateTime type | parseDateTimeBestEffort() | See the following case |
Cases
Case 1: Convert Text Date Values to Standard Date Format
Text Date | Standard Format | Function | |
20210808121600 | 2021-08-08 12:16:00 | parseDateTimeBestEffort([Text Date],'Asia/Shanghai') | |
2021/07/30 13:30:00 | 2021-07-30 13:30:00 | ||
30/7/2021 01:30 PM | 2021-07-30 13:30:00 | ||
2021-07-30T16:00:00.000Z | 2021-07-31 00:00:00 | parseDateTimeBestEffort([Text Date]) | |
2021-07-30T17:25:53+00:00 | 2021-07-31 17:25:53 | parseDateTimeBestEffort([Text Date],'UTC') | |
Sat, 18 Aug 2018 07:22:16 GMT | 2018-08-18 07:22:16 | ||
July 30, 2021 | 2021-07-30 | toDate(parseDateTimeBestEffort(replaceOne([Text Date],',',''))) | |
Aug 8, 2021 | 2021-08-08 | ||
30/07/2021 | 2021-07-30 | toDate(parseDateTimeBestEffort([Text Date])) | |
07/30/2021 | 2021-07-30 | toDate(replaceRegexpOne([Text Date],'(\\d{2})/(\\d{2})/(\\d{4})','\\3-\\1-\\2')) | |
2021年7月30日 | 2021-07-30 | toDate(replaceRegexpOne([Text Date],'(\\d{4})年(\\d{1,2})月(\\d{1,2})日','\\1-\\2-\\3')) | |
Case 2: Convert Standard Dates to Text Values
Date | Target Format (Text) | Function | |
2021-08-08 15:16:00 | 2021-08 | formatDateTime([Date],'%Y-%m') | |
202108 (numeric) | toYYYYMM([Date]) | ||
08/08 | formatDateTime([Date],'%m-%d') | ||
15:16:00 | formatDateTime([Date],'%R','Asia/Shanghai') | ||
03:16 PM | formatDateTime([Date],'%I:%M %p','Asia/Shanghai') | ||
2021年8月8日 | formatDateTime([Date],'%Y年%m月%d日') | ||
2021-08-08 15:16:00 (+08:00) | concat([Date],' (+08:00)') | ||
Aug 8, 2021 | v21.7 or later: concat(left(dateName('month',[Date]) ),3),' ', toString(Day([Date])),',',toString(toYear([Date]))) | ||
Sunday | dateName('weekday',[Date]) | ||
Appendix
-
Format Modifiers Supported by the formatDateTime Function
The "Example" column shows the formatted result for 2018-01-02 22:33:44:
| Symbol | Meaning | Example |
| %C | Year divided by 100 and truncated to an integer (00-99) | 20 |
| %d | Day of the month, zero-padded (01-31) | 2 |
| %D | Short MM/DD/YY date, equivalent to %m/%d/%y | 01/02/2018 |
| %e | Day of the month, space-padded ( 1-31) | 2 |
| %F | Short YYYY-MM-DD date, equivalent to %Y-%m-%d | 2018/1/2 |
| %G | Four-digit ISO week-numbering year, calculated from the week-based year defined by ISO 8601; usually useful only with %V | 2018 |
| %g | Two-digit year format aligned with ISO 8601; abbreviated four-digit notation | 18 |
| %H | 24-hour format (00-23) | 22 |
| %I | 12-hour format (01-12) | 10 |
| %j | Day of the year (001-366) | 2 |
| %m | Month as a decimal number (01-12) | 1 |
| %M | Minute (00-59) | 33 |
| %n | Newline character (") | |
| %p | AM or PM marker | PM |
| %Q | Quarter (1-4) | 1 |
| %R | 24-hour HH:MM time, 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 a number, Monday is 1 (1-7) | 2 |
| %V | ISO8601 week number (01-53) | 1 |
| %w | Weekday as a decimal number, Sunday is 0 (0-6) | 2 |
| %y | Year, last two digits (00-99) | 18 |
| %Y | Year | 2018 |
| %% | Percent sign | % |
-
Week number calculation requires the
modeparameter.This parameter specifies whether the week starts on Sunday or Monday and whether return values should be in the range 0 to 53 or 1 to 53. The valid range is [0,9]. If the
modeparameter is omitted, the default mode is 0. Functions that use themodeparameter includetoWeek(date[,mode]),toYearWeek(date[,mode]), andtoStartOfWeek(t[,mode]).toISOWeek()is a compatibility function equivalent totoWeek(date,3).The following table describes how the
modeparameter 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 like "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
modevalues like "contains January 1," the week containing January 1 is week 1 of the current year.