4 ms·
You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]! If you're using Postgres right now and have any columns lik
by orf 9y ago
You cannot have a post entitled "Working with time in Postgres" and fail to mention Range Types[1]!
If you're using Postgres right now and have any columns like start_* and end_* for anything (e.g numbers or dates), you need to stop what you are doing and use a range type. They are amazing. You can do things like a unique index that ensures there are no overlapping ranges, you can do efficient inclusion/exclusion indexing and much more.
Use them. I'm always surprised more people don't know about them.
1. https://www.postgresql.org/docs/9.6/static/rangetypes.html https://www.postgresql.org/docs/9.6/static/rangetypes.html
- craigkerstiens 9y agoYes! Range types should absolutely be in here. I had them as one of the top items in a recent post so thought it'd be a bit repetitive[1], but in retrospect, it should absolutely be here as well. [1] http://www.craigkerstiens.com/2017/04/30/why-postgres-five-years-later/ http://www.craigkerstiens.com/2017/04/30/why-postgres-five-y...
- jldugger 9y agoAll you have to is just self-cite and say you won't belabor the point any further. ^_^
- neuronexmachina 9y agoI'm so sad range types are unsupported in AWS Redshift. :(
- slig 9y agoThey're are awesome and Django's ORM has built in support for it. https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/fields/#range-fields https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/...
- TheCoreh 9y agoDo you know if they can be combined with the timetravel extension?
- nthcolumn 9y agoI idled here expecting something on time series data. The article whilst useful is incredibly thin, a one page blog post, which is fine but I'm not sure why it has lasted very long here? But thanks for that also, perhaps the thread will beget something more comprehensive. (Not volunteering)
- scott_karana 9y agoPractically speaking, how would you use these for noting starts and ends of long-running jobs, say? Would you set the interval starting time, but leave the end of the interval as "present"/infinity? And then update the end of the interval when the job finished? Wouldn't you also need to have a cleanup function to manually "close" intervals if the worker crashed and restarted?
- mixmastamyk 9y agoMaybe a chosen value such as "2100-01-01 00:00" could work.
- orf 9y agoI'm not sure to be honest, I would set the end as infinity I think. I wouldn't have the worker process handle this itself though, as you would need some form of cleanup. But you'd need the same with two individual columns