ClickHouse之常见的时间周期函数 - Java技术债务

前言

在工作中，如果使用了ClickHouse作为数据的存储的话，那么难免会遇到关于时间的转换问题

比如：字符串转时间，日期等特定格式。

时区相关

timeZone

返回服务器的时区。 如果它在分布式表的上下文中执行，那么它会生成一个普通列，其中包含与每个分片相关的值。否则它会产生一个常数值。

语法

代码语言：javascript复制

timeZone()

返回时区，类型为： String。

toTimeZone

将Date或DateTime转换为指定的时区。 时区是Date/DateTime类型的属性。 表字段或结果集的列的内部值（秒数）不会更改，列的类型会更改，并且其字符串表示形式也会相应更改。

语法

代码语言：javascript复制

toTimezone(value, timezone)

参数

• value — 时间或日期和时间。类型为DateTime64。
• timezone — 返回值的时区。类型为 String。 这个参数是一个常量，因为 toTimezone 改变了列的时区（时区是 DateTime 类型的属性）。

返回类型为DateTime的日期和时间。

示例：

代码语言：javascript复制

select
    now() as current_time,
    toTimezone(toDateTime(now()), 'Asia/Yekaterinburg') as timezone_datetime,
    toTypeName(toDateTime(now())) as type_name;

current_time          timezone_datetime          type_name
2023-04-19 11:08:17   2023-04-19T08:08:17 05:00  DateTime

timeZoneOf

返回DateTime或者DateTime64数据类型的时区名称。

语法

代码语言：javascript复制

timeZoneOf(value)

参数

• value — 日期和时间。类型为DateTime或者DateTime64。

返回类型为String时区名称。

示例

代码语言：javascript复制

SELECT timezoneOf(now());

timezoneOf(now())
Etc/UTC

年相关

toYear

将Date或DateTime转换为包含年份编号（AD）的UInt16类型的数字。

toStartOfYear

将Date或DateTime向前取整到本年的第一天。 返回Date类型。

toStartOfISOYear

将Date或DateTime向前取整到ISO本年的第一天。 返回Date类型。

toRelativeYearNum

将Date或DateTime转换为年份的编号，从过去的某个固定时间点开始。

toISOYear

将Date或DateTime转换为包含ISO年份的UInt16类型的编号。

季度相关

toQuarter

将Date或DateTime转换为包含季度编号的UInt8类型的数字。

toStartOfQuarter

将Date或DateTime向前取整到本季度的第一天。 返回Date类型。

toRelativeQuarterNum

将Date或DateTime转换为季度的数字，从过去的某个固定时间点开始。

月相关

toMonth

将Date或DateTime转换为包含月份编号（1-12）的UInt8类型的数字。

toStartOfMonth

将Date或DateTime向前取整到本月的第一天。 返回Date类型。

解析不正确日期的行为是特定于实现的。 ClickHouse可能会返回零日期，抛出异常或执行«natural»溢出。

toRelativeMonthNum

将Date或DateTime转换为月份的编号，从过去的某个固定时间点开始。

周相关

toMonday

将Date或DateTime向前取整到本周的星期一。 返回Date类型。

toStartOfWeek(t[,mode])

按mode将Date或DateTime向前取整到最近的星期日或星期一。 返回Date类型。 mode参数的工作方式与toWeek()的mode参数完全相同。 对于单参数语法，mode使用默认值0。

toISOWeek

将Date或DateTime转换为包含ISO周数的UInt8类型的编号。

toRelativeWeekNum

将Date或DateTime转换为星期数，从过去的某个固定时间点开始。

toWeek(date[,mode])

返回Date或DateTime的周数。两个参数形式可以指定星期是从星期日还是星期一开始，以及返回值应在0到53还是从1到53的范围内。如果省略了mode参数，则默认 模式为0。 toISOWeek()是一个兼容函数，等效于toWeek(date,3)。 下表描述了mode参数的工作方式。

