DATE_TRUNC

DATE_TRUNC('unit', timestamp)
  • 命令说明:根据指定的日期部分(如小时、周或月)截断时间戳表达式或文本。
  • 参数说明:unit可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER或者YEAR。
    • SECOND:返回秒数对应的时间
    • MINUTE:返回分钟数对应的时间
    • HOUR:返回当天整点时刻
    • DAY:返回当天零点
    • WEEK:返回周一零点
    • MONTH:返回当月第一天零点
    • QUARTER:返回本季度第一天零点
    • YEAR:返回本年度第一天零点
  • 示例:
    select date_trunc('second', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-27 14:08:53|
    +--------------------+
    select date_trunc('minute', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-27 14:15:00|
    +--------------------+
    select date_trunc('hour', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-27 14:00:00|
    +--------------------+
    select date_trunc('day', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-27 00:00:00|
    +--------------------+
    select date_trunc('week', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-24 00:00:00|
    +--------------------+
    select date_trunc('month', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-12-01 00:00:00|
    +--------------------+
    select date_trunc('quarter', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-10-01 00:00:00|
    +--------------------+
    select date_trunc('year', now());
    +--------------------+
    | _col0              |
    +--------------------+
    | 2018-01-01 00:00:00|