Clickhouse SQL Date Processing Functions and Case Studies
Applicable Scenarios
High-performance datasets, and Clickhouse direct connection datasets.
Notes
-
All functions involving hours, minutes, and seconds (DateTime fields) may require timezone parameter timezone. The timezone format is generally UTC timezone or geographic IANA identifiers (e.g., Europe/Moscow). If timezone is not specified, it defaults to the server's timezone (e.g., UTC world standard time).
-
Some functions are case-sensitive: Functions with mixed case are Clickhouse-specific functions and must be used as-is (e.g., toYear); Functions that are all uppercase or lowercase are compatible with other databases and are case-insensitive, meaning both uppercase and lowercase work (e.g., YEAR).
-
The following functions are not all available functions, but are commonly used functions compiled based on the v22 version currently used by BI. For more functions, please refer to the Clickhouse official website Dates and Times.
Date Support Functions
Time or Date Extraction Functions
A) Original field is date or datetime/timestamp, returns non-date.
Purpose | Function | Example | Result |
Extract year | toYear() / toISOYear() /YEAR() | toYear('2018-12-11 11:12:13') | 2018 |
Extract quarter number | toQuarter() / QUARTER() | toQuarter('2018-12-11 11:12:13') | 4 |
Extract month | toMonth() / MONTH() | toMonth('2018-12-11 11:12:13') | 12 |
monthName(date) | monthName('2018-12-11 11:12:13') | December | |
Extract day of month (1-31) | toDayOfMonth() / DAYOFMONTH() / DAY() | toDayOfMonth('2019-12-03') | 3 |
Extract day of year (1-365) | toDayOfYear() / DAYOFYEAR() | toDayOfYear('2019-12-03') | 337 |
Extract weekday | toDayOfWeek()/DAYOFWEEK() Monday is 1, Sunday is 7 | toDayOfWeek('2019-12-03') | 2 |
Extract hour | toHour() / HOUR() | toHour('2018-12-11 11:12:13') | 11 |
Extract minute | toMinute() / MINUTE() | toMinute('2018-12-11 11:12:13') | 12 |
Extract second | toSecond() / SECOND() | toSecond('2018-12-11 11:12:13') | 13 |
Extract ISO week number (Monday start, first week must contain >3 days of current year) | toISOWeek() / toWeek(date,3) | toISOWeek('2019-12-03') | 49 |
Extract time | formatDateTime(Time, Format) | formatDateTime('2018-12-11 11:12:13','%T') | 11:12:13 |
Extract specific part from date time | dateName(date_part,date) | dateName('weekday','2018-12-11 11:12:13') | Tuesday |
B) Original field is date or datetime/timestamp, returns date or time.
Purpose | Function | Example | Result |
Get Monday of current week | toMonday() | toMonday('2019-12-03 09:00:00') | 2019-12-02 |
Get first day of current week [,mode] defaults to 0 | toStartOfWeek(t[,mode]) | toStartOfWeek('2019-12-03 09:00:00',3) | 2019-12-02 |
Get first day of current month | toStartOfMonth() | toStartOfMonth('2019-12-03 09:00:00') | 2019-12-01 |
Get first day of current quarter | toStartOfQuarter() | toStartOfQuarter('2019-12-03 09:00:00') | 2019-10-01 |
Get first day of 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 date time to day (zero out after) | toStartOfDay() | toStartOfDay('2019-12-03 09:00:00') | 2019-12-03 00:00:00 |
Truncate date time to hour (zero out after) | toStartOfHour(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:00:00 |
Truncate date time to minute (zero out after) | toStartOfMinute(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:51:00 |
Round DateTime forward to nearest time point in 5-minute units | toStartOfFiveMinute(value[, timezone]) | toStartOfFiveMinute('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
Round DateTime forward to nearest time point in 10-minute units | toStartOfTenMinutes(value[, timezone]) | toStartOfTenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
Round DateTime forward to nearest time point in 15-minute units | toStartOfFifteenMinutes(value[, timezone]) | toStartOfFifteenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:45:00 |
Round DateTime forward to nearest time point in custom units | 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 forward to half hour | timeSlot() | timeSlot('2021-12-02 16:39:09','Asia/Shanghai') | 2021-12-02 16:30:00 |
Truncate date time to specific part (zero out after), 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 Date Time Generation Functions
| Purpose | Function | Result |
| Generate current date time, can specify timezone | 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 current timestamp | toUnixTimestamp(now()) | 1638388800 |
Date and Time Calculations
Purpose | Function | Example | Result |
Add/subtract date time | +/- interval n unit' (middle value n cannot reference other fields) | '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 | ||
Direct addition/subtraction of numbers, date adds/subtracts by days; datetime adds/subtracts by 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 — positive integer, duration, can reference other int fields | '2021-07-30'+toIntervalDay(7) | 2021-08-06 | |
'2021-07-30 15:48:08' - toIntervalHour(7) | 2021-07-30 08:48:08 | ||
Calculate 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 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 date time difference --returns integer 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 Date Time Conversion
Purpose | Function | Example | Result |
Convert string date to 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 string date or timestamp to date time 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 types and other formats to string type | 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 date time to timestamp | toUnixTimestamp(time[, timezone]) | toUnixTimestamp('2019-12-03 09:00:00') | 1575334800 |
Date time format conversion, result is generally 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 |
Date time format conversion, result is numeric type | 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 | |
Timezone offset conversion | toTimeZone(Time, Timezone) | toTimeZone('2021-12-02 16:00:09','US/Samoa') | 2021-12-01 21:00:09 |
Convert String type date time to DateTime type | parseDateTimeBestEffort() | Case reference figure below |
Cases
Case One: Convert Text Type Date 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 Two: Standard Date to Text Type Cases
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 and above: concat(left(dateName('month',[Date]) ),3),' ', toString(Day([Date])),',',toString(toYear([Date]))) | ||
Sunday | dateName('weekday',[Date]) | ||
Appendix
-
formatDateTime Function Supported Format Modifiers
The "Example" column shows the formatting result for 2018-01-02 22:33:44:
| Symbol | Meaning | Example |
| %C | Year divided by 100 and truncated to integer (00-99) | 20 |
| %d | Day of month, zero-padded (01-31) | 2 |
| %D | Short MM/DD/YY date, equivalent to %m/%d/%y | 01/02/2018 |
| %e | Day of month, space-padded ( 1-31) | 2 |
| %F | Short YYYY-MM-DD date, equivalent to %Y-%m-%d | 2018/1/2 |
| %G | Four-digit year format for ISO week number, calculated from week-based year defined by ISO 8601 standard, usually only useful for %V | 2018 |
| %g | Two-digit year format, consistent with ISO 8601, abbreviation of four-digit notation | 18 |
| %H | 24-hour format (00-23) | 22 |
| %I | 12-hour format (01-12) | 10 |
| %j | Day of year (001-366) | 2 |
| %m | Month as decimal number (01-12) | 1 |
| %M | Minute (00-59) | 33 |
| %n | Newline character (") | |
| %p | AM or PM designation | 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 number, Monday is 1 (1-7) | 2 |
| %V | ISO8601 week number (01-53) | 1 |
| %w | Weekday as decimal number, Sunday is 0 (0-6) | 2 |
| %y | Year, last two digits (00-99) | 18 |
| %Y | Year | 2018 |
| %% | % symbol | % |
-
Week number calculation requires mode parameter.
This parameter can specify whether the week starts from Sunday or Monday, and whether the return value should be in the range 0 to 53 or 1 to 53. Value range [0,9], if the mode parameter is omitted, the default mode is 0. Functions that use the mode parameter are toWeek(date[,mode]), toYearWeek(date[,mode]), toStartOfWeek(t[,mode]). toISOWeek() is a compatibility function, equivalent to toWeek(date,3).
The table below describes how the mode parameter 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," week numbering follows ISO 8601:1988:
-
If the week containing January 1st 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 like "contains January 1", the week containing January 1st is week 1 of the current year.