注：所有的时间日期函数都可以在第二个可选参数中接受时区参数。示例：Asia / Yekaterinburg。在这种情况下，它们使用指定的时区而不是本地（默认）时区。

  1. SELECT
  2.     toDateTime('2016-06-15 23:00:00') AS time,
  3.     toDate(time) AS date_local,
  4.     toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
  5.     toString(time, 'US/Samoa') AS time_samoa
  1. ┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
  2. │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
  3. └─────────────────────┴────────────┴────────────┴─────────────────────┘

常用时间函数：

  1. now()                // 2020-04-01 17:25:40     取当前时间
  2. toYear()             // 2020                    取日期中的年份
  3. toMonth()            // 4                       取日期中的月份
  4. today()              // 2020-04-01              今天的日期
  5. yesterday()          // 2020-03-31              昨天的额日期
  6. toDayOfYear()        // 92                      取一年中的第几天     
  7. toDayOfWeek()        // 3                       取一周中的第几天
  8. toHour()             //17                       取小时
  9. toMinute()           //25                       取分钟
  10. toSecond()           //40                       取秒
  11. toStartOfYear()      //2020-01-01               取一年中的第一天
  12. toStartOfMonth()     //2020-04-01               取当月的第一天
  14. formatDateTime(now(),'%Y-%m-%d')        // 2020*04-01         指定时间格式
  15. toYYYYMM()                              //202004              
  16. toYYYYMMDD()                            //20200401
  17. toYYYYMMDDhhmmss()                      //20200401172540
  18. dateDiff()
  19. ......
  1. SELECT
  2.     toDateTime('2019-07-30 10:10:10') AS time,  
  4.     -- 将DateTime转换成Unix时间戳
  5.     toUnixTimestamp(time) as unixTimestamp,  
  7.     -- 保留 时-分-秒
  8.     toDate(time) as date_local,
  9.     toTime(time) as date_time,   -- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。
  11.     -- 获取年份，月份，季度，小时，分钟，秒钟
  12.     toYear(time) as get_year,
  13.     toMonth(time) as get_month,
  15.     -- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
  16.     toQuarter(time) as get_quarter,
  17.     toHour(time) as get_hour,
  18.     toMinute(time) as get_minute,
  19.     toSecond(time) as get_second,
  21.     -- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
  22.     toDayOfYear(time) as "当前年份中的第几天",
  23.     toDayOfMonth(time) as "当前月份的第几天",
  24.     toDayOfWeek(time) as "星期",
  25.     toDate(time, 'Asia/Shanghai') AS date_shanghai,
  26.     toDateTime(time, 'Asia/Shanghai') AS time_shanghai,
  28.     -- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
  29.     toStartOfYear(time),
  30.     toStartOfMonth(time),
  31.     toStartOfQuarter(time),
  32.     toStartOfDay(time) AS cur_start_daytime,
  33.     toStartOfHour(time) as cur_start_hour,
  34.     toStartOfMinute(time) AS cur_start_minute,
  36.     -- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
  37.     toRelativeYearNum(time),
  38.     toRelativeQuarterNum(time);

