Clickhouse SQL日期处理函数及案例分享

创建于 2022-11-01 / 最近更新于 2023-07-24 / 5940

字体： [默认] [大] [更大]

适用场景

高性能数据集，以及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
取日期时间里的特定部分（v21.7及更新版本可用）	dateName(date_part,date) Date part: 'year', 'quarter', 'month', 'week', 'dayofyear', 'day', 'weekday', 'hour', 'minute', 'second'. 其中 'month', 'weekday' 返回英文月份、星期全称。	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] unit: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second'.	date_trunc('hour', '2021-12-02 16:39:09')dateTrunc('week', '2021-12-02 16:39:09')	2021-12-02 16:00:00 2021-11-29

日期或时间日期生成函数

用途	函数	结果
生成当前时间日期,可指定时区	now()	2021-12-01 20:00:00
生成今天的日期	today()	2021-12-01
生成昨天的日期	yesterday() / today() - 1	2021-11-30
生成当前时间戳	toUnixTimestamp(now())	1638388800

日期与时间计算

用途	函数	举例	结果
增减日期时间支持的时间单位unit: second, minute, hour, day, week, month, quarter, year.	+/- interval n unit' (中间数值n不能引用其他字段)	'2021-07-30 15:48:08' - interval 1 year	2020-07-30 15:48:08
	+/- interval n unit' (中间数值n不能引用其他字段)	'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
	直接加减数字, date按照天数加减; datetime按照秒数加减	'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 date_add(unit,value,date)	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 date_sub(unit,value,date)	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
计算日期时间差 --返回整数int	dateDiff('unit', startdate, enddate, [timezone])	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')	2009-07-30
将字符型日期或者时间戳转化为时间日期型	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)	2021-07-30 15:48:08
日期时间转换为时间戳	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	parseDateTimeBestEffort([文本日期],'UTC')
July 30, 2021	2021-07-30	toDate(parseDateTimeBestEffort(replaceOne([文本日期],',','')))
Aug 8, 2021	2021-08-08	toDate(parseDateTimeBestEffort(replaceOne([文本日期],',','')))
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([日期]))) 不低于v22.1版本：concat(left(monthName([日期]),3),' ', toString(Day([日期])),',',toString(toYear([日期]))) 不低于v23.2版本： formatDateTime([日期],'%b %e,%Y')
	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周。

3 人点赞过