CODEWITHSUNDEEP
sqlpostgresql
Dowwie
Dowwie

Reputation: 2043

Generating a series of time ranges between two dates in Postgresql (recurring time slots)

I'm trying to generate a series of tstzrange's between two dates and during a specific time slot.

For instance, I want to create a time series consisting of every day between 10/27/2017 and 11/27/2017 between 5pm UTC and 9pm UTC.

The following pseudocode fails but hopefully illustrates what I'm trying to achieve:

select *
from generate_series(
    tstzrange(timezone('UTC', '2017-10-27 17:00'), timezone('UTC', '2017-10-27 21:00'), '[]'),
    tstzrange(timezone('UTC', '2017-11-27 17:00'), timezone('UTC', '2017-11-27 21:00'), '[]'),
    '1 day'::interval) timeslots;

My desired results are (adjusting for timezone, naturally):

["2017-10-27 17:00:00-04","2017-10-27 21:00:00-04"]
["2017-10-28 17:00:00-04","2017-10-28 21:00:00-04"]
["2017-10-29 17:00:00-04","2017-10-29 21:00:00-04"]
                         .
                         .
                         .
["2017-11-25 17:00:00-04","2017-11-25 21:00:00-04"]
["2017-11-26 17:00:00-04","2017-11-26 21:00:00-04"]
["2017-11-27 17:00:00-04","2017-11-27 21:00:00-04"]

Is this possible in Postgresql, latest version?

Upvotes: 0

Views: 564

Answers (1)

Phylogenesis
Phylogenesis

Reputation: 7890

How about something like this:

select
    tstzrange(
        date + interval '17 hours',
        date + interval '21 hours'
    ) timeslots
from
    generate_series(
        '2017-10-27'::date,
        '2017-11-27',
        '1 day'
    ) date;

Upvotes: 3

Related Questions