【PostgreSQL-9.6.3】函数（3）--日期和时间函数

在PostgreSQL中，DATE、TIME、TIMESTAMP是三种不同的数据类型。DATE表示日期类型，格式为YYYY-MM-DD或YYYYMMDD；TIME表示时间类型，格式为hh:mi:ss；

TIMESTAM类型的格式一般为'YYYY-MM-DD hh:mi:ss'。

1. current_date、current_time和localtime

current_date按照YYYY-MM-DD格式返回当前日期；current_time获取系统的当前时间；localtime的作用和current_time相同，不同点在于localtime返回的时间不带时区。

test=# select current_date,current_time,localtime;
    date    |       timetz       |      time       
------------+--------------------+-----------------
 2017-05-31 | 21:26:47.731091+08 | 21:26:47.731091
(1 row)

2. current_timestamp、localtimestamp和now()

这三个函数的作用都是返回系统当前的日期和时间。

test=# select current_timestamp,localtimestamp,now();
             now              |         timestamp         |             now              
------------------------------+---------------------------+------------------------------
 2017-05-31 21:28:24.17628+08 | 2017-05-31 21:28:24.17628 | 2017-05-31 21:28:24.17628+08
(1 row)

3. extract(type from date)

extract函数从日期中提取部分值。

(1)依次提取日期中的年份、月份、日部分

test=# select extract(year from timestamp '2017-05-31 21:31:20') as year,extract(month from timestamp '2017-05-31 21:31:20') as month,extract(day from timestamp '2017-05-31 21:31:20') as day;
 year | month | day 
------+-------+-----
 2017 |     5 |  31
(1 row)

(2)依次查询指定日期是所在年的第几天、所在周的星期几、所在年的第几季度

test=# select extract (doy from timestamp '2017-05-31 21:31:20') as doy,extract (dow from timestamp '2017-05-31 21:31:20') as dow,extract(quarter from timestamp '2017-05-31 21:31:20') as quarter;
 doy | dow | quarter 
-----+-----+---------
 151 |   3 |       2
(1 row)

4. 日期的运算

test=# select date '2017-05-31' + integer '10';
  ?column?  
------------
 2017-06-10
(1 row)

test=# select date '2017-05-31' + interval '5 hour';
      ?column?       
---------------------
 2017-05-31 05:00:00
(1 row)

test=# select date '2017-05-31' + time '12:00';
      ?column?       
---------------------
 2017-05-31 12:00:00
(1 row)

test=# select timestamp '2017-05-31 21:31:20' + interval '3 hour';
      ?column?       
---------------------
 2017-06-01 00:31:20
(1 row)

test=# select date '2017-07-22' - date '2017-05-31';
 ?column? 
----------
       52
(1 row)

test=# select date '2017-05-31' - integer '10';
  ?column?  
------------
 2017-05-21
(1 row)

test=# select 15 * interval '2 day';
 ?column? 
----------
 30 days
(1 row)

test=# select 50 * interval '2 second';
 ?column? 
----------
 00:01:40
(1 row)

test=# select interval '1 hour' / integer '2';
 ?column? 
----------
 00:30:00
(1 row)