Live data from Hacker News

Working with time in Postgres

craigkerstiens.com

101–110 of 128 posts

Re: Working with time in Postgres

#101
post #85

Earlier quoted context omitted.

That is correct behaviour. Time zone information is a presentation detail.

Not entirely. Thanks to daylight-savings, you need time zone information to properly calculate lengths of timespans, e.g. for daily recurrences

Yes, if you are creating appointments in a calendar program and want to have a daily meeting at 3pm and you are in a jurisdiction with DST, then you will need some TZ info so it knows when to wiggle the time by an hour.

But in this case, there's a lot of other information you want to store: first date in the series, repetition frequency, time of the appointment, location (e.g. "Europe/London" as opposed to "GMT" or "UTC+0" which would not be adequate). Basically, you're not so much storing a series of dates as storing a thunk for generating dates.

Re: Working with time in Postgres

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

Standard python date library does not allow to do any sorts of operations between TZ-aware and TZ-unaware dates. You're expected to explicitly convert between the two.

Django postgresql adapter will aggressively show warnings for all of the cases where you're trying to insert a TZ-unaware date into a TZ-aware column.

Is this that hard to reproduce?

Re: Working with time in Postgres

#103
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 would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in La…

> If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time.

Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.

Re: Working with time in Postgres

#104

Fantastic article as usual. One correction: the literal for UTC 00:00:00 00:00:00 is 'allballs', not 'allsballs' as mentioned. I know this because it made me giggle when I first discovered it, and it subsequently became an immature joke around the office for a day or two afterwards.

[deleted]

Re: Working with time in Postgres

#105

Earlier quoted context omitted.

I would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in La…

> If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.

It depends on the application semantics, for example if I scheduled a Breakfast for 08:00 in Las Vegas, but then moved the event to Chicago, I wouldn't want the time to be auto-converted to 06:00, (or whatever is appropriate given DST), because that's a silly time to have breakfast.

Re: Working with time in Postgres

#106
post #79

Earlier quoted context omitted.

Just general calendar arithmetic. There are tons of cases where you need to do stuff like calculate "one month before", "one month after", "beginning of the year", ... --that's a mess with unix timestamps, but trivial with a good calendar library (or postgres, for that matter).

I agree with you (I don't use unix timestamps), but if you're using a good calendar library you can certainly get the result as a unix timestamp.

Well, yes, you can convert on input and/or output from/to unix timestamps, sure (and you can with postgres, too), but the point is that you have to convert first, you can't do date arithmetic on unix timestamps directly (because there is no fixed relation between unix timestamps and days).

Re: Working with time in Postgres

#107

Earlier quoted context omitted.

I would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in La…

> If you have an event at 10am in Las Vegas, moving it to Chicago shouldn't suddenly change the start time. Does this come up a lot? Moving from Las Vegas to Chicago would involve much more than just being aware of the time zone change.

Maybe here's an example that's more useful: Some years ago, I worked on an in-house IaaS platform that included several workflow modules for the sysadmins (alert dispatching, time-tracking, etc.).

One of those components was a scheduler for one-time and recurring tasks. For example, when you delete a system, you set a timer for 14 days to have the system remind you (via e-mail or by issuing an alert into the queue) to delete the system's backup. There were also a lot of recurring tasks that needed to be performed daily or weekly. Now if you have a task thats configured daily at 9 AM, it's tempting to implement the timestamp series as

  while (true) { timestamp += 86400; }
And indeed, that's how it was done in the existing code. But that means that once DST starts or ends, your daily-at-9-AM-task suddenly happens at 8 AM or 10 AM instead. Whether that's a problem depends on the type of task and how the sysadmins organize their work. And then there's the monthly recurrence, which is even messier with plain timestamps.

I cannot recall all details anymore, but I definitely remember that twice a year, after each DST change, someone would go through the list of recurring tasks, and move the times forward (or backward) by one hour manually.

EDIT: Maybe the simplest (though not easiest) solution to the irregular month lengths would be to attach giant thrusters to Earth and push it away from the sun a bit, so that our year is 366 days instead of 365 long. Then we make a calendar with 6 months per 61 days. As a bonus, it would reverse some of the effects of global warming. (Alternatively, go to 368 days and have 16 months with 23 days each.)

Re: Working with time in Postgres

#108
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 would say: Think carefully about what kind of 'time' you are trying to represent. Instant s like unixtime are common in many problem domains, but there are plenty of situations where other choices are appropriate. For example, I wrote an event registration marketplace some time ago. You might think "start time for event" would naturally fit as a unix timestamp, but it's a mistake. If you have an event at 10am in La…

Unix timestamps are bound to UTC[1], so it would never be correct to have a timestamp represent number of seconds since 1970 _somewhere_. You would have to convert the timezone for any location you want it to be relative to.

[1] https://en.m.wikipedia.org/wiki/Unix_time

Re: Working with time in Postgres

#109
post #99

Earlier quoted context omitted.

No, I mean you make the appointment for December 3rd at 10:00am in Eastern Time (ET), and then you convert it to UTC to put into the database. Your local->UTC conversion library looks at the date and notices that this occurs during Daylight Savings Time for ET, and uses the correct offset when converting. Unless the rules about DST change between now and that meeting this is completely correct, you don't need to stor…

That was my original point. The rules about DST (or even just the base UTC offset) may change between now and the meeting unpredictably according to the whims of the local government, and this happens all the time.

Oh yeah I totally get that, but my point was the only problem is from rule changes, not from regularly scheduled DST events.

Re: Working with time in Postgres

#110
post #50

Earlier quoted context omitted.

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

Timezones have more information and are a mini-database in themselves, which is why most database include the information and have timezone capable types. Offsets as simple numbers are not usable in any real calculation.

100% of apps? You must not know what timezone intricacies are then or just how much effort is spent to make sure time itself is properly handled, especially in any major application that has global users.

Post reply on HN