日期时间函数

日期时间格式参考

当前时间

  • unix_timestamp()
    • 返回当前 unix 时间戳
  • current_date()
    • 返回当前日期
  • current_timestamp()
    • 返回当前日期时间
select unix_timestamp(), current_date(), current_timestamp();

024

时间戳（秒）与日期时间互转

  • from_unixtime(bigint unixtime[, string format])

    • 时间戳转日期时间

    • select
    from_unixtime(1645084703),
    from_unixtime(1645084703, 'yyyyMMdd'),
    from_unixtime(1645084703, 'yyyy-MM-dd HH:mm:ss'),
    from_unixtime(1645084703, 'yyyy-MM-dd HH:mm:ss.sss');

  • unix_timestamp(string date) 或 to_unix_timestamp(string date)

    • 日期时间转时间戳

    • select
    unix_timestamp('2022-02-17 15:58:23'),
    to_unix_timestamp('2022-02-17 15:58:23'),
    unix_timestamp('2022-02-17 15:58:23.646'),
    to_unix_timestamp('2022-02-17 15:58:23.646');

  • unix_timestamp(string date, string pattern) 或 to_unix_timestamp(string date, string pattern)

    • 日期时间（指定格式）转时间戳

    • select
    unix_timestamp('2022-02-17 15:58:23', 'yyyy-MM-dd HH:mm:ss'),
    unix_timestamp('2022-02-17 15:58:23.646', 'yyyy-MM-dd HH:mm:ss.SSS'),
    unix_timestamp('2022_08_4 15:58:23', 'yyyy_ww_u HH:mm:ss'), -- 第八周星期四
    unix_timestamp('2022-02-17 15:58:23 +0800', 'yyyy-MM-dd HH:mm:ss Z'),
    to_unix_timestamp('2022-02-17 15:58:23 +0800', 'yyyy-MM-dd HH:mm:ss Z');

025

UTC 标准时与指定时区互转

  • from_utc_timestamp({*any primitive type*} ts, string timezone)

    • 将 UTC 时间戳、日期时间转换为指定时区的时间戳、日期时间

    • select
    from_utc_timestamp(1645084703.0,'CST'),
    from_utc_timestamp(1645084703646,'CST'),
    from_utc_timestamp(timestamp '2022-02-17 15:58:23','CST'),
    from_utc_timestamp(timestamp '2022-02-17 15:58:23.646','CST');

  • to_utc_timestamp({*any* *primitive type*} ts, string timezone)

    • 将指定时区的时间戳、日期时间转换为 UTC 时间戳、日期时间

    • select
    to_utc_timestamp(1645084703.0,'CST'),
    to_utc_timestamp(1645084703646,'CST'),
    to_utc_timestamp(timestamp '2022-02-17 15:58:23','CST'),
    to_utc_timestamp(timestamp '2022-02-17 15:58:23.646','CST');

026

日期时间格式

  • date_format(date/timestamp/string ts, string fmt)

    • 将日期时间转换为所需要的格式输出

    • select
    date_format(current_date(), 'yyyy-MM-dd'),
    date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss'),
    date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss.SSS'),
    date_format('2020-06-01', 'y'),
    date_format('2020-06-01', 'E'),
    date_format('2020-06-01', 'D');

027

提取部分字段

  • to_date(string timestamp)
    • 提取日期（不要其他）
  • year(string date)
    • 获取年份
  • quarter(date/timestamp/string)
    • 获取季度
  • month(string date)
    • 获取月份
  • day(string date) 或 dayofmonth(date)
    • 获取月份中的第几天
  • hour(string date)
    • 获取小时
  • minute(string date)
    • 获取分钟
  • second(string date)
    • 获取秒
  • weekofyear(string date)
    • 获取一年中的第几周
  • dayofweek(string date)
    • 获取一周中的第几天（以周日为第一天）
  • extract(field FROM source)
    • 获取 field 指定的字段
    • 支持的字段有: day, dayofweek, hour, minute, month, quarter, second, week and year
select
    to_date("1970-01-01 00:00:00"),
    year("1970-01-01 00:00:00"), year("1970-01-01"),
    quarter('2015-04-08'),
    month("1970-11-01 00:00:00"),month("1970-11-01"),
    day("1970-11-01 00:00:00"), dayofmonth("1970-11-01");
select
    hour('2009-07-30 12:58:59'), hour('12:58:59'), minute('12:58:59'), second('12:58:59');
select
    weekofyear("1970-11-01 00:00:00"), weekofyear("1970-11-01"), dayofweek("1970-11-01");
select
    extract(month from "2016-10-20"),
    extract(hour from "2016-10-20 05:06:07"),
    extract(dayofweek from "2016-10-20 05:06:07");

028

