[1182]clickhouse的日期函数

时间获取

获取当前时间戳：select toUnixTimestamp(now())
获取指定时间的时间戳：select toUnixTimestamp('2021-05-11 14:37:37')
获取当前日期时间：select now()
获取当前日期：select today()
获取昨日日期：select yesterday()

时间转换

日期时间转日期：select toDate('2021-05-11 14:31:31')
日期转时间戳：select toUnixTimestamp(toDateTime('2021-05-01'))
时间戳转日期时间：select toDateTime(1620714857)

时间计算

下面的函数都可以使用date或者datetime，即toDate() or toDateTime()

获取指定时间的年份：select toYear(toDateTime('2021-05-11 10:10:10'))
获取当前日期的年份：select toYear(now())
获取指定时间的月份：select toMonth(toDateTime('2021-05-11 10:10:10'))
获取当前日期的月份：select toMonth(now())
获取指定时间的季度：select toQuarter(toDateTime('2021-05-11 10:10:10'))
获取当前年份中的第几天：select toDayOfYear(toDateTime('2021-05-11 10:10:10'))
获取当前月份的第几天：select toDayOfMonth(toDateTime('2021-05-11 10:10:10'))
获取星期几：select toDayOfWeek(toDateTime('2021-05-11 10:10:10'))
获取指定时间的小时：select toHour(toDateTime('2021-05-11 10:10:10'))
获取指定时间的分钟：select toMinute(toDateTime('2021-05-11 10:10:10'))
获取指定时间的秒数：select toSecond(toDateTime('2021-05-11 10:10:10'))

获取当前年份的第一天：select toStartOfYear(toDateTime('2021-05-11 10:10:10'))
获取当前月份的第一天：select toStartOfMonth(toDateTime('2021-05-11 10:10:10'))
获取当前季度的第一天：select toStartOfQuarter(toDateTime('2021-05-11 10:10:10'))
获取当前周的第一天：select toMonday(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始时间：select toStartOfDay(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始小时时间：select toStartOfHour(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始分钟时间：select toStartOfMinute(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始秒数时间：select toStartOfSecond(toDateTime('2021-05-11 10:10:10'))

时间增加一年：select addYears(toDateTime('2021-05-11 10:10:10'),1)
时间增加一月：select addMonths(toDateTime('2021-05-11 10:10:10'),1)
时间增加一周：select addWeeks(toDateTime('2021-05-11 10:10:10'),1)
时间增加一天：select addDays(toDateTime('2021-05-11 10:10:10'),1)
时间增加一小时：select addHours(toDateTime('2021-05-11 10:10:10'),1)
时间增加一分钟：select addMinutes(toDateTime('2021-05-11 10:10:10'),1)
时间增加一秒钟：select addSeconds(toDateTime('2021-05-11 10:10:10'),1)
时间增加一季度：select addQuarters(toDateTime('2021-05-11 10:10:10'),1)

增加用add,减去用subtract,例如时间减去一年：select subtractYears(toDateTime('2021-05-11 10:10:10'),1)

计算时间差值：dateDiff()
SELECT
    dateDiff('year', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_years,
    dateDiff('month', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_months,
    dateDiff('week', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_week,
    dateDiff('day', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_days,
    dateDiff('hour', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_hours,
    dateDiff('minute', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_minutes,
    dateDiff('second', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_seconds

select 
-- 当月第一天  toStartOfYear(),toMonday(),toStartOfQuarter()
toStartOfMonth(now()) start_month,
-- 当月月末
toStartOfMonth(addMonths(now(),1))-1 end_month,
-- 当年月末
toStartOfYear(addMonths(now(),12))-1 end_year,
--
parseDateTimeBestEffort('20220427'),
--
toString(20220427),
toFloat32('20220427'),
ifnull(null,1)

时间格式化

日期时间转为整形：toYYYYMMDDhhmmss()
将字符串型的日期转为时间类型：parseDateTimeBestEffort()

SELECT 
    now() AS dt,
    toYYYYMMDDhhmmss(dt) AS dt_int,
    toString(dt) AS dt_str,
    parseDateTimeBestEffort(toString(dt_int)) AS datetime;

yyyymmdd 转yyyy-mm-dd 20220501 转2022-05-10
select toDate(parseDateTimeBestEffort('20220510'));

参考：https://blog.csdn.net/anyitian/article/details/116664687
https://blog.csdn.net/u013225189/article/details/124709013