代码语言：javascript复制

toWeek(date, [, mode][, Timezone])

参数

• date – Date 或 DateTime.
• mode – 可选参数, 取值范围 [0,9]， 默认0。
• Timezone – 可选参数， 可其他时间日期转换参数的行为一致。

Mode	周的第一天	范围	描述
0	Sunday	0-53	看周日在哪一年
1	Monday	0-53	如果包含1月1日的一周在后一年度中有4天或更多天，则为第1周。否则，它是上一年的最后一周，下周是第1周。
2	Sunday	1-53	看周日在哪一年
3	Monday	1-53	如果包含1月1日的一周在后一年度中有4天或更多天，则为第1周。否则，它是上一年的最后一周，下周是第1周。
4	Sunday	0-53	如果包含1月1日的一周在后一年度中有4天或更多天，则为第1周。否则，它是上一年的最后一周，下周是第1周。
5	Monday	0-53	看周一在哪一年
6	Sunday	1-53	如果包含1月1日的一周在后一年度中有4天或更多天，则为第1周。否则，它是上一年的最后一周，下周是第1周。
7	Monday	1-53	看周一在哪一年
8	Sunday	1-53	包含1月1日的那周为本年度的第1周
9	Monday	1-53	包含1月1日的那周为本年度的第1周

示例

代码语言：javascript复制

SELECT toDate('2016-12-27') AS date, toWeek(date) AS week0, toWeek(date,1) AS week1, toWeek(date,9) AS week9;

┌───────date─┬─week0─┬─week1─┬─week9─┐
│ 2016-12-27 │    52 │    52 │     1 │
└────────────┴───────┴───────┴───────┘

toYearWeek(date[,mode])

返回Date的年和周。 结果中的年份可能因为Date为该年份的第一周和最后一周而于Date的年份不同。

mode参数的工作方式与toWeek()的mode参数完全相同。 对于单参数语法，mode使用默认值0。

toISOYear()是一个兼容函数，等效于intDiv(toYearWeek(date,3),100).

示例

代码语言：javascript复制

SELECT toDate('2016-12-27') AS date, toYearWeek(date) AS yearWeek0, toYearWeek(date,1) AS yearWeek1, toYearWeek(date,9) AS yearWeek9;

┌───────date─┬─yearWeek0─┬─yearWeek1─┬─yearWeek9─┐
│ 2016-12-27 │    201652 │    201652 │    201701 │
└────────────┴───────────┴───────────┴───────────┘

日相关

toDayOfYear

将Date或DateTime转换为包含一年中的某一天的编号的UInt16（1-366）类型的数字。

toDayOfMonth

将Date或DateTime转换为包含一月中的某一天的编号的UInt8（1-31）类型的数字。

toDayOfWeek

将Date或DateTime转换为包含一周中的某一天的编号的UInt8（周一是1, 周日是7）类型的数字。

toRelativeDayNum

将Date或DateTime转换为当天的编号，从过去的某个固定时间点开始。

时分秒相关

toHour

将DateTime转换为包含24小时制（0-23）小时数的UInt8数字。 这个函数假设如果时钟向前移动，它是一个小时，发生在凌晨2点，如果时钟被移回，它是一个小时，发生在凌晨3点（这并非总是如此 - 即使在莫斯科时钟在不同的时间两次改变）。

toMinute

将DateTime转换为包含一小时中分钟数（0-59）的UInt8数字。

toSecond

将DateTime转换为包含一分钟中秒数（0-59）的UInt8数字。 闰秒不计算在内。

toUnixTimestamp

对于DateTime参数：将值转换为UInt32类型的数字-Unix时间戳，对于String参数：根据时区将输入字符串转换为日期时间（可选的第二个参数，默认使用服务器时区），并返回相应的unix时间戳。

语法

代码语言：javascript复制

toUnixTimestamp(datetime)
toUnixTimestamp(str, [timezone])

