Working with time in Postgres • Hacker News Board

Re: Working with time in Postgres

#61

Post by Arnavion » Fri, Jun 09, 2017, 12:50 AM UTC

manigandham wrote: post #58

Arnavion wrote: post #53

Earlier quoted context omitted.

If the software converts to UTC it should be using the expected local timezone on the target date (4pm Tuesday), not the current timezone. So this will only be a problem if the TZ tables for the future are not correct (which happens, as noted in sibling comments, but is not as widespread as every DST event like your comment seems to imply).

> expected local timezone I'm not sure what this means. Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp. UTC version of that timestamp is optional (maybe for easier calculations in the database), but you must have the local timestamp to convert correctly. Local TZ => UTC is a formula that is not constant. You cannot store the output of this form…

>I'm not sure what this means.

The timezone that the user will experience on that date.

>Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp.

Correct. I did not disagree with this.

>Local TZ -> UTC is a formula that is not constant. You cannot store the output of this formula and expect to reverse and get back the original input precisely because of the changing formula.

Also correct. But as I said, your original comment implied this is a concern for all DST events which is not true. It is only a concern when the time was converted using some DST assumption and then that assumption is invalidated (eg the software decided to convert "4pm Tuesday local" to UTC assuming the timezone on Tuesday will be UTC+7, but the DST change happens to come early this year and in fact it's UTC+8 on Tuesday).

Regardless, I agree that taking this risk is not correct. Such a time should not be converted to UTC to begin with. Better to store the original local time.

Re: Working with time in Postgres

#62

Post by andrewfromx » Fri, Jun 09, 2017, 12:52 AM UTC

Have to mention https://github.com/activityclub/pointspaced in a hn post about time. I use psd for so many queries now vs sql.

Re: Working with time in Postgres

#63

Post by slig » Fri, Jun 09, 2017, 12:59 AM UTC

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…

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

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

Re: Working with time in Postgres

#64

Post by _jal » Fri, Jun 09, 2017, 1:07 AM UTC

openasocket wrote: post #38

icebraining wrote: post #31

Earlier quoted context omitted.

There's always changes happening. Just this year, Southern Chile changed its summer time zone offset, Haiti started observing DST and Mongolia stopped doing that.

Seriously?! Man, dealing with time can just never be simple...

Time, to me, is the canonical example of "things that people think should be easy, aren't". It has everything - complex, constantly changing "business" rules, exceptions generated essentially randomly by a shadowy cabal[1] nobody's ever heard of, "impossible" situations like the 11 days that never happened and other technically arbitrary calendar edits[2], multiple silly base conversions, really weird rules for picking certain dates[3], lots of opportunities for making fencepost errors, and lots of other things.

Which leads me to my rule with time programming: Never fail to use a solid library, unless you're unfortunate enough to be writing one.

This[4] is a great, necessary but not sufficient book if you have to do that.

[1] https://en.wikipedia.org/wiki/International_Earth_Rotation_a...

[2] For example, https://en.wikipedia.org/wiki/Swedish_calendar

[3] https://en.wikipedia.org/wiki/Easter#Computations

[4] https://www.amazon.com/Calendrical-Calculations-Nachum-Dersh...

Re: Working with time in Postgres

#65

Post by baddox » Fri, Jun 09, 2017, 1:13 AM UTC

mason55 wrote: post #56

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

When you talk about Date objects, do you mean datetime? Because a Date with no time compenent has tons and tons of real world uses that a unix time stamp would be inappropriate for.

Can you provide examples? It only seems useful to me for display purposes if you know you'll never need to worry about any specific local time zone (or if you will, then you know what time zone that is and know how to deal with it).

Re: Working with time in Postgres

#66

Post by baddox » Fri, Jun 09, 2017, 1:14 AM UTC

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 don't have much of a preference for milliseconds or seconds, but yeah, Unix time stamps are nearly always the way to go.

Re: Working with time in Postgres

#67

Post by baddox » Fri, Jun 09, 2017, 1:15 AM UTC

Nullabillity wrote: post #22

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.

When would you want use something else than UTC for business logic? Time zones (and their related nonsense) should be a view-layer concern.

Perhaps because there's not always a one to one relation between a time with zone and a unix time stamp.

Re: Working with time in Postgres

#68

Post by yawaramin » Fri, Jun 09, 2017, 1:16 AM UTC

cuu508 wrote: post #3

Another tip, if you work with per-user custom timezones, then "SELECT some_date AT TIME ZONE %(users_timezone)s" is also sometimes useful and needed. Normally you would want to receive timezone-aware timestamps from the database, and format them in user's timezone at display time--perhaps in a template. But, if you're e.g. aggregating data for a day-over-day or month-over-month report, then the conversion to naive da…

I'm a bit of a n00b at date handling. What's the benefit to communicating with TZ-aware timestamps versus with TZ-less timestamps with the shared understanding that they're always at UTC? With the latter approach I can also convert to my local timestamp for display.

Re: Working with time in Postgres

#69

Post by kclay » Fri, Jun 09, 2017, 1:18 AM UTC

One thing I learned about working with postgres and time is that the timezone is based on the timezone of the connecting sever and not actual sever. I can't tell you how long it took me to debug code due to my workstation being at cst but severs in est and then storing dates as utc. Bundle that with caculating upcoming birthdays within 15-30 days before and leap years.

Yeah I didn't like it one bit. Sorta reminds me when I had to develop a Grantt chart component in flex for a client, so many problems with dates.

Re: Working with time in Postgres

#70

Post by baddox » Fri, Jun 09, 2017, 1:18 AM UTC

0x0 wrote: post #15

lacampbell wrote: post #12

Earlier quoted context omitted.

True. I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.

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.

I don't think that's necessarily true. If you really care about that edge case, then you would need to ask whether the local time should change if the local time zone's UTC unpredictably changes. I don't think you can make a safe assumption either way.