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.
Yes! 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.
I 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)
Practically 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?
I'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
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