How to store an array of date ranges in Postgres?

Question

I am trying to build a schedule, I generate an array of objects on the client containing date ranges

[ 
    {start: "2020-07-06 0:0", end: "2020-07-10 23:59"}, 
    {start: "2020-07-13 0:0", end: "2020-07-17 23:59"} 
]

I have a column of type daterange[] what is the proper way to format this data to insert it into my table?

This is what I have so far:

INSERT INTO schedules(owner, name, dates) VALUES (
    1, 
    'work', 
    '{
         {[2020-07-06 0:0,2020-07-10 23:59]},
         {[2020-07-13 0:0,2020-07-17 23:59]}
    }'
)

GMB · Accepted Answer · 2020-07-15 00:43:37Z

4

I think you want:

insert into schedules(owner, name, dates) values (
    1, 
    'work', 
    array[ 
        '[2020-07-06, 2020-07-11)'::daterange, 
        '[2020-07-13, 2020-07-18)'::daterange 
    ]
);

Rationale:

you are using dateranges, so you cannot have time portions (for this, you would need tsrange instead); as your code stands, it seems like you want an inclusive lower bound and an exclusive upper bound (hence [ at the left side, and ) at the right side)
explicit casting is needed so Postgres can recognize the that array elements have the proper datatype (otherwise, they look like text)
then, you can surround the list of ranges with the array[] constructor

Demo on DB Fiddle:

owner | name | dates                                                
----: | :--- | :----------------------------------------------------
    1 | work | {"[2020-07-06,2020-07-11)","[2020-07-13,2020-07-18)"}

answered Jul 15, 2020 at 0:43

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ayn Over a year ago

Awesome! tsrange is what I needed. Do you mind explaining what an inclusive lower bound and exclusive upper bound is?

GMB Over a year ago

@Ayn: you can have a look at the relevant part of the documentation. Actually, I think that daterange is closer to what you want here (based on the fact that the time portions of your upper bould are 23:59)...

Ayn Over a year ago

Thanks! I see what you mean in this particular example but some dates might not include the whole day and might include partial hours. I think I am understanding things better now.

Collectives™ on Stack Overflow

How to store an array of date ranges in Postgres?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related