• 面向OLAP的列式存储DBMS-9-[ClickHouse]的常用日期时间操作

    ClickHouse 日期时间的相关操作函数
    在这里插入图片描述

    1 日期时间操作函数

    1.1 toDate和toDateTime

    toDate、toDateTime：将字符串转成 Date、DateTime
    一、传入字符串

    SELECT toDate('2020-11-11 12:12:12') v1, toDateTime('2020-11-11 12:12:12') v2;
/*
┌─────────v1─┬──────────────────v2─┐
│ 2020-11-11 │ 2020-11-11 12:12:12 │
└────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    二、传入Date和DateTime

    -- 当然除了字符串，也可以传入 DateTime、Date
WITH toDate('2020-11-11 12:12:12') AS v1, toDateTime('2020-11-11 12:12:12') AS v2
SELECT v1, v2, toDateTime(v1) v3, toDate(v2) v4;
/*
┌─────────v1─┬──────────────────v2─┬──────────────────v3─┬─────────v4─┐
│ 2020-11-11 │ 2020-11-11 12:12:12 │ 2020-11-11 00:00:00 │ 2020-11-11 │
└────────────┴─────────────────────┴─────────────────────┴────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    三、传入时间戳

    -- 当然时间戳也是可以的
SELECT toDate(1605067932), toDateTime(1605067932);
/*
┌─toDate(1605067932)─┬─toDateTime(1605067932)─┐
│         2020-11-11 │    2020-11-11 12:12:12 │
└────────────────────┴────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    四、对于toDateTime在转换的时候也可以指定时区：

    -- Asia/Shanghai 为东八区，将 UTC 的时间转成 Asia/Shanghai 之后，会增加 8 小时
SELECT toDateTime('2020-11-11 12:12:12', 'UTC') v1, 
toDateTime(v1, 'Asia/Shanghai') v2;
/*
┌──────────────────v1─┬──────────────────v2─┐
│ 2020-11-11 12:12:12 │ 2020-11-11 20:12:12 │
└─────────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.2 timeZone

    timeZone：返回当前服务器所在的时区

    SELECT timeZone();
/*
┌─timeZone()────┐
│ Asia/Shanghai │
└───────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    1.3 toTimeZone

    toTimeZone：转换 DataTime 所在的时区

    -- 转换 DateTime 所在的时区
SELECT toDateTime('2020-01-01 12:11:33', 'UTC') v1, 
toTimeZone(v1, 'Asia/Shanghai') v2;
/*
┌──────────────────v1─┬──────────────────v2─┐
│ 2020-01-01 12:11:33 │ 2020-01-01 20:11:33 │
└─────────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.4 timeZoneOf

    timeZoneOf：返回 DateTime 所在的时区

    WITH toDateTime('2020-01-01 12:11:33', 'UTC') AS v1, 
toTimeZone(v1, 'Asia/Shanghai') AS v2
SELECT timeZoneOf(v1), timeZoneOf(v2);
/*
┌─timeZoneOf(v1)─┬─timeZoneOf(v2)─┐
│ UTC            │ Asia/Shanghai  │
└────────────────┴────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.5 timeZoneOffset

    timeZoneOffset：返回某个时区和 UTC 之间的偏移量。
    比如 Asia/Shanghai 和 UTC 之间查了 8 个小时，也就是 8 * 3600 秒

    -- 我们需要使用timeZoneOffset的时候，需要先使用toTypeName获取相应的类型
WITH toDateTime('2020-01-01 11:11:11', 'Asia/Shanghai') AS v
SELECT toTypeName(v) type, timeZoneOffset(v) offset_second, 
offset_second / 3600 offset_hour;
/*
┌─type──────────────────────┬─offset_second─┬─offset_hour─┐
│ DateTime('Asia/Shanghai') │         28800 │           8 │
└───────────────────────────┴───────────────┴─────────────┘
*/

