|
clickhouse的时间分为Date和DateTime类型。以下所有函数需要用toDate或者toDateTime
两种类型都可用。
toYear ： 获取年份toQuarter：获取季度toMonth ：获取月份toDayOfYear ：获取当天在当前年中的天数toDayOfMonth ：获取当前天在当前月份的天数toDayOfWeek ：获取当前天在当前周的天数
select toYear(toDate('2021-08-01')) year, toQuarter(toDate('2021-08-01')) quarter, toMonth(toDate('2021-08-01')) month, toDayOfYear(toDate('2021-08-01')) dayOfYear, toDayOfMonth(toDate('2021-08-01')) dayOfMonth, toDayOfWeek(toDate('2021-08-01')) datOfWeek
只对DateTime类型有用：
toHour ：获取小时数toMinute ：获取分钟数toSecond ：获取秒数toUnixTimestamp：获取当前时间的时间戳
select toHour(toDateTime('2021-08-01 13:12:54')) hour, toMinute(toDateTime('2021-08-01 13:12:54')) minute, toSecond(toDateTime('2021-08-01 13:12:54')) second, toUnixTimestamp(toDateTime('2021-08-01 13:12:54')) timestamp
Start系列
toStartOfYear：获取当前时间所在当前年的第一天。toStartOfISOYear ：获取当前时间所在当前年的第一个周一。
select toStartOfYear(toDate('2021-08-05 12:02:32')),toStartOfISOYear(toDate('2021-08-05 12:02:32'))
 
toStartOfQuarter ：获取当前时间所在季度的第一天。toStartOfMonth ：获取当前时间所在月的第一天。toMonday ：获取当前时间所在周的第一个周一。toStartOfWeek ：获取当前时间所在周的第一天（美国的周第一天是周日）。toStartOfDay ：获取当前时间所在的0点（按天取整）。toStartOfHour ：获取当前时间所在小时整点（按小时取整）。toStartOfMinute ：获取当前时间所在分钟整分钟（按分钟取整）。toStartOfSecond：获取当前时间所在秒的整秒数（按秒取整）。
SELECT
toStartOfQuarter(toDate('2021-08-05 12:02:32')) AS startOfQuarter,
toStartOfMonth(toDate('2021-08-05 12:02:32')) AS startOfMonth,
toMonday(toDate('2021-08-05 12:02:32')) AS toMonday,
toStartOfWeek(toDate('2021-08-05 12:02:32')) AS startOfWeek,
toStartOfDay(toDate('2021-08-25 12:02:32')) AS startOfDay,
toStartOfHour(toDateTime('2021-08-25 12:02:32')) AS startOfHour,
toStartOfMinute(toDateTime('2021-08-25 12:02:32')) AS startOfMinute,
toStartOfSecond(toDateTime64('2021-08-25 12:02:32.324',3)) AS startOfSecond
 22. toStartOfFiveMinute ：以5分钟为单位向前取整。 23. toStartOfTenMinutes ：以10分钟为单位向前取整。 24. toStartOfFifteenMinutes ：以15分钟为单位向前取整。
select toStartOfFiveMinute(toDateTime('2021-08-25 12:28:32')) as startFive,toStartOfTenMinutes(toDateTime('2021-08-25 12:28:32')) as startTen,toStartOfFifteenMinutes(toDateTime('2021-08-25 12:28:32')) as startFifteen
 25. toStartOfInterval(time_or_data, INTERVAL x unit [, time_zone])：自定义toSart系列函数。
toStartOfInterval(t, INTERVAL 1 year) = toStartOfYear(t),
toStartOfInterval(t, INTERVAL 1 month) = toStartOfMonth(t),
toStartOfInterval(t, INTERVAL 1 day) = toStartOfDay(t),
toStartOfInterval(t, INTERVAL 15 minute) = toStartOfFifteenMinutes(t)
按照指定年数往前取整：
select toStartOfInterval(toDateTime('2021-08-25 12:28:32'), INTERVAL 5 year) start5,toStartOfInterval(toDateTime('2021-08-25 12:28:32'), INTERVAL 10 year) start10,toStartOfInterval(toDateTime('2021-08-25 12:28:32'), INTERVAL 20 year) start20
 26. toRelative系列：获取当前时间与0000-00-00 00:00:00相差的时间数。下面是分别计算相差的年数、季度数、月数、周数、天数、小时数、分钟数、秒数。
