Clickhouse之常用函數操作

常用函數彙總

[日期]

注：所有的時間日期函數都可以在第二個可選參數中接受時區參數。示例：Asia / Yekaterinburg。在這種情況下，它們使用指定的時區而不是本地（預設）時區。

now()         // 2022-08-08 17:19:00  取目前時間
toYear()      // 2022                             取日志中的年份
toMonth()  // 8                                   取日子中的月份
today()      // 2022-08-08                   今天的日期
yesterday()  // 2022-08-07                昨天的日期
toDayOfYear()  // 100                         取一年中的第幾天
toDayOfWeek()  // 1                           取一周中的第幾天
toHour()           17                              取小時
toMinute()      22                                取分鐘
toSecond()    33                                 取秒
toStartOfYear()    2022-01-01              取一年中的第一天
toStartOfMonth()   2022-08-01           取當月的第一天
toStartOfQuarter(time),



formatDatetime(now(), '%Y-%m-%d')   // 2022-08-08  指定時間格式
toYYYYMM()                              //202004              
toYYYYMMDD()                            //20200401
toYYYYMMDDhhmmss()                      //20200401172540


toUnixTimestamp(time)              将datetime格式轉換成Unix時間戳
toQuarter(time)                       季度(一年分為四個季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）)      

　　

未來日期:

-- 第一種，日期格式（指定日期，需注意時區的問題）
WITH
    toDate('2019-09-09') AS date,
    toDateTime('2019-09-09 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 0) AS add_years_with_date_time;

-- 第二種，日期格式（目前，本地時間）
WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    now() as now_time,-- 目前時間
    -- 之後1年
    addYears(date, 1) AS add_years_with_date,                  
    addYears(date_time, 1) AS add_years_with_date_time,

    -- 之後1月
    addMonths(date, 1) AS add_months_with_date,                 
    addMonths(date_time, 1) AS add_months_with_date_time,

    --之後1周
    addWeeks(date, 1) AS add_weeks_with_date,                   
    addWeeks(date_time, 1) AS add_weeks_with_date_time,

    -- 之後1天
    addDays(date, 1) AS add_days_with_date,                     
    addDays(date_time, 1) AS add_days_with_date_time,

    --之後1小時
    addHours(date_time, 1) AS add_hours_with_date_time,  

    --之後1分中       
    addMinutes(date_time, 1) AS add_minutes_with_date_time,

    -- 之後10秒鐘     
    addSeconds(date_time, 10) AS add_seconds_with_date_time,

     -- 之後1個季度    
    addQuarters(date, 1) AS add_quarters_with_date,            
    addQuarters(date_time, 1) AS add_quarters_with_date_time;      

過去日期:

WITH
    toDate(now()) as date,
    toDateTime(now()) as date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time,
    subtractQuarters(date, 1) AS subtract_Quarters_with_date,
    subtractQuarters(date_time, 1) AS subtract_Quarters_with_date_time,
    subtractMonths(date, 1) AS subtract_Months_with_date,
    subtractMonths(date_time, 1) AS subtract_Months_with_date_time,
    subtractWeeks(date, 1) AS subtract_Weeks_with_date,
    subtractWeeks(date_time, 1) AS subtract_Weeks_with_date_time,
    subtractDays(date, 1) AS subtract_Days_with_date,
    subtractDays(date_time, 1) AS subtract_Days_with_date_time,
    subtractHours(date_time, 1) AS subtract_Hours_with_date_time,
    subtractMinutes(date_time, 1) AS subtract_Minutes_with_date_time,
    subtractSeconds(date_time, 1) AS subtract_Seconds_with_date_time;      

計算時間內插補點:

-- 第一種：指定時間計算內插補點示例
WITH
    toDateTime('2019-07-30 10:10:10', 'Asia/Shanghai') as date_shanghai_one,
    toDateTime('2020-10-31 11:20:30', 'Asia/Shanghai') as date_shanghai_two
SELECT
    dateDiff('year', date_shanghai_one, date_shanghai_two) as diff_years,
    dateDiff('month', date_shanghai_one, date_shanghai_two) as diff_months,
    dateDiff('week', date_shanghai_one, date_shanghai_two) as diff_week,
    dateDiff('day', date_shanghai_one, date_shanghai_two) as diff_days,
    dateDiff('hour', date_shanghai_one, date_shanghai_two) as diff_hours,
    dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes,
    dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;

-- 第二種：本地目前時間示例
WITH
    now() as date_time
SELECT
    dateDiff('year', date_time, addYears(date_time, 1)) as diff_years,
    dateDiff('month', date_time, addMonths(date_time, 2)) as diff_months,
    dateDiff('week', date_time, addWeeks(date_time, 3)) as diff_week,
    dateDiff('day', date_time, addDays(date_time, 3)) as diff_days,
    dateDiff('hour', date_time, addHours(date_time, 3)) as diff_hours,
    dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes,
    dateDiff('second', date_time, addSeconds(date_time, 35)) as