ClickHouse 日期时间的相关操作函数 |
您所在的位置：网站首页 › 时间戳加一天 › ClickHouse 日期时间的相关操作函数 |
ClickHouse 日期时间的相关操作函数
|
楔子
下面来说一说日期和时间的相关操作。
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 │ └────────────┴─────────────────────┘ */ -- 当然除了字符串，也可以传入 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 │ └────────────┴─────────────────────┴─────────────────────┴────────────┘ */ -- 当然时间戳也是可以的 SELECT toDate(1605067932), toDateTime(1605067932); /* ┌─toDate(1605067932)─┬─toDateTime(1605067932)─┐ │ 2020-11-11 │ 2020-11-11 12:12:12 │ └────────────────────┴────────────────────────┘ */对于 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 │ └─────────────────────┴─────────────────────┘ */
timeZone：返回当前服务器所在的时区 SELECT timeZone(); /* ┌─timeZone()────┐ │ Asia/Shanghai │ └───────────────┘ */
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 │ └─────────────────────┴─────────────────────┘ */
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 │ └────────────────┴────────────────┘ */
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) │ └─────────────────┴────────────────────┴─────────────────────┴─────────────────────┘ */
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 │ └───────────┴────────────┴──────────────┘ */
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 │ └───────────┴─────────────┴─────────────┘ */
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 │ └────────────────┴─────────────────┴────────────────┘ */
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 │ └──────────────────┴───────────────────┴─────────────────────┘ */
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 │ └────────────────┴─────────────┘ */
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 │ └────────────┴─────────────┴───────────────┴─────────────────────┴─────────────────────┘ */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 │ └─────────────────────┴────────────────────────────┴─────────────────────────────┘ */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 │ └─────────────────────┴────────────────────────────┴─────────────────────────────┘ */
dataDiff：计算两个 DateTime、Date 的差值 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 │ └──────────────────────────┴───────────────────────────┴──────────────────────────┘ */
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 │ └─────────────────────┴──────────────────────┴─────────────────────┘ */
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 │ └────────────┴─────────────┴──────────────────────────────────┘ */
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 │ └─────────────────────────────────────────────────────┘ */
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秒 │ └────────────────────────────────────────────────────────────────────────────────┘ */函数不难，主要是一些格式符号我们需要记忆，以下是一些常见的格式符号： %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
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 │ └─────────────────────┴──────────────────────┴────────────────────────┘ */
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 │ └───────────────────────────┴────────────────────────────────────┘ */
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 │ └────────────┴─────────────────────────────────────┘ */如果觉得文章对您有所帮助，可以请囊中羞涩的作者喝杯柠檬水，万分感谢，愿每一个来到这里的人都生活愉快，幸福美满。
微信赞赏
支付宝赞赏
|
【本文地址】
今日新闻 |
推荐新闻 |