Clickhouse SQL日期處理函式及案例分享

duanxuan發表於2024-05-24
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周。

相關文章