Clickhouse 时间日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174


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

    SELECT
        toDateTime('2016-06-15 23:00:00') AS time,
        toDate(time) AS date_local,
        toDate(time, 'Asia/Yekaterinburg') AS date_yekat,
        toString(time, 'US/Samoa') AS time_samoa

    ┌────────────────time─┬─date_local─┬─date_yekat─┬─time_samoa──────────┐
    │ 2016-06-15 23:00:00 │ 2016-06-15 │ 2016-06-16 │ 2016-06-15 09:00:00 │
    └─────────────────────┴────────────┴────────────┴─────────────────────┘


常用时间函数：

    now()                // 2020-04-01 17:25:40     取当前时间
    toYear()             // 2020                    取日期中的年份
    toMonth()            // 4                       取日期中的月份
    today()              // 2020-04-01              今天的日期
    yesterday()          // 2020-03-31              昨天的额日期
    toDayOfYear()        // 92                      取一年中的第几天
    toDayOfWeek()        // 3                       取一周中的第几天
    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('2019-07-30 10:10:10') AS time,

        -- 将DateTime转换成Unix时间戳
        toUnixTimestamp(time) as unixTimestamp,

        -- 保留 时-分-秒
        toDate(time) as date_local,
        toTime(time) as date_time,   -- 将DateTime中的日期转换为一个固定的日期，同时保留时间部分。

        -- 获取年份，月份，季度，小时，分钟，秒钟
        toYear(time) as get_year,
        toMonth(time) as get_month,

        -- 一年分为四个季度。1（一季度:1-3）,2（二季度:4-6）,3（三季度:7-9）,4（四季度:10-12）
        toQuarter(time) as get_quarter,
        toHour(time) as get_hour,
        toMinute(time) as get_minute,
        toSecond(time) as get_second,

        -- 获取 DateTime中的当前日期是当前年份的第几天，当前月份的第几日，当前星期的周几
        toDayOfYear(time) as "当前年份中的第几天",
        toDayOfMonth(time) as "当前月份的第几天",
        toDayOfWeek(time) as "星期",
        toDate(time, 'Asia/Shanghai') AS date_shanghai,
        toDateTime(time, 'Asia/Shanghai') AS time_shanghai,

        -- 得到当前年份的第一天,当前月份的第一天，当前季度的第一天，当前日期的开始时刻
        toStartOfYear(time),
        toStartOfMonth(time),
        toStartOfQuarter(time),
        toStartOfDay(time) AS cur_start_daytime,
        toStartOfHour(time) as cur_start_hour,
        toStartOfMinute(time) AS cur_start_minute,

        -- 从过去的某个固定的时间开始，以此得到当前指定的日期的编号
        toRelativeYearNum(time),
        toRelativeQuarterNum(time);

获取未来时间的函数：

    -- 第一种，日期格式（指定日期，需注意时区的问题）
    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;

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

    -- 亚洲只能加载上海的timezone？？？
    SELECT toDateTime('2019-07-31 10:10:10', 'Asia/Shanghai') as date_shanghai;

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

    -- 第一种：指定时间计算差值示例
    **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 diff_seconds;