Working with time in Postgres • Hacker News Board

Re: Working with time in Postgres

#21

Post by piinbinary » Thu, Jun 08, 2017, 10:56 PM UTC

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.

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)

Re: Working with time in Postgres

#22

Post by Nullabillity » Thu, Jun 08, 2017, 10:58 PM UTC

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.

Re: Working with time in Postgres

#23

Post by rainbowliquor » Thu, Jun 08, 2017, 11:00 PM UTC

extesy wrote: post #18

rainbowliquor wrote: post #14

date_trunc() is one way but to_char is even better as you can get the resulting output to something nicer. Doing: SELECT DATE_TRUNC('week', CURRENT_TIMESTAMP); gives: 2017-06-05 00:00:00+00 vs: SELECT TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-WW"wk"'); gives: 2017-23wk

It's not the same: date_trunc returns timestamp, to_char returns a string.

True but in the use case of OPs example. In the article OP says "So if we wanted to find the count of users that signed up per week:" which "2017-06-05 00:00:00+00" isn't a week, it's a date (with a time stamp as well which isn't pertinent) which happens to be the beginning of a week. Using TO_CHAR() with a format string makes it more legible and more recognizable.

Re: Working with time in Postgres

#24

Post by Nullabillity » Thu, Jun 08, 2017, 11:00 PM 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.

Not really, that still breaks when you have participants from multiple time zones.

Re: Working with time in Postgres

#25

Post by ender7 » Thu, Jun 08, 2017, 11:01 PM UTC

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't be mapped unambiguously to a single unix millis timestamp. Usually means a datetime that defaults to the current timezone). Does anyone have a preferred method that avoids these pitfalls?

At the end of the day I always come back to "solution with lots of possible bugs" or "unix millis everywhere". And I always choose the latter. It means we can't use nice date features in a lot of databases, but...eh? They've never seemed worth it.

Re: Working with time in Postgres

#26

Post by gtrubetskoy » Thu, Jun 08, 2017, 11:02 PM UTC

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, probably because it uses Monday as the first day of the week.

Re: Working with time in Postgres

#27

Post by davidw » Thu, Jun 08, 2017, 11:04 PM UTC

"I was in favour of space exploration until I realised what it'd mean for date time libraries" -- Joe Wright

https://twitter.com/joe_jag/status/510048646482894848?lang=e...

Re: Working with time in Postgres

#28

Post by openasocket » Thu, Jun 08, 2017, 11:05 PM 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.

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.

Re: Working with time in Postgres

#29

Post by icebraining » Thu, Jun 08, 2017, 11:08 PM UTC

Nullabillity wrote: post #24

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.

Not really, that still breaks when you have participants from multiple time zones.

It doesn't "break"; yes, you have to change the local time for the other time zones, but the point is that some times you have to use a specific reference time zone, so you got to save it.

Re: Working with time in Postgres

#30

Post by waffle_ss » Thu, Jun 08, 2017, 11:10 PM UTC

piinbinary wrote: post #21

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.

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