postgresql的时间数据处理

postgresql的时间数据类型

postgresql的时间数据类型与oracle有较大的区别，在理解和使用的时候需要下一番功夫

• 下表是postgresql官网上9.6版本的时间数据类型列表

Name Storage Size Description timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone date 4 bytes date (no time of day) time [ (p) ] [ without time zone ] 8 bytes time of day (no date) time [ (p) ] with time zone 12 bytes times of day only, with time zone interval [ fields ] [ (p) ] 16 bytes time interval

在实际应用中，可以将timestamp类型近似的看作是oracle中的date类型来使用
还有一个很特殊的数据类型interval，他是用来存储两个时间的差值的
比较有意思的是timestamp和time类型都提供了一个精度值p，可用来指定保留秒的小数点后几位

时间函数

由于postgresql中灵活的时间计算模式，其自带了很多很有用的函数，下面来简单介绍一下

current_timestamp函数（now()）

current_timestamp与now()等效，都是返回当前时间，返回类型为timestamp with time zone

• 示例

postgres=# select current_timestamp;              now              ------------------------------- 2017-09-20 17:08:05.290852+08(1 row)

同理的current_date返回当前日期，current_time返回当前时间

age函数

age函数顾名思义主要用来求年纪，也就是两个时间之间的差值：

函数名 返回类型 age(timestamp, timestamp) interval age(timestamp) interval

• 示例

postgres=# select age(timestamp '1989-05-13');          age           ------------------------ 28 years 4 mons 7 days(1 row)postgres=# select age(timestamp '2016-09-19',timestamp '1989-05-13');          age           ------------------------ 27 years 4 mons 6 days(1 row)

如果只写一个时间，那么将默认用当前时间减去输入时间；
如果写了2个时间，那么将用第一个减去第二个（可以减成负数）

date_part（extract）函数

date_part与extract等效，都是从日期/时间值中抽取子域，返回类型为double precision

• 范例：

EXTRACT(field FROM source)date_part('field', source)

• 示例

postgres=# select now();              now              ------------------------------- 2017-09-21 10:27:07.664038+08(1 row)postgres=# select extract(day from now()); date_part -----------        21(1 row)postgres=# select date_part('hour', now()); date_part -----------        11(1 row)

• 常用的EXTRACT的field

由于现实环境中一般都使用功能更加强大的extract，下面举得例子都是适用于extract的

字段名 意义 hour 一天中的小时（0~23） minute 分钟（0~59） second 秒（1~59） day 月份里的日（0~31） month 一年中的月（1~12） year 年 century 世纪 dow 一周中的日（0~6），周日为0 isodow 一周中的日（1~7），周日为7 doy 一年中的第几日（1~366） quarter 一年中的季度（1~4），1~3月为1 week 一年中的第几周

date_trunc

date_trunc有点类似于数字的trunc函数，是直接截取到指定的精度

• 范例

date_trunc('field', source)

• 示例

postgres=# select date_trunc('hour',timestamp '2017-09-21 11:11:11');     date_trunc      --------------------- 2017-09-21 11:00:00(1 row)

• 常用的field

字段名 意义 second 秒 minute 分 hour 小时 day 天 week 周 month 月 quarter 季 year 年 century 世纪

make_timestamp等

make_timestamp用于创造时间，会返回一个timestamp类型的值
同理，make_date会返回一个date
make_time会返回一个time
make_interval会返回一个interval

• 示例

postgres=# select make_timestamp(2017,9,21,11,12,13);   make_timestamp    --------------------- 2017-09-21 11:12:13(1 row)postgres=# select make_time(1,2,3); make_time ----------- 01:02:03(1 row)postgres=# select make_interval(months=>3); make_interval --------------- 3 mons(1 row)

OVERLAPS

OVERLAPS函数用来判断两个时间域是否相交，返回布尔类型
如时间域相交则为true，否则为false

• 范例

(start1, end1) OVERLAPS (start2, end2)(start1, length1) OVERLAPS (start2, length2)

• 示例

postgres=# select (date '2017-01-01',date '2017-06-01') overlaps (date '2017-05-01',date '2017-10-01'); overlaps ---------- t(1 row)postgres=# select (date '2017-01-01',date '2017-06-01') overlaps (date '2017-06-01',date '2017-10-01'); overlaps ---------- f(1 row)postgres=# select (date '2017-01-01',interval '5 months') overlaps (date '2017-05-01',date '2017-10-01'); overlaps ---------- t(1 row)