返回类型**UInt32**unix timestamp.

示例

代码语言：javascript复制

SELECT toUnixTimestamp('2017-11-05 08:07:47', 'Asia/Tokyo') AS unix_timestamp

┌─unix_timestamp─┐
│     1509836867 │
└────────────────┘

注意 下面描述的返回类型 toStartOf 函数是 Date 或 DateTime。尽管这些函数可以将 DateTime64 作为参数，但将超出正常范围（1900年-2299年）的 DateTime64 传递给它们会给出不正确的结果。

toStartOfDay

将DateTime向前取整到今天的开始。

toStartOfHour

将DateTime向前取整到当前小时的开始。

toStartOfMinute

将DateTime向前取整到当前分钟的开始。

toStartOfSecond

将DateTime向前取整到当前秒数的开始。

语法

代码语言：javascript复制

toStartOfSecond(value[, timezone])

参数

• value — 时间和日期DateTime64.
• timezone — 返回值的Timezone (可选参数)。 如果未指定将使用 value 参数的时区。

返回类型DateTime64，输入值毫秒部分为零。

示例

不指定时区查询:

代码语言：javascript复制

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64);

┌───toStartOfSecond(dt64)─┐
│ 2020-01-01 10:20:30.000 │
└─────────────────────────┘

WITH toDateTime64('2020-01-01 10:20:30.999', 3) AS dt64
SELECT toStartOfSecond(dt64, 'Asia/Istanbul');

┌─toStartOfSecond(dt64, 'Asia/Istanbul')─┐
│                2020-01-01 13:20:30.000 │
└────────────────────────────────────────┘

toStartOfFiveMinute

将DateTime以五分钟为单位向前取整到最接近的时间点。

toStartOfTenMinutes

将DateTime以十分钟为单位向前取整到最接近的时间点。

toStartOfFifteenMinutes

将DateTime以十五分钟为单位向前取整到最接近的时间点。

toStartOfInterval(time_or_data,间隔x单位[,time_zone])

这是名为toStartOf*的所有函数的通用函数。例如， toStartOfInterval（t，INTERVAL 1 year）返回与toStartOfYear（t）相同的结果， toStartOfInterval（t，INTERVAL 1 month）返回与toStartOfMonth（t）相同的结果， toStartOfInterval（t，INTERVAL 1 day）返回与toStartOfDay（t）相同的结果， toStartOfInterval（t，INTERVAL 15 minute）返回与toStartOfFifteenMinutes（t）相同的结果。

toTime

将DateTime中的日期转换为一个固定的日期，同时保留时间部分。

toRelativeHourNum

将DateTime转换为小时数，从过去的某个固定时间点开始。

toRelativeMinuteNum

将DateTime转换为分钟数，从过去的某个固定时间点开始。

toRelativeSecondNum

将DateTime转换为秒数，从过去的某个固定时间点开始。

时间计算相关

date_trunc

将Date或DateTime按指定的单位向前取整到最接近的时间点。

语法

代码语言：javascript复制

date_trunc(unit, value[, timezone])

别名: dateTrunc.

参数

• unit — 单位. String. 可选值:second、minute、hour、day、week、month、quarter、year
• value — DateTime 或者 DateTime64.
• timezone — Timezone name 返回值的时区(可选值)。如果未指定将使用value的时区

返回按指定的单位向前取整后的DateTime。

示例

不指定时区查询:

代码语言：javascript复制

SELECT now(), date_trunc('hour', now());

┌───────────────now()─┬─date_trunc('hour', now())─┐
│ 2020-09-28 10:40:45 │       2020-09-28 10:00:00 │
└─────────────────────┴───────────────────────────┘

SELECT now(), date_trunc('hour', now(), 'Asia/Istanbul');

┌───────────────now()─┬─date_trunc('hour', now(), 'Asia/Istanbul')─┐
│ 2020-09-28 10:46:26 │                        2020-09-28 13:00:00 │
└─────────────────────┴────────────────────────────────────────────┘

