Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Lincoln Swaine-Moore <lswainemoore(at)gmail(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones
Date: 2023-10-04 19:00:35
Message-ID: CAEfWYyyWk4Zwpji4n1dsxUJ0Op7fc_ZwirqHYc+bzYA7w65J6A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2023-10-04 19:06:34 Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones
Previous Message Lincoln Swaine-Moore 2023-10-04 17:45:33 Re: Strategies for converting UTC data to local windows for arbitrary resolutions and timezones