get time series in even numbers of hours instead of odd numbers of hours-CodePudding

I am generating one time-series from using below query.

SELECT date_trunc('hour', dd):: TIMESTAMP WITHOUT TIME zone as time_ent
FROM generate_series ( '2021-12-02 11:49:26'::timestamp 
                     , '2021-12-09 11:49:26'::timestamp
                     , '120 min'::interval) dd

and it will give me output like below.

2021-12-02 11:00:00.000
2021-12-02 13:00:00.000
2021-12-02 15:00:00.000
2021-12-02 17:00:00.000
2021-12-02 19:00:00.000

but I need output like.

2021-12-02 12:00:00.000
2021-12-02 14:00:00.000
2021-12-02 16:00:00.000
2021-12-02 18:00:00.000
2021-12-02 20:00:00.000

currently, the time series hours depend upon the timestamp that I pass. in above it gives me hours in odd numbers like 11,13,15...but I want the hours in even numbers in any cases like 12,14,16...it should not depend on the time I passed in query '2021-12-02 11:49:26'::timestamp, '2021-12-09 11:49:26'::timestamp

CodePudding user response：

Here is a solution according to Tilen's comment.

SELECT date_trunc('hour', dd):: TIMESTAMP WITHOUT TIME zone as time_ent
FROM generate_series (
        CASE
            WHEN MOD(EXTRACT(HOUR FROM '2021-12-02 11:49:26'::timestamp)::INT, 2) = 0 THEN
                '2021-12-02 11:49:26'::timestamp
            ELSE
                '2021-12-02 11:49:26'::timestamp   INTERVAL '1 hour'
        END
        , '2021-12-09 11:49:26'::timestamp
        , '120 min'::INTERVAL
    ) dd

CodePudding user response：

SELECT date_trunc('hour', dd):: TIMESTAMP WITHOUT TIME zone as time_ent
FROM generate_series ( timestamp '2021-12-02 11:49:26'   ((DATE_PART('hour', timestamp '2021-12-02 11:49:26')::integer % 2) || ' hours') :: INTERVAL
                     , '2021-12-03 11:49:26'::timestamp
                     , '120 min'::interval) dd

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=6b220fdb1d30c10ca17e8ce07333f5ce