date_add

将时间间隔或日期间隔添加到提供的日期或带时间的日期。

语法

代码语言：javascript复制

date_add(unit, value, date)

别名为：dateAdd, DATE_ADD。

参数

• unit — value对应的时间单位。类型为String。 可能的值：second、minute、hour、day、week、month、quarter、year
• value — 要添加的间隔值。类型为Int。
• date — 添加value的日期或日期。类型为Date或者DateTime。

返回值

通过将 value 以unit 表示，添加到date 获得的日期或带时间的日期。

类型为： Date或DateTime。

示例

代码语言：javascript复制

SELECT date_add(YEAR, 3, toDate('2018-01-01'));

┌─plus(toDate('2018-01-01'), toIntervalYear(3))─┐
│                                    2021-01-01 │
└───────────────────────────────────────────────┘

date_diff

返回两个日期或具有时间值的日期之间的差值。

语法

代码语言：javascript复制

date_diff('unit', startdate, enddate, [timezone])

别名为： dateDiff, DATE_DIFF。

参数

• unit — value对应的时间单位。类型为String。 可能的值：second、minute、hour、day、week、month、quarter、year
• startdate — 要减去的第一个时间值（减数）。类型为Date或者DateTime。
• enddate — 要减去的第二个时间值（被减数）。类型为Date或者DateTime。
• timezone — Timezone name (可选项)。如果指定，它适用于 startdate 和 enddate。如果未指定，则使用 startdate 和 enddate 的时区。如果它们不相同，则结果未指定。类型为String。

返回以 unit 表示的 enddate 和 startdate 之间的区别。

示例

代码语言：javascript复制

SELECT dateDiff('hour', toDateTime('2018-01-01 22:00:00'), toDateTime('2018-01-02 23:00:00')) as dateDiff;

┌─dateDiff─┐
│                                                                                           25 │
└──────────┘

date_sub

从提供的日期或带时间的日期中减去时间间隔或日期间隔。

语法

代码语言：javascript复制

date_sub(unit, value, date)

别名为： dateSub, DATE_SUB.

参数

• unit — value对应的时间单位。类型为String。 可能的值：second、minute、hour、day、week、month、quarter、year
• value — 要减去的时间。类型为Int。
• date — 被减去value*的日期或日期。类型为Date或者DateTime。

返回值

从 date 中减去以unit 表示的value 得到的日期或带时间的日期。

类型为：Date 或者 DateTime。

示例

代码语言：javascript复制

SELECT date_sub(YEAR, 3, toDate('2018-01-01'));

┌─minus(toDate('2018-01-01'), toIntervalYear(3))─┐
│                                     2015-01-01 │
└────────────────────────────────────────────────┘

timestamp_add

将指定的时间值与提供的日期或日期时间值相加。

语法

代码语言：javascript复制

timestamp_add(date, INTERVAL value unit)

别名为： timeStampAdd, TIMESTAMP_ADD.

参数

• date — 日期或日期与时间。类型为Date或者DateTime。
• value — 要添加的间隔值。类型为Int。
• unit — value对应的时间单位。类型为String。 可能的值：second、minute、hour、day、week、month、quarter、year

返回值

以**unit表示的指定value的日期或带时间的日期添加到date**。

类型为：Date或者DateTime。

示例

代码语言：javascript复制

select timestamp_add(toDate('2018-01-01'), INTERVAL 3 MONTH);

┌─plus(toDate('2018-01-01'), toIntervalMonth(3))─┐
│                                     2018-04-01 │
└────────────────────────────────────────────────┘

timestamp_sub

从提供的日期或带时间的日期中减去时间间隔。

语法

代码语言：javascript复制

timestamp_sub(unit, value, date)

别名为： timeStampSub, TIMESTAMP_SUB。

参数