-- 任何一个值的类型都可以通过 toTypeName 查看
SELECT toTypeName(123), toTypeName('你好'), 
toTypeName([]), toTypeName((1, 2));
/*
┌─toTypeName(123)─┬─toTypeName('你好')─┬─toTypeName(array())─┬─toTypeName((1, 2))──┐
│ UInt8           │ String             │ Array(Nothing)      │ Tuple(UInt8, UInt8) │
└─────────────────┴────────────────────┴─────────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    1.6 toYear和toMonth和toQuarter

    toYear：获取 DateTime、Date 的年份
    toMonth：获取 DateTime、Date 的月份
    toQuarter：获取 DateTime、Date 的季度

    WITH toDate('2020-08-21') AS v
SELECT toYear(v), toMonth(v), toQuarter(v);
/*
┌─toYear(v)─┬─toMonth(v)─┬─toQuarter(v)─┐
│      2020 │          8 │            3 │
└───────────┴────────────┴──────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.7 toHour和toMinute和toSecond

    toHour：获取 DateTime 的小时
    toMinute：获取 DateTime 的分钟
    toSecond：获取 DateTime 的秒

    WITH toDateTime('2020-08-21 12:11:33') AS v
SELECT toHour(v), toMinute(v), toSecond(v);
/*
┌─toHour(v)─┬─toMinute(v)─┬─toSecond(v)─┐
│        12 │          11 │          33 │
└───────────┴─────────────┴─────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.8 toDayOfYear和toDayOfMonth和toDayOfWeek

    toDayOfYear：返回某个 DateTime、Date 是一年当中的第几天（1 ~ 366）
    toDayOfMonth：返回某个 DateTime、Date 是一个月当中的第几天（1 ~ 31）
    toDayOfWeek：返回某个 DateTime、Date 是一周当中的第几天（星期一是 1，星期天是 7）

    WITH toDateTime('2020-08-21 12:11:33') AS v
SELECT toDayOfYear(v), toDayOfMonth(v), toDayOfWeek(v);
/*
┌─toDayOfYear(v)─┬─toDayOfMonth(v)─┬─toDayOfWeek(v)─┐
│            234 │              21 │              5 │
└────────────────┴─────────────────┴────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.9 toStartOfYear和toStartOfMonth和toStartOfQuarter

    toStartOfYear：返回一个 DateTime、Date 所在的年的第一天
    toStartOfMonth：返回一个 DateTime、Date 所在的月的第一天
    toStartOfQuarter：返回一个 DateTime、Date 所在的季度的第一天

    -- 2020-08-21 12:22:33 所在的年的第一天是 2020-01-01
-- 2020-08-21 12:22:33 所在的月的第一天是 2020-08-01
-- 2020-08-21 12:22:33 所在的季度的第一天是 2020-07-01，第三季度
WITH toDateTime('2020-08-21 12:22:33') AS v
SELECT toStartOfYear(v), toStartOfMonth(v), toStartOfQuarter(v);
/*
┌─toStartOfYear(v)─┬─toStartOfMonth(v)─┬─toStartOfQuarter(v)─┐
│       2020-01-01 │        2020-08-01 │          2020-07-01 │
└──────────────────┴───────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1.10 toMonday

    toMonday：返回一个距离指定 DateTime、Date 最近的星期一

    -- 2020-08-21 是星期五，所以最近的星期一是 2020-08-17
WITH toDateTime('2020-08-21 12:22:33') AS v
SELECT toDayOfWeek(v), toMonday(v);
/*
┌─toDayOfWeek(v)─┬─toMonday(v)─┐
│              5 │  2020-08-17 │
└────────────────┴─────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.11 dateTrunc

    dateTrunc：将 DateTime 按照指定部分进行截断，截断后的部分使用 0 填充

    -- 这里按小时截断，截断后的部分直接丢弃或者用 0 填充，所以会得到 2020-08-21 12:00:00
WITH toDateTime('2020-08-21 12:22:33') AS v
SELECT v, dateTrunc('hour', v);
/*
┌───────────────────v─┬─dateTrunc('hour', v)─┐
│ 2020-08-21 12:22:33 │  2020-08-21 12:00:00 │
└─────────────────────┴──────────────────────┘
*/

-- 总共可以按照 year、quarter、month、week、day、hour、minute、second 进行截断
WITH toDateTime('2020-08-21 12:22:33') AS v
SELECT dateTrunc('year', v) year_trunc, 
       dateTrunc('month', v) month_trunc, 
       dateTrunc('quarter', v) quarter_trunc,
       dateTrunc('day', v) day_truc, 
       dateTrunc('minute', v) minute_trunc
