• 【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;
  • 相关阅读:
    《感想都是碎片》
    我容易么我……
    elvish Template Library
    可以直接在C++里面写类似RSL的shader了
    全身和鞘内注射依那西普抑制糖尿病小鼠的触觉异常痛敏
    针对英夫利昔单抗或阿达木单抗的抗体存在与否决定转用依那西普的疗效
    全基因组关联研究发现TNF抑制剂治疗RA与7个位点的多态性有关
    依那西普治疗幼年型特发性关节炎的疗效
    强直性脊柱炎中的髋关节受累:流行病学和髋关节置换术的相关危险因素
    长期使用依那西普对幼年型特发性关节炎患儿生长的影响
  • 原文地址:https://www.cnblogs.com/DBArtist/p/clickhouse_date_time_function.html
Copyright © 2020-2023  润新知