• unit — value对应的时间单位。类型为String。 可能的值:second、minute、hour、day、week、month、quarter、year
• value — 要减去的间隔值。类型为Int。
• date — 日期或日期与时间。类型为Date或者DateTime。

返回值

从 date 中减去以**unit** 表示的**value** 得到的日期或带时间的日期。

类型为： Date或者DateTime。

示例

代码语言：javascript复制

select timestamp_sub(MONTH, 5, toDateTime('2018-12-18 01:02:03'));

┌─minus(toDateTime('2018-12-18 01:02:03'), toIntervalMonth(5))─┐
│                                          2018-07-18 01:02:03 │
└──────────────────────────────────────────────────────────────┘

add**

• addYears
• addMonths
• addWeeks
• addDays
• addHours
• addMinutes
• addSeconds
• addQuarters

函数将一段时间间隔添加到Date/DateTime，然后返回Date/DateTime。例如：

代码语言：javascript复制

WITH
    toDate('2018-01-01') AS date,
    toDateTime('2018-01-01 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time

┌─add_years_with_date─┬─add_years_with_date_time─┐
│          2019-01-01 │      2019-01-01 00:00:00 │
└─────────────────────┴──────────────────────────┘

subtract**

• subtractYears
• subtractMonths
• subtractWeeks
• subtractDays
• subtractours
• subtractMinutes
• subtractSeconds
• subtractQuarters

函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime。例如：

代码语言：javascript复制

WITH
    toDate('2019-01-01') AS date,
    toDateTime('2019-01-01 00:00:00') AS date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time

┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐
│               2018-01-01 │           2018-01-01 00:00:00 │
└──────────────────────────┴───────────────────────────────┘

timeSlots(StartTime, Duration,[, Size])

它返回一个时间数组，其中包括从从«StartTime»开始到«StartTime Duration 秒»内的所有符合«size»（以秒为单位）步长的时间点。其中«size»是一个可选参数，默认为1800。 例如，timeSlots(toDateTime('2012-01-01 12:20:00')，600) = [toDateTime（'2012-01-01 12:00:00'），toDateTime（'2012-01-01 12:30:00' ）]。 这对于搜索在相应会话中综合浏览量是非常有用的。

当前时间相关

now

返回当前日期和时间。

语法

代码语言：javascript复制

now([timezone])

参数

• timezone — Timezone name 返回结果的时区(可先参数). String.

返回类型DateTime的当前日期和时间。

示例

代码语言：javascript复制

SELECT now();
┌───────────────now()─┐
│ 2020-10-17 07:42:09 │
└─────────────────────┘

SELECT now('Asia/Istanbul');
┌─now('Asia/Istanbul')─┐
│  2020-10-17 10:42:23 │
└──────────────────────┘

转日期格式相关

today

不接受任何参数并在请求执行时的某一刻返回当前日期(Date)。 其功能与’toDate（now()）’相同。

yesterday

不接受任何参数并在请求执行时的某一刻返回昨天的日期(Date)。 其功能与’today() - 1’相同。

timeSlot

将时间向前取整半小时。 此功能用于Yandex.Metrica，因为如果跟踪标记显示单个用户的连续综合浏览量在时间上严格超过此数量，则半小时是将会话分成两个会话的最短时间。这意味着（tag id，user id，time slot）可用于搜索相应会话中包含的综合浏览量。

toYYYYMM

将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 100 MM）。

toYYYYMMDD

将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 10000 MM * 100 DD）。

toYYYYMMDDhhmmss

将Date或DateTime转换为包含年份和月份编号的UInt64类型的数字（YYYY * 10000000000 MM * 100000000 DD * 1000000 hh * 10000 mm * 100 ss）。

formatDateTime

函数根据给定的格式字符串来格式化时间。请注意：格式字符串必须是常量表达式，例如：单个结果列不能有多种格式字符串。

语法

代码语言：javascript复制

formatDateTime(Time, Format[, Timezone])

返回值根据指定格式返回的日期和时间。

支持的格式修饰符

使用格式修饰符来指定结果字符串的样式。«示例» 列是对**2018-01-02 22:33:44**的格式化结果。

修饰符	描述	示例
%C	年除以100并截断为整数(00-99)	20
%d	月中的一天，零填充（01-31)	02
%D	短MM/DD/YY日期，相当于%m/%d/%y	01/02/2018
%e	月中的一天，空格填充（ 1-31)	2
%F	短YYYY-MM-DD日期，相当于%Y-%m-%d	2018-01-02
%G	ISO周号的四位数年份格式， 从基于周的年份由ISO 8601定义 标准计算得出，通常仅对％V有用	2018
%g	两位数的年份格式，与ISO 8601一致，四位数表示法的缩写	18
%H	24小时格式（00-23)	22
%I	12小时格式（01-12)	10
%j	一年中的一天 (001-366)	002
%m	月份为十进制数（01-12)	01
%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)	01
%w	工作日为十进制数，周日为0(0-6)	2
%y	年份，最后两位数字（00-99)	18
%Y	年	2018
%%	%符号	%