周期相关

  • trunc(string date, string format)

    • 用于获取指定日期所在的指定周期的第一天

    • 支持的周期有

      • 月：‘MONTH’/‘MON’/‘MM’
      • 季：‘QUARTER’/‘Q’
      • 年：‘YEAR’/‘YYYY’/‘YY’
    • select
    trunc('2009-12-12', 'MM'),
    trunc('2009-12-12 00:11:22', 'Q'),
    trunc('2009-12-12 00:11:22.333', 'YY');

    • 该函数，除了此处 trunc(date, fmt) （用于获取指定日期所在的指定周期的第一天）的形式，还有一种 trunc(N,D) （用于截断数值）的形式，参考

  • last_day(string date)

    • 获取当月的最后一天日期

    • select
    last_day('2009-01-12'),
    last_day('2009-02-12 00:11:22'),
    last_day('2009-03-12 00:11:22.333');

    029

  • floor_year 、floor_month 、floor_day 、floor_hour 、floor_minute 、floor_second 、floor_quarter 、floor_week

    • 类似 floor 向下取整，将时间戳 “向下取整” 到指定的时间单位

    • 可以用来获取时间戳所在的指定时间周期的起始时间

    • SELECT unix_timestamp('2022-03-03 13:14:15.666');
SELECT
    floor_year(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP)),
    floor_month(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP)),
    floor_day(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP));
SELECT
    floor_hour(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP)),
    floor_minute(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP)),
    floor_second(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP));
SELECT
    floor_quarter(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP)),
    floor_week(CAST('2022-03-03 13:14:15.666' AS TIMESTAMP));

    • 029a

日期计算和偏移

  • datediff(string enddate, string startdate)

    • 计算日期差（忽略时间）

    • select
    datediff('2011-11-11', '2011-11-1'),
    datediff('2000-3-1 11:22:33', '2000-2-1'),
    datediff('2000-1-1 11:22:33.444', '2000-2-1');

  • months_between(date1, date2) 或 months_between(date1, date2, false)

    • 计算月份差（忽略时间）

    • SELECT
    months_between('2000-11-12', '2000-01-02'),
    months_between('2000-11-12', '2000-01-02', false),
    months_between('2000-11-12 12:00:00', '2000-01-12'),
    months_between('2000-01-12', '2000-11-12 12:00:00.000');

  • date_add(date/timestamp/string startdate, tinyint/smallint/int days)

    • 日期往后偏移指定的天数（忽略时间）

    • select
    date_add('2011-11-11', 1),
    date_add('2011-11-11 11:22:33', 2),
    date_add('2011-11-11 11:22:33.444', -3);

  • date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

    • 日期往前偏移指定的天数（忽略时间）

    • select
    date_sub('2011-11-11', 1),
    date_sub('2011-11-11 11:22:33', 2),
    date_sub('2011-11-11 11:22:33.444', -3);
-- 获取当前月份的第 1 天:
select date_sub(current_date, dayofmonth(current_date)-1);

    030

  • add_months(string start_date, int num_months, output_date_format)

    • 日期往后偏移指定的月数（忽略时间）

    • 如果获得的天数大于目标月份的最后一天，取该月的最后一天

    • select
    add_months('2001-01-31', 1),
    add_months('2001-01-31 11:22:33', 3),
    add_months('2001-01-31 11:22:33.444', 5);
-- 获取当前月份下个月第1天:
select add_months(date_sub(current_date,dayofmonth(current_date)-1), 1);

  • next_day(string start_date, string day_of_week)

    • 获取给定日期的下一个指定的周几的日期

      • 周几 全称 缩写
        星期一 Monday MO
        星期二 Tuesday TU
        星期三 Wednesday WE
        星期四 Thursday TH
        星期五 Friday FR
        星期六 Saturday SA
        星期日 Sunday SU 
    • select
    next_day('2021-10-31','Monday'),
    next_day('2021-10-31','Tuesday'),
    next_day('2021-10-31','Wednesday'),
    next_day('2021-10-31','Thursday'),
    next_day('2021-10-31','Friday'),
    next_day('2021-10-31','Saturday'),
    next_day('2021-10-31','Sunday');
select
    next_day('2021-10-31','MO'),
    next_day('2021-10-31','TU'),
    next_day('2021-10-31','WE'),
    next_day('2021-10-31','TH'),
    next_day('2021-10-31','FR'),
    next_day('2021-10-31','SA'),
    next_day('2021-10-31','SU');

    031

  • +/- INTERVAL int <间隔单位>

    • 支持的间隔单位：

      • 年：YEAR 或YEARS
      • 月：MONTH 或MONTHS
      • 日：DAY 或DAYS
      • 时：HOUR 或HOURS
      • 分：MINUTE 或MINUTES
      • 秒：SECOND 或SECONDS
    • select
    chr(10),
    current_timestamp(),
    chr(10),
    current_timestamp() + INTERVAL 1 year,
    chr(10),
    current_timestamp() - INTERVAL 2 month,
    chr(10),
    current_timestamp() + INTERVAL 3 day,
    chr(10),
    current_timestamp() - INTERVAL 4 hour,
    chr(10),
    current_timestamp() + INTERVAL 5 minute,
    chr(10),
    current_timestamp() - INTERVAL 6 second;

    • 031a