1. 日期类型

本节先介绍几种最常见的日期类型。

Date类型

表示日期，表示范围4713 BC ~ 5874897 AD，精确至天。当前日期current_date，另外还有几个关键词：

test=# select current_date::date, 'yesterday'::date,'today'::date,'tomorrow'::date;
 current_date |    date    |    date    |    date
--------------+------------+------------+------------
 2021-04-28   | 2021-04-27 | 2021-04-28 | 2021-04-29
(1 行记录)

TimeStamp

表示不带时区的日期时间,表示范围4713 BC ~ 294276 AD，精确至毫秒。当前时间 NOW 或 CURRENT_TIMESTAMP；因为不带时区，故切换时区对应时间数据值不变。带时区使用timestamptz类型，切换时区对应时间数据会随之变化。如果timestamp时间能满足业务需要，使用timestamp效率更高。

interval

为了表示日期，也可以使用固定时间间隔 interval数据类型，用于分析花了多长时间。例如，分析客户购物所花的时间。

test=# select interval '5 day';
 interval
----------
 5 days
(1 行记录)

interval类型还可用于日期加减计算：

test=# select timestamp'2016-03-01 00:00:00' - timestamp'2016-02-01 00:00:00' as day_in_feb;
 day_in_feb
------------
 29 days

增加7天：

test=# select '2016-03-01 00:00:00'::timestamp + interval '7 day' as new_date;
      new_date
---------------------
 2016-03-08 00:00:00
(1 行记录)

完整的参考示例：

test=# SELECT now();

now
-------------------------------
2013-08-06 10:41:19.202914+02

(1 row)

test=# SELECT now() + '3 decades 2 hours 5 centuries 20 minutes 90 days 12 months'::interval;

?column?
-------------------------------
2544-11-04 13:01:42.298739+01

interval 中描述固定时间与书写顺序无关。interval’7 day’ 等价 ‘7 day’::interval。

生成时间序列：

test=# select * from pg_catalog.generate_series(Date'2020-01-01',Date'2020-01-07', interval'1 day');
    generate_series
------------------------
 2020-01-01 00:00:00+08
 2020-01-02 00:00:00+08
 2020-01-03 00:00:00+08
 2020-01-04 00:00:00+08
 2020-01-05 00:00:00+08
 2020-01-06 00:00:00+08
 2020-01-07 00:00:00+08
(7 行记录)

2. 转换日期类型

extract() 函数

extract(component from date)

component 可以为：
year(等同y) ,month(等同mon), day(等同d)
dow(day of week的简写，1为星期天), isodow(1为星期一)
week(周), quarter(季度)

date_trunc() 函数

test=# select now(), date_trunc('month',now());
              now              |       date_trunc
-------------------------------+------------------------
 2021-04-28 11:04:17.935142+08 | 2021-04-01 00:00:00+08
(1 行记录)

注意，DATE_TRUNC(…)函数没有对值进行四舍五入。相反它输出小于或等于输入日期值的最大四舍五入值。

DATE_TRUNC(…)函数类似数学函数中的flooring函数，即输出较输入最小或相等的数（5.6返回5）；它对Group by 子句很有用。如可以按照季度进行分组统计。

DATE_TRUNC(…) 需要字符串表示截断的时间部分；而EXTRACT(…)接受的字符串不需要引号。

3. 总结

本文介绍了Postgresql的日期类型及常用转换函数。