Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Working with time in Postgres

craigkerstiens.com

Post reply on HN
Print view View original

121–128 of 128 posts

Re: Working with time in Postgres

#121

Post by scott_karana »

orf wrote: post #36

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 ind…

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?

Re: Working with time in Postgres

#122

Post by mixmastamyk »

scott_karana wrote: post #121
orf wrote: post #36

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 ind…

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?

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

Re: Working with time in Postgres

#123

Post by greggyb »

solidsnack9000 wrote: post #117
greggyb wrote: post #92

I never see discussion of fiscal calendars in these sorts of threads and articles. Whenever I read these, all I see is something that would fall apart as soon as approximately half of my clients look at it. We have found that it is more annoying to keep track of the behavior of the library and of a home-grown date dimension. In my organization, we tend to use a standardized pattern that can handle arbitrary calendars…

Do you guys use a custom date type, then?

No. Dates are dates. Everyone can agree when a specific day exists. It's all about grouping.

Dates have attributes that group them together. "Month" is an attribute that you're familiar with. "Fiscal Period" can take on many specific definitions but it is analogous to "Month".

Those two concepts share a lot of properties. They each collect a series of contiguous dates. Each is adjacent to a similar grouping that falls sequentially and the last date that exists in one is one day prior to the first day that exists in the next. Each falls within a larger category like "Year" or "Fiscal Year".

Year+Period forms a composite key for a period. We can also assign a monotonically increasing field that increments by one with each subsequent period. That field allows simple arithmetic to shift forward and backward. We typically call this attribute PeriodIndex or PeriodSequential. I'll abbreviate to PI here.

If you have a reference PI, you can always find the immediate prior period by subtracting one from the reference. We can assign these for any grain of time period. We typically see Week, Period, Quarter, Semester, Year.

This is the baseline of how we handle dates. There are plenty of utility fields we'll maintain for specific time-based needs, but it's all sugar on top of that.

Re: Working with time in Postgres

#124

Post by mixmastamyk »

mrtbld wrote: post #98

> Postgres has two types of timestamps. It has a generic timestamp and one with timezone embedded in it. That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a disp…

Thanks. If I store all datetimes from my app in UTC, with end users in more than one timezone, which type should I use?

Re: Working with time in Postgres

#125

Post by mrtbld »

mixmastamyk wrote: post #124
mrtbld wrote: post #98

> Postgres has two types of timestamps. It has a generic timestamp and one with timezone embedded in it. That's not correct, timestamptz doesn't have a timezone embedded in it. It's just that it's timezone-aware. A timestamptz corresponds to a universal point in time that have many human reprensentations, one for each timezone. psql uses the default timezone of the postgres instance to convert a timestamptz to a disp…

Thanks. If I store all datetimes from my app in UTC, with end users in more than one timezone, which type should I use?

Well I would use timestamptz, using user's timezone only to convert for display. Use cases for timestamp are very limited.

Just make sure you include a timezone info in string representations in your SQL queries. For example '2000-01-01T00:00:00Z' where Z stands for UTC. Otherwise that would insert a timestamp into a timestamptz column, in which case postgres uses local timezone setting for conversion, implicitly; this is not what you want.

See http://phili.pe/posts/timestamps-and-time-zones-in-postgresq...

Also you should use an equivalent type in you app, i.e. python datetime with tzinfo or JS Date. And beware of UTC offsets: they can't handle DST. Python pytz and JS moment-timezone provide DST-aware timezone info (which is built-in in postgres).

Edit: if you can rely on your users system time for display that's even better because you wouldn't have to explicitly deal with those DST-aware timezone info.

Re: Working with time in Postgres

#126

Post by pvaldes »

I understand by the discussion that if you want to have a field with only four qualitative categories (0, 1, 2, 3 with zero meaning "none" and 3 being "very much") you could use a numrange or int4range for example instead the standard integer type. Interesting. Apart of being much more restrictive in the allowed input, are other advantages (less memory?) or cons (possible portability problems?) that we should be aware of?

Footnote:

> Here’s just a few examples of things you could do with interals:

The author of the article could want to fix the 'interals' typo in the text.

Re: Working with time in Postgres

#127

Post by TheCoreh »

orf wrote: post #86
TheCoreh wrote: post #84

Earlier quoted context omitted.

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

No idea, maybe? You mean this[1]? 1. https://www.postgresql.org/docs/9.1/static/contrib-spi.html#...

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

Re: Working with time in Postgres

#128

Post by orf »

scott_karana wrote: post #121
orf wrote: post #36

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 ind…

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

Post reply on HN