[orf]

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

[craigkerstiens]

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.

[1] http://www.craigkerstiens.com/2017/04/30/why-postgres-five-y...

[jldugger]

All you have to is just self-cite and say you won't belabor the point any further. ^_^

[slig]

They're are awesome and Django's ORM has built in support for it.

https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/...

[nthcolumn]

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)

[neuronexmachina]

I'm so sad range types are unsupported in AWS Redshift. :(

[scott_karana]

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?

[orf]

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

[mixmastamyk]

Maybe a chosen value such as "2100-01-01 00:00" could work.

[TheCoreh]

Do you know if they can be combined with the timetravel extension?

[orf]

No idea, maybe? You mean this[1]?

1. https://www.postgresql.org/docs/9.1/static/contrib-spi.html#...

[TheCoreh]

Yeah. Would be really cool to combine both fields in one to get a cleaner schema.