【ClickHouse】0：clickhouse学习3之时间日期函数
官方文档： https://clickhouse.tech/docs/zh/sql-reference/functions/date-time-functions/
常用的clickhouse时间函数：
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('2020-09-02 16: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; 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;