select toRelativeYearNum(toDateTime('2021-08-25 12:28:32')) as yearCount,
toRelativeQuarterNum(toDateTime('2021-08-25 12:28:32')) as quarterCount,
toRelativeMonthNum(toDateTime('2021-08-25 12:28:32')) as monthCount,
toRelativeWeekNum(toDateTime('2021-08-25 12:28:32')) as weekCount,
toRelativeDayNum(toDateTime('2021-08-25 12:28:32')) as dayCount,
toRelativeHourNum(toDateTime('2021-08-25 12:28:32')) hourCount,
toRelativeMinuteNum(toDateTime('2021-08-25 12:28:32')) as minuteCount,
toRelativeSecondNum(toDateTime('2021-08-25 12:28:32')) as secondCount
toISOYeartoISOWeektoWeek(date, [, mode][, Timezone])：获取当前时间的周数，model为计算第一周的策略。toYearWeek(date, [, mode][, Timezone])     date_trunc：时间消除函数。
SELECT now(),
date_trunc('year', now()) as tYear,
date_trunc('quarter', now()) as tQuarter,
date_trunc('month', now()) as tMonth,
date_trunc('week', now()) as tWeek,
date_trunc('day', now()) as tDay,
date_trunc('hour', now()) as tHour,
date_trunc('minute', now()) as tMinute,
date_trunc('second', now()) as tSecond;
 32. date_add：加时间函数。如果是减时间，可以将参数设置为负数，相当于date_sub（减时间函数）。
SELECT
now(),
date_add(year, 3, now()) as tYear,
date_add(quarter,3, now()) as tQuarter,
date_add(month,3, now()) as tMonth,
date_add(week,3, now()) as tWeek,
date_add(day,3, now()) as tDay,
date_add(hour,3, now()) as tHour,
date_add(minute,3, now()) as tMinute,
date_add(second,3, now()) as tSecond;
 33. date_diff：计算时间差函数（date_diff(‘unit’, startdate, enddate, [timezone])）。
SELECT
now(),
dateDiff('second', toDateTime('2000-05-12 12:45:12'), now()) AS diffsecond,
dateDiff('minute', toDateTime('2000-05-12 12:45:12'), now()) AS diffminute,
dateDiff('hour', toDateTime('2000-05-12 12:45:12'), now()) AS diffhour,
dateDiff('day', toDateTime('2000-05-12 12:45:12'), now()) AS diffday,
dateDiff('week', toDateTime('2000-05-12 12:45:12'), now()) AS diffweek,
dateDiff('month', toDateTime('2000-05-12 12:45:12'), now()) AS diffmonth,
dateDiff('quarter', toDateTime('2000-05-12 12:45:12'), now()) AS diffquarter,
dateDiff('year', toDateTime('2000-05-12 12:45:12'), now()) AS diffyear
 34. timestamp_add(now(), INTERVAL 3 MONTH) = date_add(month,3, now()); 35. timestamp_sub(now(), INTERVAL 3 MONTH) = date_sub(month,3, now()); 36. now()：获取当前时间，支持传入指定时区做参数。  37. today() = toDate(now()) 38. yesterday() = today() - 1 39. timeSlot()：向前取整半小时。  40. toYYYYMM 41. toYYYYMMDD 42. toYYYYMMDDhhmmss  40. add和sub系列函数。  41. timeSlots(StartTime, Duration,[, Size])：以size为步长，将statTime到statTime+Duration之间的时间按照size拆分成多个时间点。  42. formatDateTime：时间转换函数，类似java里的SimpleDateFormat函数。  43. dateName：获取时间的名称。ck报错了，暂未发现原因。 
FROM_UNIXTIME：将时间戳转换是时间。  儒略日时间转换函数
toModifiedJulianDaytoModifiedJulianDayOrNullfromModifiedJulianDayfromModifiedJulianDayOrNull
SELECT toModifiedJulianDayOrNull('2020-01-01');
SELECT fromModifiedJulianDay(58849);
SELECT fromModifiedJulianDayOrNull(58849);
|