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