RICH-ATONE

clickhouse时间日期函数

一、常用的时间戳到日期，日期到时间戳操作

SELECT toDateTime(1665936000,'Asia/Shanghai') `时间戳转为:%Y-%m-%d %H:%M:%S`,
       toUnixTimestamp('2022-10-17') AS `日期转为时间戳`,
       toDate(1665986835,'Asia/Shanghai') AS `时间戳转为天`,
       MINUTE(toDateTime(1665936000,'Asia/Shanghai')) AS `取分钟`,
       HOUR(toDateTime(1665936000,'Asia/Shanghai')) AS `取小时` ;

　　结果示例：

 

 二、日期 formatDateTime 到`分钟、小时、天、月、周`的常用转换

SELECT formatDateTime(toDateTime(1665936000,'Asia/Shanghai'),'%Y-%m-%d %H:%M') AS `分钟`,
       formatDateTime(toDateTime(1665936000,'Asia/Shanghai'),'%Y-%m-%d %H') AS `小时`,
       formatDateTime(toDateTime(1665936000,'Asia/Shanghai'),'%Y-%m-%d') AS `天`,
       formatDateTime(toDateTime(1665936000,'Asia/Shanghai'),'%Y-%m') AS `月`,
       formatDateTime(toDateTime(1665936000,'Asia/Shanghai'),'%V') AS `周`,
       concat(cast(toMonday(toDateTime(1665936000,'Asia/Shanghai')) AS String),'**',cast(toMonday(toDateTime(1665936000,'Asia/Shanghai'))+6 AS String)) AS `周一到周日` ;

　　结果示例：

 

 参考：https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions/#formatdatetime

posted on 2022-10-18 14:58  RICH-ATONE  阅读(2545)  评论(0)  编辑  收藏  举报

导航