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