Postgresql数据类型-时间类型

PostgreSQL对时间、日期数据类型的支持丰富而灵活，本节介绍PostgreSQL支持的时间、日期类型，及其操作符和常用函数。

PostgreSQL支持的时间、日期类型如表所示。

我们通过一个简单的例子理解这几个时间、日期数据类型，先来看看系统自带的now()函数，now()函数显示当前时间，返回的类型为timestamp [ (p) ] with time zone，如下所示：

        mydb=> SELECT now();
                      now
        -------------------------------
          2017-07-29 09:44:25.493425+08
        (1 row)

 这里提前介绍下类型转换，本章最后一节将专门介绍数据类型转换的常用方法，以下SQL中的两个冒号是指类型转换，转换成timestamp without time zone格式如下，注意返回的数据变化：

        mydb=> SELECT now()::timestamp without time zone;
                      now
        ----------------------------
            2017-07-29 09:44:55.804403
        (1 row)

转换成date格式，如下所示：

        mydb=> SELECT now()::date;
            now
        ------------
          2017-07-29
        (1 row)

转换成time without time zone，如下所示：

        mydb=> SELECT now()::time without time zone;
            now
        -----------------
        09:45:49.390428
        (1 row)

interval指时间间隔，时间间隔单位可以是hour、day、month、year等，举例如下：

        mydb=> SELECT now(), now()+interval'1 day';
                          now               |           ? column?
        ----------------------------------+-------------------------------
          2017-07-29 09:47:26.026418+08 | 2017-07-30 09:47:26.026418+08
        (1 row)

通过以上几个示例读者应该对时间、日期数据类型有了初步的了解，值得一提的是时间类型中的(p)是指时间精度，具体指秒后面小数点保留的位数，如果没声明精度默认值为6，以下示例声明精度为0：

        mydb=> SELECT now(), now()::timestamp(0);
                          now               |         now
        ----------------------------------+---------------------
          2017-07-29 09:59:42.688445+08 | 2017-07-29 09:59:43
        (1 row)

时间、日期数据类型支持的操作符有加、减、乘、除，下面举例说明。日期相加，如下所示：

        mydb=> SELECT date '2017-07-29' +  interval'1 days';
                ?column?
        ---------------------
            2017-07-30 00:00:00
        (1 row)

日期相减，如下所示：

        mydb=> SELECT date '2017-07-29' -  interval'1 hour';
                ?column?
        ---------------------
            2017-07-28 23:00:00
        (1 row)

日期相乘，如下所示：

        mydb=> SELECT 100＊ interval '1 second';
            ?column?
        ----------
            00:01:40
        (1 row)

日期相除，如下所示：

        mydb=> SELECT interval '1 hour' / double precision '3';
            ?column?
        ----------
            00:20:00
        (1 row)

接下来演示时间、日期常用函数。显示当前时间，如下所示：

        mydb=> SELECT current_date, current_time;
            current_date |    current_time
        -----------------+--------------------
            2017-07-29   | 10:53:10.375374+08
        (1 row)

另一个非常重要的函数为EXTRACT函数，可以从日期、时间数据类型中抽取年、月、日、时、分、秒信息，语法如下所示：

        EXTRACT(field FROM source)

field值可以为century、year、month、day、hour、minute、second等，source类型为timestamp、time、interval的值的表达式，例如取年份，代码如下所示：

        mydb=> SELECT EXTRACT( year FROM now());
            date_part
        -----------
                2017
        (1 row)

对于timestamp类型，取月份和月份里的第几天，代码如下所示：

        mydb=> SELECT EXTRACT( month FROM now()), EXTRACT(day FROM now());
            date_part | date_part
        --------------+-----------
                    7 |        29
        (1 row)

取小时、分钟，如下所示：

        mydb=> SELECT EXTRACT( hour FROM now()), extract (minute FROM now());
            date_part | date_part
        --------------+-----------
                    11 |        14

取秒，如下所示：

        mydb=> SELECT EXTRACT( second FROM now());
            date_part
        -----------
            43.031366
        (1 row)

取当前日期所在年份中的第几周，如下所示：

        mydb=> SELECT EXTRACT( week FROM now());
            date_part
        -----------
                30
        (1 row)

当天属于当年的第几天，如下所示：

        mydb=> SELECT EXTRACT( doy FROM now());
            date_part
        -----------
                210
        (1 row)