Clickhouse SQL日期处理函数及案例分享
适用场景
高性能数据集，以及Clickhouse直连数据集。
注意事项
-
所有涉及到时分秒（DateTime字段）的函数都可能需要时区参数 timezone，时区格式一般为UTC时区或地理位置的IANA标识符（例如 Europe/Moscow），未指定时区则默认转换为服务器的时区（例如世界标准时间UTC）。
-
部分函数大小写敏感：大小写混写的函数为Clickhouse专有函数，必须原样使用（例如 toYear）；全部为大写或小写的函数为兼容其他数据库的函数，大小写不敏感，即大小写都行（例如 YEAR）。
-
以下函数非全部可用函数，是基于BI当前所用v22版本汇编而成的常用函数。更多函数请参考Clickhouse官方网站Dates and Times。
日期支持函数
时间或日期截取函数
A) 原字段为日期(date)或日期时间(datetime/timestamp), 返回非日期。
用途 | 函数 | 举例 | 结果 |
取年份 | toYear() / toISOYear() /YEAR() | toYear('2018-12-11 11:12:13') | 2018 |
取季度数 | toQuarter() / QUARTER() | toQuarter('2018-12-11 11:12:13') | 4 |
取月份 | toMonth() / MONTH() | toMonth('2018-12-11 11:12:13') | 12 |
monthName(date) | monthName('2018-12-11 11:12:13') | December | |
取当月内的天数（1-31） | toDayOfMonth() / DAYOFMONTH() / DAY() | toDayOfMonth('2019-12-03') | 3 |
取当年内的天数（1-365） | toDayOfYear() / DAYOFYEAR() | toDayOfYear('2019-12-03') | 337 |
取星期 | toDayOfWeek()/DAYOFWEEK() 周一是1, 周日是7 | toDayOfWeek('2019-12-03') | 2 |
取小时 | toHour() / HOUR() | toHour('2018-12-11 11:12:13') | 11 |
取分钟 | toMinute() / MINUTE() | toMinute('2018-12-11 11:12:13') | 12 |
取秒 | toSecond() / SECOND() | toSecond('2018-12-11 11:12:13') | 13 |
取ISO周数 (周一起始,第一周需包含当年天数>3) | toISOWeek() / toWeek(date,3) | toISOWeek('2019-12-03') | 49 |
取时分秒 | formatDateTime(Time, Format) | formatDateTime('2018-12-11 11:12:13','%T') | 11:12:13 |
取日期时间里的特定部分 | dateName(date_part,date) | dateName('weekday','2018-12-11 11:12:13') | Tuesday |
B) 原字段为日期(date)或日期时间(datetime/timestamp), 返回日期或时间。
用途 | 函数 | 举例 | 结果 |
取所在周的周一 | toMonday() | toMonday('2019-12-03 09:00:00') | 2019-12-02 |
取所在周的第一天 [,mode]缺省默认为0 | toStartOfWeek(t[,mode]) | toStartOfWeek('2019-12-03 09:00:00',3) | 2019-12-02 |
取所在月第一天 | toStartOfMonth() | toStartOfMonth('2019-12-03 09:00:00') | 2019-12-01 |
取所在季第一天 | toStartOfQuarter() | toStartOfQuarter('2019-12-03 09:00:00') | 2019-10-01 |
取所在年第一天 | toStartOfYear() | toStartOfYear('2019-12-03 09:00:00') | 2019-01-01 |
toStartOfISOYear() | toStartOfISOYear('2019-12-03 09:00:00') | 2018-12-31 | |
截取时间日期到天(之后归零) | toStartOfDay() | toStartOfDay('2019-12-03 09:00:00') | 2019-12-03 00:00:00 |
截取时间日期到小时(之后归零) | toStartOfHour(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:00:00 |
截取时间日期到分钟(之后归零) | toStartOfMinute(value[, timezone]) | toStartOfHour('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:51:00 |
将DateTime以五分钟为单位向前取整到最接近的时间点 | toStartOfFiveMinute(value[, timezone]) | toStartOfFiveMinute('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
将DateTime以十分钟为单位向前取整到最接近的时间点 | toStartOfTenMinutes(value[, timezone]) | toStartOfTenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:50:00 |
将DateTime以十五分钟为单位向前取整到最接近的时间点 | toStartOfFifteenMinutes(value[, timezone]) | toStartOfFifteenMinutes('2021-11-30 13:51:35','Asia/Shanghai') | 2021-11-30 13:45:00 |
将DateTime以自定义单位向前取整到最接近的时间点 | toStartOfInterval(time_or_data,间隔x单位[,time_zone]) | toStartOfInterval('2021-11-30 13:51:35',INTERVAL 20 minute,'Asia/Shanghai') | 2021-11-30 13:40:00 |
将时间向前取整半小时 | timeSlot() | timeSlot('2021-12-02 16:39:09','Asia/Shanghai') | 2021-12-02 16:30:00 |
截取时间日期到特定部分(之后归零), 返回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 |
日期或时间日期生成函数
| 用途 | 函数 | 结果 |
| 生成当前时间日期,可指定时区 | now() | 2021-12-01 20:00:00 |
| 生成今天的日期 | today() | 2021-12-01 |
| 生成昨天的日期 | yesterday() / today() - 1 | 2021-11-30 |
| 生成当前时间戳 | toUnixTimestamp(now()) | 1638388800 |
日期与时间计算
用途 | 函数 | 举例 | 结果 |
增减日期时间 | +/- interval n unit' (中间数值n不能引用其他字段) | '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 | ||
直接加减数字, date按照天数加减; datetime按照秒数加减 | '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 — 正整数，持续的时间, 可引用其他int字段 | '2021-07-30'+toIntervalDay(7) | 2021-08-06 | |
'2021-07-30 15:48:08' - toIntervalHour(7) | 2021-07-30 08:48:08 | ||
计算未来日期 | 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 | ||
计算过去日期 | 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 | ||
计算日期时间差 --返回整数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 |
日期与时间日期转化
用途 | 函数 | 举例 | 结果 |
将字符型日期转化为日期型 | 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') | ||
将字符型日期或者时间戳转化为时间日期型 | 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 | |
将数值型、日期型等格式转化为字符型 | 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) | ||
日期时间转换为时间戳 | toUnixTimestamp(time[, timezone]) | toUnixTimestamp('2019-12-03 09:00:00') | 1575334800 |
日期时间格式转化，结果一般为字符型或数值型 | formatDateTime(Time, Format \ [,Timezone\]) | formatDateTime('2021-12-02 15:48:52', '%Y/%m/%d %I:%M','Asia/Shanghai') | 2021/12/02 03:48 |
日期时间格式转化，结果为数值型 | 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 | |
时区偏移转换 | toTimeZone(Time, Timezone) | toTimeZone('2021-12-02 16:00:09','US/Samoa') | 2021-12-01 21:00:09 |
把String类型的时间日期转换为DateTime类型 | parseDateTimeBestEffort() | 案例参考下图 |
案例
案例一：文本类型日期转换为标准日期格式
文本日期 | 标准格式 | 函数 | |
20210808121600 | 2021-08-08 12:16:00 | parseDateTimeBestEffort([文本日期],'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([文本日期]) | |
2021-07-30T17:25:53+00:00 | 2021-07-31 17:25:53 | parseDateTimeBestEffort([文本日期],'UTC') | |
Sat, 18 Aug 2018 07:22:16 GMT | 2018-08-18 07:22:16 | ||
July 30, 2021 | 2021-07-30 | toDate(parseDateTimeBestEffort(replaceOne([文本日期],',',''))) | |
Aug 8, 2021 | 2021-08-08 | ||
30/07/2021 | 2021-07-30 | toDate(parseDateTimeBestEffort([文本日期])) | |
07/30/2021 | 2021-07-30 | toDate(replaceRegexpOne([文本日期],'(\\d{2})/(\\d{2})/(\\d{4})','\\3-\\1-\\2')) | |
2021年7月30日 | 2021-07-30 | toDate(replaceRegexpOne([文本日期],'(\\d{4})年(\\d{1,2})月(\\d{1,2})日','\\1-\\2-\\3')) | |
案例二：标准日期转换为文本类型案例
日期 | 目标格式（文本） | 函数 | |
2021-08-08 15:16:00 | 2021-08 | formatDateTime([日期],'%Y-%m') | |
202108 （数值） | toYYYYMM([日期]) | ||
08/08 | formatDateTime([日期],'%m-%d') | ||
15:16:00 | formatDateTime([日期],'%R','Asia/Shanghai') | ||
03:16 PM | formatDateTime([日期],'%I:%M %p','Asia/Shanghai') | ||
2021年8月8日 | formatDateTime([日期],'%Y年%m月%d日') | ||
2021-08-08 15:16:00 (+08:00) | concat([日期],' (+08:00)') | ||
Aug 8, 2021 | 不低于v21.7版本：concat(left(dateName('month',[日期]) ),3),' ', toString(Day([日期])),',',toString(toYear([日期]))) | ||
Sunday | dateName('weekday',[日期]) | ||
附录：
-
formatDateTime 函数支持的格式修饰符
“举例”列是对2018-01-02 22:33:44的格式化结果：
| 符号 | 含义 | 举例 |
| %C | 年除以100并截断为整数(00-99) | 20 |
| %d | 月中的一天，零填充（01-31) | 2 |
| %D | 短MM/DD/YY日期，相当于%m/%d/%y | 01/02/2018 |
| %e | 月中的一天，空格填充（ 1-31) | 2 |
| %F | 短YYYY-MM-DD日期，相当于%Y-%m-%d | 2018/1/2 |
| %G | ISO周号的四位数年份格式， 从基于周的年份由ISO 8601定义 标准计算得出，通常仅对％V有用 | 2018 |
| %g | 两位数的年份格式，与ISO 8601一致，四位数表示法的缩写 | 18 |
| %H | 24小时格式（00-23) | 22 |
| %I | 12小时格式（01-12) | 10 |
| %j | 一年中的一天 (001-366) | 2 |
| %m | 月份为十进制数（01-12) | 1 |
| %M | 分钟(00-59) | 33 |
| %n | 换行符(") | |
| %p | AM或PM指定 | PM |
| %Q | 季度（1-4) | 1 |
| %R | 24小时HH:MM时间，相当于%H:%M | 22:33 |
| %S | 秒 (00-59) | 44 |
| %t | 水平制表符(’) | |
| %T | ISO8601时间格式(HH:MM:SS)，相当于%H:%M:%S | 22:33:44 |
| %u | ISO8601工作日为数字，星期一为1(1-7) | 2 |
| %V | ISO8601周编号(01-53) | 1 |
| %w | 工作日为十进制数，周日为0(0-6) | 2 |
| %y | 年份，最后两位数字（00-99) | 18 |
| %Y | 年 | 2018 |
| %% | %符号 | % |
-
周数计算需要使用mode参数。
该参数可以指定星期是从星期日还是星期一开始，以及返回值应在0到53还是从1到53的范围内。取值范围 [0,9]，如果省略了mode参数，则默认模式为0。使用mode参数的函数有 toWeek(date[,mode])，toYearWeek(date[,mode])，toStartOfWeek(t[,mode])。toISOWeek()是一个兼容函数，等效于toWeek(date,3)。
下表描述了mode参数的工作方式:
| 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 |
对于象“with 4 or more days this year,”的mode值，根据ISO 8601：1988对周进行编号：
-
如果包含1月1日的一周在后一年度中有4天或更多天，则为第1周。
-
否则，它是上一年的最后一周，下周是第1周。
对于像“contains January 1”的mode值, 包含1月1日的那周为本年度的第1周。