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

高性能数据集，以及Clickhouse直连数据集。

  1. 所有涉及到时分秒（DateTime字段）的函数都可能需要时区参数 timezone，时区格式一般为UTC时区或地理位置的IANA标识符（例如 Europe/Moscow），未指定时区则默认转换为服务器的时区（例如世界标准时间UTC）。

  2. 部分函数大小写敏感：大小写混写的函数为Clickhouse专有函数，必须原样使用（例如 toYear）；全部为大写或小写的函数为兼容其他数据库的函数，大小写不敏感，即大小写都行（例如 YEAR）。

  3. 以下函数非全部可用函数，是基于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

'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
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

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([日期])))
不低于v22.1版本：concat(left(monthName([日期]),3),' ', toString(Day([日期])),',',toString(toYear([日期])))
不低于v23.2版本： formatDateTime([日期],'%b %e,%Y')

Sunday

dateName('weekday',[日期])

  1. 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

%%

%符号

%

  1. 周数计算需要使用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周。

posted @ 2024-05-24 12:33  duanxuan  阅读(1397)  评论(0)  编辑  收藏  举报