/*
┌─year_trunc─┬─month_trunc─┬─quarter_trunc─┬────────────day_truc─┬────────minute_trunc─┐
│ 2020-01-01 │  2020-08-01 │    2020-07-01 │ 2020-08-21 00:00:00 │ 2020-08-21 12:22:00 │
└────────────┴─────────────┴───────────────┴─────────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    1.12 dateAdd和dateSub

    dateAdd、dateSub：给 DateTime、Date 加/减 一个时间间隔

    WITH toDateTime('2017-08-21 12:22:33') AS v
SELECT v, dateAdd(YEAR, 3, v), dateAdd(YEAR, -3, v);
/*
┌───────────────────v─┬─plus(v, toIntervalYear(3))─┬─plus(v, toIntervalYear(-3))─┐
│ 2017-08-21 12:22:33 │        2020-08-21 12:22:33 │         2014-08-21 12:22:33 │
└─────────────────────┴────────────────────────────┴─────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    dateSub 的用法与之一样，其实当 dateAdd 加的时间间隔为负数时，等同于 dateSub。时间间隔的单位可以是 year、quarter、month、week、day、hour、minute、second，并且除了使用函数之外，我们也可以直接相加。

    --  v + INTERVAL 3 YEAR 等价于  v - INTERVAL -3 YEAR
WITH toDateTime('2017-08-21 12:22:33') AS v
SELECT v, v + INTERVAL 3 YEAR, v + INTERVAL -3 YEAR;
/*
┌───────────────────v─┬─plus(v, toIntervalYear(3))─┬─plus(v, toIntervalYear(-3))─┐
│ 2017-08-21 12:22:33 │        2020-08-21 12:22:33 │         2014-08-21 12:22:33 │
└─────────────────────┴────────────────────────────┴─────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    1.13 dateDiff

    dateDiff：计算两个 DateTime、Date 的差值

    unit — 返回结果的时间单位。 类型 ：String。
    支持的时间单位: second, minute, hour, day, week, month, quarter, year。

    WITH toDateTime('2017-08-21 12:22:33') AS v1, 
toDateTime('2018-09-15 11:44:55') AS v2
SELECT dateDiff('YEAR', v1, v2), dateDiff('MONTH', v1, v2), 
dateDiff('HOUR', v1, v2);

/*
┌─dateDiff('YEAR', v1, v2)─┬─dateDiff('MONTH', v1, v2)─┬─dateDiff('HOUR', v1, v2)─┐
│                        1 │                        13 │                     9359 │
└──────────────────────────┴───────────────────────────┴──────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1.14 now

    now：返回当前的 DateTime

    -- 默认是本地时区，当然我们也可以手动指定
SELECT now(), now('Asia/Shanghai'), now('UTC');
/*
┌───────────────now()─┬─now('Asia/Shanghai')─┬──────────now('UTC')─┐
│ 2021-09-07 12:27:31 │  2021-09-07 12:27:31 │ 2021-09-07 04:27:31 │
└─────────────────────┴──────────────────────┴─────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.15 today和yesterday

    today：返回当前的 Date，类似于 toDate( now() )
    yesterday：前一天，类似于 today() - INTERVAL 1 DAY

    SELECT today(), yesterday(), today() - INTERVAL 1 DAY;
/*
┌────today()─┬─yesterday()─┬─minus(today(), toIntervalDay(1))─┐
│ 2021-09-07 │  2021-09-06 │                       2021-09-06 │
└────────────┴─────────────┴──────────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    1.16 toYYYYMM

    toYYYYMM：将 DateTime、Date 使用整型表示，保留到月

    SELECT toYYYYMM(toDate('2020-11-11'));
/*
┌─toYYYYMM(toDate('2020-11-11'))─┐
│                         202011 │
└────────────────────────────────┘
*/

-- 同理还有 toYYYYMMDD 和 toYYYYMMDDhhmmss
SELECT toYYYYMMDD(toDate('2020-11-11'));
/*
┌─toYYYYMMDD(toDate('2020-11-11'))─┐
│                         20201111 │
└──────────────────────────────────┘
*/

SELECT toYYYYMMDDhhmmss(toDateTime('2020-11-11 12:12:12'));
/*
┌─toYYYYMMDDhhmmss(toDateTime('2020-11-11 12:12:12'))─┐
│                                      20201111121212 │
└─────────────────────────────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    1.17 formatDateTime

    formatDateTime：讲一个 DateTime、Date 格式化成字符串

    SELECT formatDateTime(toDateTime('2020-01-01 11:11:11'), '%F');
/*
┌─formatDateTime(toDateTime('2020-01-01 11:11:11'), '%F')─┐
│ 2020-01-01                                              │
└─────────────────────────────────────────────────────────┘
*/

