ck的日期函数

静听山水

已于 2023-02-11 17:39:39 修改

阅读量2.4k

点赞数

分类专栏： Clickhouse 文章标签： sql Powered by 金山文档

于 2023-01-30 14:11:42 首次发布

本文链接：https://blog.csdn.net/qq_41081716/article/details/128801106

版权

Clickhouse 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

select today();   --2023-01-30

select addDays(yesterday(), -1);  --2023-01-28

select date_sub(today(),1);   --2023-01-29

select  date_sub(day, 1, toStartOfYear(yesterday()))  --2022-12-31

select  toYear(yesterday());  --2023

select toDateTime('2023-02-11 16:54:59') as time --2023-02-11 16:54:59
-- 将DateTime转换成Unix时间戳
                                            toUnixTimestamp(time) as unixTimestamp,  --1676105699
-- 保留 时-分-秒
       toDate(time)                      as date_local,        --2023-02-11
       toTime(time)                      as date_time,-- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。  --1970-01-02 16:54:59
-- 获取年份，月份，季度，小时，分钟，秒钟
       toYear(time)                      as get_year,          --2023
       toMonth(time)                     as get_month,         --2
-- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
       toQuarter(time)                   as get_quarter,       --1
       toHour(time)                      as get_hour,          --16
       toMinute(time)                    as get_minute,        --54
       toSecond(time)                    as get_second,        --59
-- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
       toDayOfYear(time)                 as "当前年份中的第几天",       --42
       toDayOfMonth(time)                as "当前月份的第几天",        --11
       toDayOfWeek(time)                 as "星期",              --6
       toDate(time, 'asia/Shanghai')     as date_shanghai,     --2023-02-11
       toDateTime(time, 'asia/Shanghai') as time_shanghai,     --2023-02-11 16:54:59
-- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
       toStartOfYear(time),                                    --2023-01-01
       toStartOfMonth(time),                                   --2023-02-01
       toStartOfQuarter(time),                                 --2023-01-01
       toStartOfDay(time)                as cur_start_daytime, --2023-02-11 00:00:00
       toStartOfHour(time)               as cur_start_hour,    --2023-02-11 16:00:00
       toStartOfMinute(time)             as cur_start_minute,  --2023-02-11 16:54:00
-- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
       toRelativeYearNum(time),                                --2023
       toRelativeQuarterNum(time); --8092

select toDateTime('2023-02-11 16:54:59') as time,          --2023-02-11 16:54:59
       toISOYear(time)                   as iso_year,      --2023
       toISOWeek(time)                   as iso_week,      --6
       now()                             as cur_dateTime1, -- 返回当前时间yyyy-MM-dd HH:mm:ss  --2023-02-11 16:58:53
       today()                           as cur_dateTime2, -- 其功能与'toDate(now())'相同  --2023-02-11
       yesterday()                       as yesterday,     -- 当前日期的上一天  --2023-02-10
       toDate(time)                      as getY_M_d;      -- 2023-02-11

-- 目前只有这三种格式，没有什么toYYYY(),toYYYddmm()之类的函数，不要想当然。
select now() as nowTime,          --2023-02-11 16:54:59
-- 将Date或DateTime转换为包含年份和月份编号的UInt32类型的数字（YYYY * 100 + MM）
       toYYYYMMDDhhmmss(nowTime), --20230211165459
       toYYYYMMDD(nowTime),       --20230211
       toYYYYMM(nowTime);         --202302

-- formatDateTime(Time, Format[,Timezone])函数引用
select now()                              as now_time,                 --2023-02-11 17:00:02
       toDateTime('2023-02-11 17:00:02')  as def_datetime,
       formatDateTime(now_time, '%D')     as now_time_day_month_year,-- 02/11/23
       formatDateTime(def_datetime, '%Y') as def_datetime_year,        -- 2023
       formatDateTime(def_datetime, '%y') as def_datetime_year_litter, --23
       formatDateTime(def_datetime, '%H') as hour24,                   -- 17
       formatDateTime(def_datetime, '%I') as hour12,                   -- 05
       formatDateTime(def_datetime, '%p') as PMorAM,                   -- PM
       formatDateTime(def_datetime, '%w') as def_datetime_get_curWeek,-- 6(指定日期为星期六)
       formatDateTime(def_datetime, '%F') as def_datetime_get_date,-- 2023-02-11
       formatDateTime(def_datetime, '%T') as def_datetime_get_time,-- 17:00:02
       formatDateTime(def_datetime, '%M') as def_datetime_get_minute,-- 00(得到指定事件的“分”，minute (00-59))
       formatDateTime(def_datetime, '%S') as def_datetime_get_second;

-- 1.跳转到之后的日期函数
-- 第一种，日期格式（指定日期，需注意时区的问题）
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, --2020-09-09
       addYears(date_time, 0) as add_years_with_date_time;
