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

31–40 of 128 posts

Re: Working with time in Postgres

#31

Post by icebraining »

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.

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.

Re: Working with time in Postgres

#32

Post by jontro »

gtrubetskoy wrote: post #26

The week example is a tad misleading, 2017-01-01 is a Sunday, which in some/most? countries is the first day of the week. If the date were 2016-01-01 and you compared it with what week Postgres thinks it is, you'd get: SELECT date_part('week', '2016-01-01'::date); date_part ----------- 53 (1 row) This is because 2016-01-01 is still the 53rd week of 2015. Edit: Actually, 2017-01-01 is week 52 according to Postgres, pr…

Probably because it's using ISO-8601 week numbers. https://en.wikipedia.org/wiki/ISO_week_date

Re: Working with time in Postgres

#33

Post by luhn »

I love Postgres' time handling, even more so whenever I have to handcraft time-based queries in other databases, like MongoDB (which is more often than I'd like).

Some things the author didn't mention that I like:

* Timestamp with time zone string parsing: '2013-06-27 13:15:00 US/Pacific'::timestamptz

* Timezone-aware to timezone-naive conversion (or vice versa): mytztime AT TIME ZONE 'US/Pacific'

* I haven't used tstzrange yet, but it looks pretty powerful.

Re: Working with time in Postgres

#34

Post by waffle_ss »

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

The problem with Unix milliseconds is it's actually not always increasing thanks to leap seconds. A positive leap second will result in the fractional part of the last second of the day going up to .999..., then resetting to .000... over again.

https://en.wikipedia.org/wiki/Unix_time#Leap_seconds

Re: Working with time in Postgres

#35

Post by openasocket »

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.

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?

Re: Working with time in Postgres

#36

Post by 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

Re: Working with time in Postgres

#37

Post by akira2501 »

waffle_ss wrote: post #34
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'…

The problem with Unix milliseconds is it's actually not always increasing thanks to leap seconds. A positive leap second will result in the fractional part of the last second of the day going up to .999..., then resetting to .000... over again. https://en.wikipedia.org/wiki/Unix_time#Leap_seconds

There's always TAI64.

http://dyscour.se/post/12679668746/using-tai64-for-logging

Re: Working with time in Postgres

#38

Post by openasocket »

icebraining wrote: post #31
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.

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

Re: Working with time in Postgres

#39

Post by [deleted] »

waffle_ss wrote: post #30
piinbinary wrote: post #21

Earlier quoted context omitted.

You can do that with the 'TIMESTAMP WITH TIME ZONE' column type. https://www.postgresql.org/docs/current/static/datatype-date... (Edit: or not. See child comment)

Nope, that doesn't store the time zone, it just uses time zone information before flattening to UTC time. https://stackoverflow.com/a/9576170/215168

[deleted]

Re: Working with time in Postgres

#40

Post by fnord123 »

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.

That is correct behaviour. Time zone information is a presentation detail.
Post reply on HN