示例

代码语言：javascript复制

SELECT formatDateTime(toDate('2010-01-04'), '%g')
┌─formatDateTime(toDate('2010-01-04'), '%g')─┐
│ 10                                         │
└────────────────────────────────────────────┘

dateName

返回日期的指定部分。

语法

代码语言：javascript复制

dateName(date_part, date)

参数

• date_part — 日期部分。可能的值为：year, quarter,month, week, dayofyear, day, weekday, hour, minute, second。类型为String。
• date — 日期。类型为Date, DateTime或者DateTime64。
• timezone — 时区（可选项）。类型为String。

返回日期的指定部分。

示例

代码语言：javascript复制

WITH toDateTime('2021-04-14 11:22:33') AS date_value
SELECT dateName('year', date_value) year, dateName('month', date_value) month, dateName('day', date_value) day;

┌─year─┬─month─┬─day─┐
│ 2021 │ April │ 14  │
└──────┴───────┴─────┴

FROM_UNIXTIME

当只有单个整数类型的参数时，它的作用与**toDateTime**相同，并返回DateTime类型。

例如:

代码语言：javascript复制

SELECT FROM_UNIXTIME(423543535)

┌─FROM_UNIXTIME(423543535)─┐
│      1983-06-04 10:58:55 │
└──────────────────────────┘

当有两个参数时，第一个是整型或DateTime，第二个是常量格式字符串，它的作用与**formatDateTime相同，并返回String**类型。

例如:

代码语言：javascript复制

SELECT FROM_UNIXTIME(1234334543, '%Y-%m-%d %R:%S') AS DateTime

┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘

toModifiedJulianDay

将文本形式 的**YYYY-MM-DD** 日期转换为 Int32 数字。此功能支持从**0000-01-01到9999-12-31**的日期。如果无法将参数解析为日期或日期无效，则会引发异常。

语法

代码语言：javascript复制

toModifiedJulianDay(date)

参数

• date — 文本形式的日期。类型为String或者FixedString。

返回转换的日数。

示例

代码语言：javascript复制

SELECT toModifiedJulianDay('2020-01-01');

┌─toModifiedJulianDay('2020-01-01')─┐
│                             58849 │
└───────────────────────────────────┘

toModifiedJulianDayOrNull

类似于toModifiedJulianDay()，但它不会引发异常，而是返回 NULL。

fromModifiedJulianDay

将日数字转换为 YYYY-MM-DD 文本格式的日期。该函数支持从 -678941 到 2973119 的天数（分别代表 0000-01-01 和 9999-12-31）。如果天数超出支持范围，则会引发异常。

fromModifiedJulianDayOrNull

类似于fromModifiedJulianDayOrNull()，但它不会引发异常，而是返回 NULL。

更多详情请看ClickHouse官网：直通车

clickhouse datetime 函数 语法 java

0 人点赞