clickhouse的常用日期函数
时间获取获取当前时间戳:select toUnixTimestamp(now())获取指定时间的时间戳:select toUnixTimestamp('2021-05-11 14:37:37')获取当前日期时间:select now()获取当前日期:select today()获取昨日日期:select yesterday()时间转换日期时间转日期:select toDate('2021-05-1
·
时间获取
获取当前时间戳:select toUnixTimestamp(now())
获取指定时间的时间戳:select toUnixTimestamp('2021-05-11 14:37:37')
获取当前日期时间:select now()
获取当前日期:select today()
获取昨日日期:select yesterday()
时间转换
日期时间转日期:select toDate('2021-05-11 14:31:31')
日期转时间戳:select toUnixTimestamp(toDateTime('2021-05-01'))
时间戳转日期时间:select toDateTime(1620714857)
时间计算
下面的函数都可以使用date或者datetime,即toDate() or toDateTime()
获取指定时间的年份:select toYear(toDateTime('2021-05-11 10:10:10'))
获取当前日期的年份:select toYear(now())
获取指定时间的月份:select toMonth(toDateTime('2021-05-11 10:10:10'))
获取当前日期的月份:select toMonth(now())
获取指定时间的季度:select toQuarter(toDateTime('2021-05-11 10:10:10'))
获取当前年份中的第几天:select toDayOfYear(toDateTime('2021-05-11 10:10:10'))
获取当前月份的第几天:select toDayOfMonth(toDateTime('2021-05-11 10:10:10'))
获取星期几:select toDayOfWeek(toDateTime('2021-05-11 10:10:10'))
获取指定时间的小时:select toHour(toDateTime('2021-05-11 10:10:10'))
获取指定时间的分钟:select toMinute(toDateTime('2021-05-11 10:10:10'))
获取指定时间的秒数:select toSecond(toDateTime('2021-05-11 10:10:10'))
获取当前年份的第一天:select toStartOfYear(toDateTime('2021-05-11 10:10:10'))
获取当前月份的第一天:select toStartOfMonth(toDateTime('2021-05-11 10:10:10'))
获取当前季度的第一天:select toStartOfQuarter(toDateTime('2021-05-11 10:10:10'))
获取当前周的第一天:select toMonday(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始时间:select toStartOfDay(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始小时时间:select toStartOfHour(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始分钟时间:select toStartOfMinute(toDateTime('2021-05-11 10:10:10'))
获取当前时间的起始秒数时间:select toStartOfSecond(toDateTime('2021-05-11 10:10:10'))
时间增加一年:select addYears(toDateTime('2021-05-11 10:10:10'),1)
时间增加一月:select addMonths(toDateTime('2021-05-11 10:10:10'),1)
时间增加一周:select addWeeks(toDateTime('2021-05-11 10:10:10'),1)
时间增加一天:select addDays(toDateTime('2021-05-11 10:10:10'),1)
时间增加一小时:select addHours(toDateTime('2021-05-11 10:10:10'),1)
时间增加一分钟:select addMinutes(toDateTime('2021-05-11 10:10:10'),1)
时间增加一秒钟:select addSeconds(toDateTime('2021-05-11 10:10:10'),1)
时间增加一季度:select addQuarters(toDateTime('2021-05-11 10:10:10'),1)
增加用add,减去用subtract,例如时间减去一年:select subtractYears(toDateTime('2021-05-11 10:10:10'),1)
计算时间差值:dateDiff()
SELECT
dateDiff('year', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_years,
dateDiff('month', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_months,
dateDiff('week', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_week,
dateDiff('day', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_days,
dateDiff('hour', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_hours,
dateDiff('minute', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_minutes,
dateDiff('second', toDateTime('2021-05-11 10:10:10'), toDateTime('2021-05-11 18:04:44')) as diff_seconds
时间格式化
日期时间转为整形:toYYYYMMDDhhmmss()
将字符串型的日期转为时间类型:parseDateTimeBestEffort()
SELECT
now() AS dt,
toYYYYMMDDhhmmss(dt) AS dt_int,
toString(dt) AS dt_str,
parseDateTimeBestEffort(toString(dt_int)) AS datetime
更多推荐
已为社区贡献3条内容
所有评论(0)