generate_series产生连续数据
2021-06-17 10:35:14    22    0    0
ivan

1、按月产生连续数据

highgo=# SELECT to_char( T, 'yyyy-mm' ) AS TIME FROM generate_series(to_timestamp('2021-01', 'yyyy-mm'), to_timestamp('2021-10', 'yyyy-mm'), '1 months') AS T;

      time   

    ---------

     2020-01

     2020-02

     2020-03

     2020-04

 

2、按天产生连续数据

highgo=# SELECT to_char( T, 'yyyy-mm-dd' ) AS TIME FROM generate_series(to_timestamp('2021-01-01', 'yyyy-mm-dd'), to_timestamp('2021-01-03', 'yyyy-mm-dd'), '1 days') AS T;

        time    

    ------------

     2020-01-01

     2020-01-02

     2020-01-03

  

3、按小时产生连续数据

highgo=# SELECT to_char( t, 'yyyy-mm-dd hh24:mi:ss' ) AS TIME FROM generate_series ( to_timestamp( '2021-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), to_timestamp( '2021-01-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss' ), '1 hours' ) AS t;

            time         

    ---------------------

     2020-01-01 00:00:00

     2020-01-01 01:00:00

     .    .    .

     2020-01-01 06:00:00

     2020-01-01 07:00:00

 

Pre: No Post

Next: 触发器

22
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content