Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones - Mailing list pgsql-general

From Steve Crawford
Subject Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones
Date
Msg-id CAEfWYyyWk4Zwpji4n1dsxUJ0Op7fc_ZwirqHYc+bzYA7w65J6A@mail.gmail.com
Whole thread Raw
In response to Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones  (Lincoln Swaine-Moore <lswainemoore@gmail.com>)
Responses Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones
List pgsql-general
...
Yeah, I'm definitely interested in keeping as much of the DST stuff outside my code as possible. I guess my concern is that I think there are other places where this database is being used in my codebase that may rely on the database time setting being UTC (or really, GMT, though I don't think there's a difference for these purposes). It would be best if all of my application's code declared its intentions about the time zone of the database connection before running its query, but I don't think that's a feasible change to make right now. That's what's motivating my pursuit of finding a way to write these queries without changing this setting, through appropriate casting and such.
...

 Really UTC (not that it matters for calculation purposes): https://en.wikipedia.org/wiki/Coordinated_Universal_Time

As to "other places" in your (or others) code, provided that you are storing and manipulating your data as timestamp WITH time zone (point in time), it can be inserted, reported, displayed, calculated, etc. in whatever time zone any client or piece of code prefers. But when displaying data around DST changes, fall-back in particular, the offset must be included in your output to disambiguate things like 01:30:00-07 from 01:30:00-08. Both are 1:30am but are one hour apart.

Cheers,
Steve

pgsql-general by date:

Previous
From: Lincoln Swaine-Moore
Date:
Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones
Next
From: Alban Hertroys
Date:
Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones