公式ドキュメント: https://clickhouse.tech/docs/zh/sql-reference/functions/date-time-functions/

基本時間関数の一覧

now()                   // 2020-04-01 17:25:40  現在日時を取得
toYear()                // 2020                 年を抽出
toMonth()               // 4                    月を抽出
today()                 // 2020-04-01           本日の日付
yesterday()             // 2020-03-31           昨日の日付
toDayOfYear()           // 92                   年内の通算日
toDayOfWeek()           // 3                    週内の曜日（月曜=1）
toHour()                // 17                   時を取得
toMinute()              // 25                   分を取得
toSecond()              // 40                   秒を取得
toStartOfYear()         // 2020-01-01           年初の日付
toStartOfMonth()        // 2020-04-01           月初の日付
formatDateTime(now(), '%Y-%m-%d')  // 2020-04-01  書式指定
toYYYYMM()              // 202004
toYYYYMMDD()            // 20200401
toYYYYMMDDhhmmss()      // 20200401172540
dateDiff()              // 日付差分計算

時間要素の分解と変換

SELECT
    toDateTime('2020-09-02 16:10:10') AS sample_time,
    
    -- Unixタイムスタンプ変換
    toUnixTimestamp(sample_time) AS unix_ts,
    
    -- 日付・時間の分離
    toDate(sample_time) AS date_part,
    toTime(sample_time) AS time_part,
    
    -- 年・月・四半期・時・分・秒
    toYear(sample_time) AS year_val,
    toMonth(sample_time) AS month_val,
    toQuarter(sample_time) AS quarter_val,
    toHour(sample_time) AS hour_val,
    toMinute(sample_time) AS minute_val,
    toSecond(sample_time) AS second_val,
    
    -- 年内・月内・週内の位置
    toDayOfYear(sample_time) AS day_of_year,
    toDayOfMonth(sample_time) AS day_of_month,
    toDayOfWeek(sample_time) AS weekday_num,
    
    -- タイムゾーン指定
    toDate(sample_time, 'Asia/Tokyo') AS date_tokyo,
    toDateTime(sample_time, 'Asia/Tokyo') AS datetime_tokyo,
    
    -- 期間の開始点
    toStartOfYear(sample_time) AS start_of_year,
    toStartOfMonth(sample_time) AS start_of_month,
    toStartOfQuarter(sample_time) AS start_of_quarter,
    toStartOfDay(sample_time) AS start_of_day,
    toStartOfHour(sample_time) AS start_of_hour,
    toStartOfMinute(sample_time) AS start_of_minute,
    
    -- 相対年・四半期番号
    toRelativeYearNum(sample_time) AS relative_year,
    toRelativeQuarterNum(sample_time) AS relative_quarter;

未来日時の計算

-- 固定日付からの加算
WITH
    toDate('2019-09-09') AS base_date,
    toDateTime('2019-09-09 00:00:00') AS base_datetime
SELECT
    addYears(base_date, 1) AS date_plus_1y,
    addYears(base_datetime, 1) AS datetime_plus_1y;

-- 現在日時からの加算
WITH
    toDate(now()) AS current_date,
    toDateTime(now()) AS current_datetime
SELECT
    now() AS now_time,
    addYears(current_date, 1) AS date_plus_1y,
    addYears(current_datetime, 1) AS datetime_plus_1y,
    addMonths(current_date, 1) AS date_plus_1m,
    addMonths(current_datetime, 1) AS datetime_plus_1m,
    addWeeks(current_date, 1) AS date_plus_1w,
    addWeeks(current_datetime, 1) AS datetime_plus_1w,
    addDays(current_date, 1) AS date_plus_1d,
    addDays(current_datetime, 1) AS datetime_plus_1d,
    addHours(current_datetime, 1) AS datetime_plus_1h,
    addMinutes(current_datetime, 1) AS datetime_plus_1min,
    addSeconds(current_datetime, 10) AS datetime_plus_10s,
    addQuarters(current_date, 1) AS date_plus_1q,
    addQuarters(current_datetime, 1) AS datetime_plus_1q;

過去日時の計算

WITH
    toDate(now()) AS current_date,
    toDateTime(now()) AS current_datetime
SELECT
    subtractYears(current_date, 1) AS date_minus_1y,
    subtractYears(current_datetime, 1) AS datetime_minus_1y,
    subtractQuarters(current_date, 1) AS date_minus_1q,
    subtractQuarters(current_datetime, 1) AS datetime_minus_1q,
    subtractMonths(current_date, 1) AS date_minus_1m,
    subtractMonths(current_datetime, 1) AS datetime_minus_1m,
    subtractWeeks(current_date, 1) AS date_minus_1w,
    subtractWeeks(current_datetime, 1) AS datetime_minus_1w,
    subtractDays(current_date, 1) AS date_minus_1d,
    subtractDays(current_datetime, 1) AS datetime_minus_1d,
    subtractHours(current_datetime, 1) AS datetime_minus_1h,
    subtractMinutes(current_datetime, 1) AS datetime_minus_1min,
    subtractSeconds(current_datetime, 1) AS datetime_minus_1s;

-- タイムゾーンのテスト
SELECT toDate('2019-07-31', 'Asia/Tokyo') AS date_tokyo;
SELECT toDate('2019-07-31') AS date_default, 
       toDate('2019-07-31', 'Asia/Shanghai') AS date_shanghai;

SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Tokyo') AS datetime_tokyo;

日付・時刻の差分計算

-- 固定の日時ペアで差分を計算
WITH
    toDateTime('2019-07-30 10:10:10', 'Asia/Tokyo') AS start_dt,
    toDateTime('2020-10-31 11:20:30', 'Asia/Tokyo') AS end_dt
SELECT
    dateDiff('year', start_dt, end_dt) AS diff_years,
    dateDiff('month', start_dt, end_dt) AS diff_months,
    dateDiff('week', start_dt, end_dt) AS diff_weeks,
    dateDiff('day', start_dt, end_dt) AS diff_days,
    dateDiff('hour', start_dt, end_dt) AS diff_hours,
    dateDiff('minute', start_dt, end_dt) AS diff_minutes,
    dateDiff('second', start_dt, end_dt) AS diff_seconds;

-- 現在時刻と加算後の時刻で差分を計算
WITH
    now() AS current_time
SELECT
    dateDiff('year', current_time, addYears(current_time, 1)) AS diff_1y,
    dateDiff('month', current_time, addMonths(current_time, 2)) AS diff_2m,
    dateDiff('week', current_time, addWeeks(current_time, 3)) AS diff_3w,
    dateDiff('day', current_time, addDays(current_time, 3)) AS diff_3d,
    dateDiff('hour', current_time, addHours(current_time, 3)) AS diff_3h,
    dateDiff('minute', current_time, addMinutes(current_time, 30)) AS diff_30min,
    dateDiff('second', current_time, addSeconds(current_time, 35)) AS diff_35s;