--2019-09-09 00:00:00
-- 第二种，日期格式（当前，本地时间）
with
    toDate(now()) as date,
    toDateTime(now()) as date_time
select now()                     as now_time,-- 当前时间   --2023-02-11 17:05:26
       addYears(date, 1)         as add_years_with_date,-- 之后1年  --2024-02-11
       addYears(date_time, 1)    as add_years_with_date_time, --2024-02-11 17:05:26
       addMonths(date, 1)        as add_months_with_date,-- 之后1月  --2023-03-11
       addMonths(date_time, 1)   as add_months_with_date_time, --2023-03-11 17:05:26
       addWeeks(date, 1)         as add_weeks_with_date,--之后1周  --2023-02-18
       addWeeks(date_time, 1)    as add_weeks_with_date_time, --2023-02-18 17:05:26
       addDays(date, 1)          as add_days_with_date,-- 之后1天  --2023-02-12
       addDays(date_time, 1)     as add_days_with_date_time, --2023-02-12 17:05:26
       addHours(date_time, 1)    as add_hours_with_date_time,--之后1小时  --2023-02-11 18:05:26
       addMinutes(date_time, 1)  as add_minutes_with_date_time,--之后1分中  --2023-02-11 17:06:26
       addSeconds(date_time, 10) as add_seconds_with_date_time,-- 之后10秒钟  --2023-02-11 17:05:36
       addQuarters(date, 1)      as add_quarters_with_date, -- 之后1个季度  --2023-05-11
       addQuarters(date_time, 1) as add_quarters_with_date_time;
pp2023-05-11 17:05:26

-- 2.跳转到当前日期之前的函数(函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime)
with
    toDate(now()) as date,         --2023-02-11
    toDateTime(now()) as date_time --2023-02-11 17:07:22
select subtractYears(date, 1)         as subtract_years_with_date,         --2022-02-11
       subtractYears(date_time, 1)    as subtract_years_with_date_time,    --2022-02-11 17:07:22
       subtractQuarters(date, 1)      as subtract_Quarters_with_date,      --2022-11-11
       subtractQuarters(date_time, 1) as subtract_Quarters_with_date_time, --2022-11-11 17:07:22
       subtractMonths(date, 1)        as subtract_Months_with_date,        --2023-01-11
       subtractMonths(date_time, 1)   as subtract_Months_with_date_time,   --2023-01-11 17:07:22
       subtractWeeks(date, 1)         as subtract_Weeks_with_date,         --2023-02-04
       subtractWeeks(date_time, 1)    as subtract_Weeks_with_date_time,    --2023-02-04 17:07:22
       subtractDays(date, 1)          as subtract_Days_with_date,          --2023-02-10
       subtractDays(date_time, 1)     as subtract_Days_with_date_time,     --2023-02-10 17:07:22
       subtractHours(date_time, 1)    as subtract_Hours_with_date_time,    --2023-02-11 16:07:22
       subtractMinutes(date_time, 1)  as subtract_Minutes_with_date_time,  --2023-02-11 17:06:22
       subtractSeconds(date_time, 1)  as subtract_Seconds_with_date_time; --2023-02-11 17:07:21

select toDate('2019-07-31', 'asia/GuangZhou') as date_guangzhou; --2019-07-31
select toDate('2019-07-31'), toDate('2019-07-31', 'asia/Beijing') as date_beijing;
--2019-07-31,2019-07-31

-- 计算连个时刻在不同时间单位下的差值
-- 第一种：指定时间计算差值示例
with
    toDateTime('2019-07-30 10:10:10') as date_shanghai_one,
    toDateTime('2020-10-31 11:20:30') as date_shanghai_two
select date_shanghai_one,                                                        --2019-07-30 10:10:10
       date_shanghai_two,                                                        --2020-10-31 11:20:30
       dateDiff('year', date_shanghai_one, date_shanghai_two)   as diff_years,   --1
       dateDiff('month', date_shanghai_one, date_shanghai_two)  as diff_months,  --15
       dateDiff('week', date_shanghai_one, date_shanghai_two)   as diff_week,    --65
       dateDiff('day', date_shanghai_one, date_shanghai_two)    as diff_days,    --459
       dateDiff('hour', date_shanghai_one, date_shanghai_two)   as diff_hours,   --11017
       dateDiff('minute', date_shanghai_one, date_shanghai_two) as diff_minutes, --661030
       dateDiff('second', date_shanghai_one, date_shanghai_two) as diff_seconds;
--39661820

-- 第二种：本地当前时间示例
with
    now() as date_time
