How to store an event is happening from 2017-08-20 to 2017-08-24? What data type?

Home / sql / How to store an event is happening from 2017-08-20 to 2017-08-24? What data type?

Question:
Say an event is happening from 2017-08-20 to 2017-08-24.

I’ve actually seen most APIs store and return this like:date_start: date
date_end: date
However it might just be the case that these APIs are using MongoDB or some other NoSQL without types.

Should I be doing the above or using the data types interval (not sure of an example of what it’s for) or daterange (seems likely) instead?


Answer:
Range Types might be the best – either daterange, tsrange or tstzrange

8.17.1. Built-in Range Types

PostgreSQL comes with the following built-in range types:
int4range — Range of integer

int8range — Range of bigint

numrange — Range of numeric

tsrange — Range of timestamp without time zone

tstzrange — Range of timestamp with time zone

daterange — Range of date

GiST and SP-GiST indexes can be created for table columns of range types.
You can create cxclusion constraints for ranges which allow the specification of constraints such as "non-overlapping" on a range type.
There are many built-in functions and operators available for range types.

The only drawback is that ranges are not a part of SQL standard and they are not supported by other databases, you may have troubles if you decide some day to move to another RDBMS.
Read more

Leave a Reply

Your email address will not be published. Required fields are marked *