SELECT formatDateTime(toDateTime('2020-01-01 11:11:11'), '%Y年%m月%d日 %H时%M分%S秒');
/*
┌─formatDateTime(toDateTime('2020-01-01 11:11:11'), '%Y年%m月%d日 %H时%M分%S秒')─┐
│ 2020年01月01日 11时11分11秒                                                    │
└────────────────────────────────────────────────────────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    函数不难，主要是一些格式符号我们需要记忆，以下是一些常见的格式符号：

    %Y: 对应年
%m: 对应月，01 ~ 12
%d: 对应天，01 ~ 31
%H: 对应小时，00 ~ 23
%M: 对应分钟，00 ~ 59
%S: 对应秒钟，00 ~ 59
%F: 对应年月日，相当于 %Y-%m-%d
%j: 一年中的第几天，001 ~ 366
%P: 对应上午还是下午
%Q: 对应季度，1 ~ 4
%R: 相当于 %H:%M
%u: 星期几，1 ~ 7
%V: 一年中的第几个星期，01 ~ 53

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    1.18 dateName

    dateName：返回 DateTime 指定部分，得到的是字符串

    WITH toDateTime('2020-09-17 11:22:33') AS v
SELECT dateName('year', v), dateName('month', v), dateName('quarter', v);
/*
┌─dateName('year', v)─┬─dateName('month', v)─┬─dateName('quarter', v)─┐
│ 2020                │ September            │ 3                      │
└─────────────────────┴──────────────────────┴────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.19 FROM_UNIXTIME

    FROM_UNIXTIME：将一个时间戳转成时间

    -- 默认转换的格式是 年-月-日 时:分:秒，当然我们也可以指定格式
SELECT FROM_UNIXTIME(1600312953), FROM_UNIXTIME(1600312953, '%F %R');
/*
┌─FROM_UNIXTIME(1600312953)─┬─FROM_UNIXTIME(1600312953, '%F %R')─┐
│       2020-09-17 11:22:33 │ 2020-09-17 11:22                   │
└───────────────────────────┴────────────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.20 toUnixTimestamp

    toUnixTimestamp：将一个 DateTime、Date 转成时间戳

    -- 里面除了字符串，也可以传递 DateTime、Date
SELECT toUnixTimestamp('2020-09-17 11:22:33');
/*
┌─toUnixTimestamp('2020-09-17 11:22:33')─┐
│                             1600312953 │
└────────────────────────────────────────┘
*/

-- 同时也可以指定时区，默认使用本地时区，
-- UTC 时区的 2020-09-17 11:22:33 相当于 Asia/Shanghai 时区的 2020-09-17 19:22:33 
SELECT toUnixTimestamp('2020-09-17 11:22:33', 'UTC') v1, 1600312953 + 8 * 3600;
/*
┌─────────v1─┬─plus(1600312953, multiply(8, 3600))─┐
│ 1600341753 │                          1600341753 │
└────────────┴─────────────────────────────────────┘
*/

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    2 应用

    2.1 统计数据频率

    SELECT a1,a2,a3,floor(1000*dateDiff('s', arrts[1], arrts[-1])/(length(arrts)-1)) step
from
(
	select a1,a2,a3,arraySlice(arraySort(groupUniqArray(t1)),1,100) as arrts
		from
			(
			select a1,a2,a3,starttime t1 
			from monitor
			where starttime >'2024-01-02 09:49:20'
			)
		group by a1,a2,a3
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
  • 相关阅读:
    【饮食的迷思--人类必看的真正的饮食长寿指南--来自英国国王学院顶级遗传性流行病教授】---智人必须会的生活技能 by Tim Spector--饮食的迷思
    playwright 脚本调试
    Windows、Linux应急响应大致流程
    3dmax渲染内存不足，这样解决！
    基于HTML+CSS+JavaScript制作一个介绍自己家乡河池主题的网站，排版整洁，内容丰富，主题鲜明
    吴恩达2022机器学习专项课测评来了！
    传统 IAM 已成为企业增长桎梏，下一代身份基础设施如何帮助企业破局？
    《javascript忍者秘籍》笔记
    【LeetCode刷题（数据结构）】：另一颗树的子树
    Atcoder TUPC 2023（東北大学プログラミングコンテスト 2023）P. Sub Brackets（dinic 二分图最大独立集）
  • 原文地址：https://blog.csdn.net/qq_20466211/article/details/127945622