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

81–90 of 128 posts

Re: Working with time in Postgres

#81

Post by elmigranto »

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…

> Sunday, which in some/most? countries is the first day of the week.

Just like imperial system, only a couple of weirdos do that.

Re: Working with time in Postgres

#82

Post by talaketu »

zAy0LfpBZLC8mAC wrote: post #74
Nullabillity wrote: post #22

Earlier quoted context omitted.

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

Because there actually are times that are specified in terms of local time and are not fixed to a specific timezone. Take a birthday, for a trivial example: The span of time in UTC that corresponds to someone's birthday depends on their location at the time.

true that anniversaries are not fixed to timezone, but these are not timestamps, and generally not even dates. Just recurrences specified by month, day (or maybe time of day eg Armistice Day observed at 11:00am).

Better example: Typically an events schedule should specify destination timezone when registering an event, so that if regulations on local timezone change, scheduled event times remain valid.

Re: Working with time in Postgres

#83

Post by deepsun »

_jal wrote: post #64
openasocket wrote: post #38

Earlier quoted context omitted.

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

Don't even mention how to calculate number of weeks in a year! It's often needed in real world programming for business reports, yet Joda-time library still doesn't support weeks not starting on Monday (like USA-ones) for that.

Re: Working with time in Postgres

#84

Post by TheCoreh »

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…

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

Re: Working with time in Postgres

#85

Post by yen223 »

fnord123 wrote: post #40
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.

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

Re: Working with time in Postgres

#86

Post by orf »

TheCoreh wrote: post #84
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…

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

Re: Working with time in Postgres

#87

Post by [deleted] »

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

[deleted]

Re: Working with time in Postgres

#88

Post by mirekrusin »

timestamptz doesn't embed timezone, it stores it as utc without any timezone information.

timestamp does the same - stores value without timezone information.

the difference is with writing/reading those values where timestamptz behaves as you'd expect and timestamp ignores timezone information.

timestamptz - gives you the thing that exists: unique point in time, ie. if person A in australia and person B in europe hits the red button at the same time - timestamptz will have the same value, regardless of the fact that those two timestamp strings had different representations.

timestamp - gives you this local view of time: when person A in australia wakes up 6am to work and person B in europe wakes up at 6am to work - they will hit the snooze button and it will create same value in the database - even though those events happened hours apart.

in both cases you'd have to store timezone in separate column if you want to extract information on which timezone the timestamp was generated in. let me repeat - both cases loose information on timezone. they just do it in different way - timestamp by ignoring it completely and timestamptz by mapping it correctly to unix epoch.

Post reply on HN