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
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?
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.