获取未来时间的函数：

  1. -- 第一种，日期格式（指定日期，需注意时区的问题）
  2. WITH
  3.     toDate('2019-09-09') AS date,
  4.     toDateTime('2019-09-09 00:00:00') AS date_time
  5. SELECT
  6.     addYears(date, 1) AS add_years_with_date,
  7.     addYears(date_time, 0) AS add_years_with_date_time;
  9. -- 第二种，日期格式（当前，本地时间）
  10. WITH
  11.     toDate(now()) as date,
  12.     toDateTime(now()) as date_time
  13. SELECT
  14.     now() as now_time,-- 当前时间
  15.     -- 之后1年
  16.     addYears(date, 1) AS add_years_with_date,                  
  17.     addYears(date_time, 1) AS add_years_with_date_time,
  19.     -- 之后1月
  20.     addMonths(date, 1) AS add_months_with_date,                 
  21.     addMonths(date_time, 1) AS add_months_with_date_time,
  23.     --之后1周
  24.     addWeeks(date, 1) AS add_weeks_with_date,                   
  25.     addWeeks(date_time, 1) AS add_weeks_with_date_time,
  27.     -- 之后1天
  28.     addDays(date, 1) AS add_days_with_date,                     
  29.     addDays(date_time, 1) AS add_days_with_date_time,
  31.     --之后1小时
  32.     addHours(date_time, 1) AS add_hours_with_date_time,  
  34.     --之后1分中       
  35.     addMinutes(date_time, 1) AS add_minutes_with_date_time,
  37.     -- 之后10秒钟     
  38.     addSeconds(date_time, 10) AS add_seconds_with_date_time,
  40.      -- 之后1个季度    
  41.     addQuarters(date, 1) AS add_quarters_with_date,            
  42.     addQuarters(date_time, 1) AS add_quarters_with_date_time;

获取过去时间的函数：

  1. WITH
  2.     toDate(now()) as date,
  3.     toDateTime(now()) as date_time
  4. SELECT
  5.     subtractYears(date, 1) AS subtract_years_with_date,
  6.     subtractYears(date_time, 1) AS subtract_years_with_date_time,
  7.     subtractQuarters(date, 1) AS subtract_Quarters_with_date,
  8.     subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
  9.     subtractMonths(date, 1) AS subtract_Months_with_date,
  10.     subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
  11.     subtractWeeks(date, 1) AS subtract_Weeks_with_date,
  12.     subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
  13.     subtractDays(date, 1) AS subtract_Days_with_date,
  14.     subtractDays(date_time, 1) AS subtract_Days_with_date_time,
  15.     subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
  16.     subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
  17.     subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;
  19. SELECT toDate('2019-07-31', 'Asia/GuangZhou') as date_guangzhou;
  20. SELECT toDate('2019-07-31'), toDate('2019-07-31', 'Asia/Beijing') as date_beijing;
  22. -- 亚洲只能加载上海的timezone？？？
  23. SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;

计算连个时刻在不同时间单位下的差值

  1. -- 第一种：指定时间计算差值示例
  2. WITH
  3.     toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
  4.     toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
  5. SELECT
  6.     dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
  7.     dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
  8.     dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
  9.     dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
  10.     dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
  11.     dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
  12.     dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
  14. -- 第二种：本地当前时间示例
  15. WITH
  16.     now() as date_time,                                                 -- 当前时间
  17.     toStartOfMonth(date_time) as start_of_month,                        -- 月初
  18.     addDays(addMonths(start_of_month, 1), -1) as end_of_month,          -- 月末
  19.     addMonths(start_of_month, 1) as next_start_of_month,                -- 下个月初
  20.     addDays(addMonths(next_start_of_month, 1), -1) as next_end_of_month -- 下个月末
  21. SELECT date_time,
  22.        start_of_month,
  23.        end_of_month,
  24.        next_start_of_month,
  25.        next_end_of_month,
  26.        dateDiff('day', start_of_month, next_start_of_month)     as month_diff_days, -- 当月天数
  27.        dateDiff('year', date_time, addYears(date_time, 1))      as diff_years,      -- 相差年份
  28.        dateDiff('month', date_time, addMonths(date_time, 2))    as diff_months,     -- 相差月份
  29.        dateDiff('week', date_time, addWeeks(date_time, 3))      as diff_week,       -- 相差星期
  30.        dateDiff('day', date_time, addDays(date_time, 3))        as diff_days,       -- 相差天数
  31.        dateDiff('hour', date_time, addHours(date_time, 3))      as diff_hours,      -- 相差小时
  32.        dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,    -- 相差分钟
  33.        dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds     -- 相差秒数
  34. ;

参考：

  1. Clickhouse时间日期函数 官方文档
    2. Clickhouse 基础知识 - 函数