select date_time,                                                                --2023-02-11 17:25:31
       dateDiff('year', date_time, addYears(date_time, 1))      as diff_years,   --1
       dateDiff('month', date_time, addMonths(date_time, 2))    as diff_months,  --2
       dateDiff('week', date_time, addWeeks(date_time, 3))      as diff_week,    --3
       dateDiff('day', date_time, addDays(date_time, 3))        as diff_days,    --3
       dateDiff('hour', date_time, addHours(date_time, 3))      as diff_hours,   --3
       dateDiff('minute', date_time, addMinutes(date_time, 30)) as diff_minutes, --30
       dateDiff('second', date_time, addSeconds(date_time, 35)) as diff_seconds;
--35

-- timeSlot(StartTime, Duration, [,Size])
-- 它返回一个时间数组，其中包括从从“StartTime”开始到“StartTime + Duration 秒”内的所有符合“size”（以秒为单位）步长的时间点
-- 作用:搜索在相应会话中综合浏览量是非常有用的。
select timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600)) as dateTimeArray, -- ['2012-01-01 12: 00:00','2012-01-01 12:30:00']
       dateTimeArray[0]                                            as arr_index_0,   -- 1970-01-01 08:00:00
       dateTimeArray[1]                                            as arr_index_1,   -- 2012-01-01 12:00:00
       dateTimeArray[2]                                            as arr_index_2,   -- 2012-01-01 12:30:00
       dateTimeArray[3]                                            as arr_index_3,   -- 1970-01-01 08:00:00
       dateTimeArray[4]                                            as arr_index_4;
-- 1970-01-01 08:00:00
-- no result.
-- toUInt32(600) 表示之后间距20秒的时刻
select timeSlots(now(), toUInt32(600), 20) as dateTimeArray, -- ['2012-01-01 12:20:00','2012-01-01 12:20:20','2012-01-01 12:20:40','2012-01-01 12:21:00','2012-01-01 12:21:20','2012-01-01 12:21:40','2012-01-01 12:22:00','2012-01-01 12:22:20','2012-01-01 12:22:40','2012-01-01 12:23:00','2012-01-01 12:23:20','2012-01-01 12:23:40','2012-01-01 12:24:00','2012-01-01 12:24:20','2012-01-01 12:24:40','2012-01-01 12:25:00','2012-01-01 12:25:20','2012-01-01 12:25:40','2012-01-01 12:26:00','2012-01-01 12:26:20','2012-01-01 12:26:40','2012-01-01 12:27:00','2012-01-01 12:27:20','2012-01-01 12:27:40','2012-01-01 12:28:00','2012-01-01 12:28:20','2012-01-01 12:28:40','2012-01-01 12:29:00','2012-01-01 12:29:20','2012-01-01 12:29:40','2012-01-01 12:30:00']
       dateTimeArray[0]                    as arr_index_0,   -- 1970-01-01 08:00:00
       dateTimeArray[1]                    as arr_index_1,   -- 2012-01-01 12:20:00
       dateTimeArray[2]                    as arr_index_2,   --2012-01-01 12:20:20
       dateTimeArray[3]                    as arr_index_3,   --2012-01-01 12:20:40
       dateTimeArray[4]                    as arr_index_4,   --2012-01-01 12:21:00
       dateTimeArray[5]                    as arr_index_5;
--2012-01-01 12:21:20
-- 指定时间为基准，之后每个元素增加20秒
select timeSlots(toDateTime('2012-01-01 12:20:00'), toUInt32(600), 20) as cur_dateTimeArray, -- ['2012-01-01 12:20:00','2012-01-01 12:20:20','2012-01-01 12:20:40','2012-01-01 12:21:00','2012-01-01 12:21:20','2012-01-01 12:21:40','2012-01-01 12:22:00','2012-01-01 12:22:20','2012-01-01 12:22:40','2012-01-01 12:23:00','2012-01-01 12:23:20','2012-01-01 12:23:40','2012-01-01 12:24:00','2012-01-01 12:24:20','2012-01-01 12:24:40','2012-01-01 12:25:00','2012-01-01 12:25:20','2012-01-01 12:25:40','2012-01-01 12:26:00','2012-01-01 12:26:20','2012-01-01 12:26:40','2012-01-01 12:27:00','2012-01-01 12:27:20','2012-01-01 12:27:40','2012-01-01 12:28:00','2012-01-01 12:28:20','2012-01-01 12:28:40','2012-01-01 12:29:00','2012-01-01 12:29:20','2012-01-01 12:29:40','2012-01-01 12:30:00']
       cur_dateTimeArray[0]                                            as arr_index_0,       -- 1970-01-01 08:00:00
       cur_dateTimeArray[1]                                            as arr_index_1,       -- 2012-01-01 12:20:00
       cur_dateTimeArray[2]                                            as arr_index_2,       -- 2012-01-01 12:20:20
       cur_dateTimeArray[3]                                            as arr_index_3,       -- 2012-01-01 12:20:40
       cur_dateTimeArray[4]                                            as arr_index_4,       -- 2012-01-01 12:21:00
       cur_dateTimeArray[5]                                            as arr_index_5;       -- 2012-01-01 12:21:20