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

41–50 of 128 posts

Re: Working with time in Postgres

#41

Post by craigkerstiens »

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…

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

Re: Working with time in Postgres

#42

Post by 0x0 »

openasocket wrote: post #28
0x0 wrote: post #15

Earlier quoted context omitted.

Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.

How often do time zone rules change? That seems like a fairly rare event. If you're talking about daylight savings time, the date library you're using to convert local time to UTC should account for that.

This happens all the time, and sometimes even with just a few days heads-up. Here's a great example: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=457938

Re: Working with time in Postgres

#43

Post by manigandham »

openasocket wrote: post #35
manigandham wrote: post #11

Earlier quoted context omitted.

Timezones are more than just an offset.

Aren't timezones an offset of UTC by definition? I thought during daylight savings time a country is temporarily changing their timezone. Isn't that why we have EST and EDT: they're two different timezones?

No, time zones are a specific geo region that follows a certain time standard. The standard has a relative reference to UTC but that reference may change, either regularly or randomly.

Eastern Time Zone is a single time zone, that has different offsets depending on season, formally referred to as EDT and EST to make it easier to identify as daylight or standard references to UTC.

Even regions within the same time zone don't follow the standard exactly, so Panama does not observe daylight saving time while New York does. This is why we have even more granular settings used for calendars and dates.

Re: Working with time in Postgres

#45

Post by manigandham »

openasocket wrote: post #28
0x0 wrote: post #15

Earlier quoted context omitted.

Any future event (such as a meeting) can't be stored as UTC, because time zone rules may change between now and the event date, but the event still needs to happen 10am local time.

How often do time zone rules change? That seems like a fairly rare event. If you're talking about daylight savings time, the date library you're using to convert local time to UTC should account for that.

It doesnt have to be rules, time zones have relative and changing references against UTC, for example daylight saving time which is 2x per year in the US.

If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.

Re: Working with time in Postgres

#46

Post by [deleted] »

ender7 wrote: post #25

Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…

[deleted]

Re: Working with time in Postgres

#47

Post by flukus »

lacampbell wrote: post #10
deepsun wrote: post #8

Last time I checked, I couldn't store a datetime _with_ timezone. It was really strange that such a powerful database doesn't support storing full-ISO datetimes, like '2017-01-01T00:00:00Z'. Instead, it converted it to date-time-only instant, losing information of original timezone along the way. Sure, I could fetch it back using any timezone I want, but I really wanted to know the original timezone it was in.

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

While I agree with the other replies, using a smallint would assume all timezones are offset in hourly increments, which isn't the case.

Re: Working with time in Postgres

#48

Post by gshulegaard »

ender7 wrote: post #25

Every time I deviate from using unix milliseconds as my timestamps, I end up regretting it. If we use unix seconds, we get infinite bugs related to people forgetting to convert to millis when comparing against the current time. If we use Date objects, it's an even larger surface of potential bugs. Every Date interface I've ever seen makes it far too easy to accidentally create a relative time (i.e. anything that can'…

I have had good success working with Postgres timestamp without time zone.

Re: Working with time in Postgres

#49

Post by openasocket »

manigandham wrote: post #45
openasocket wrote: post #28

Earlier quoted context omitted.

How often do time zone rules change? That seems like a fairly rare event. If you're talking about daylight savings time, the date library you're using to convert local time to UTC should account for that.

It doesnt have to be rules, time zones have relative and changing references against UTC, for example daylight saving time which is 2x per year in the US. If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.

That doesn't sound right. If you have a local timestamp with the date and the time and convert it to UTC, shouldn't the datetime library look up the rules for this timezone and convert it correctly? Isn't that the whole point of having timezones be more than offsets?

Re: Working with time in Postgres

#50

Post by coldtea »

manigandham wrote: post #11
lacampbell wrote: post #10

Earlier quoted context omitted.

Would it be that much work to add a smallint field, that had the original UTC offset used for your time?

Timezones are more than just an offset.

Doesn't matter as far as the DB is concerned.

You either just store UTC and co, or you store it just as an datetime + offset.

100% of apps in production don't handle most of the timezone intricacies anyway and the sky hasn't fallen (heck, the sky hasn't even fallen for Y2K).

Post reply on HN