generate_series时间类型的扩展应用

瀚高PG实验室

于 2018-03-31 09:31:11 发布

阅读量989

点赞数

分类专栏： PostgreSQL之SQL语言文章标签： postgresql

本文链接：https://blog.youkuaiyun.com/pg_hgdb/article/details/79765841

版权

PostgreSQL之SQL语言专栏收录该内容

99 篇文章

订阅专栏

作者：瀚高PG实验室（Highgo PG Lab）- 海无涯

小数：

highgo=# SELECT generate_series(1.2, 5, 1.3);
 generate_series 
-----------------
             1.2
             2.5
             3.8
(3 rows)


highgo=# SELECT generate_series(1.2, 5.5, 1.3);
 generate_series 
-----------------
             1.2
             2.5
             3.8
             5.1
(4 rows)

日期+整数操作（7天一循环）

highgo=# SELECT current_date + s.a AS dates FROM generate_series(0,14,7) AS s(a);
   dates    
------------
 2018-03-31
 2018-04-07
 2018-04-14
(3 rows)

其他方式来表示时间类型

highgo=# SELECT * FROM generate_series('2018-03-01 00:00'::timestamp,'2018-03-04 12:00', '10 hours');
   generate_series   
---------------------
 2018-03-01 00:00:00
 2018-03-01 10:00:00
 2018-03-01 20:00:00
 2018-03-02 06:00:00
 2018-03-02 16:00:00
 2018-03-03 02:00:00
 2018-03-03 12:00:00
 2018-03-03 22:00:00
 2018-03-04 08:00:00
(9 rows)


highgo=# select generate_series(now(), now() + '7 days', '1 day');
        generate_series        
-------------------------------
 2018-03-31 09:24:26.871687+08
 2018-04-01 09:24:26.871687+08
 2018-04-02 09:24:26.871687+08
 2018-04-03 09:24:26.871687+08
 2018-04-04 09:24:26.871687+08
 2018-04-05 09:24:26.871687+08
 2018-04-06 09:24:26.871687+08
 2018-04-07 09:24:26.871687+08
(8 rows)


highgo=# select generate_series(to_date('20180403','yyyymmdd'), to_date('20180404','yyyymmdd'), '3 hours');
    generate_series     
------------------------
 2018-04-03 00:00:00+08
 2018-04-03 03:00:00+08
 2018-04-03 06:00:00+08
 2018-04-03 09:00:00+08
 2018-04-03 12:00:00+08
 2018-04-03 15:00:00+08
 2018-04-03 18:00:00+08
 2018-04-03 21:00:00+08
 2018-04-04 00:00:00+08
(9 rows)