The sqlite3 timestamp conversion between unixepoch and localtime can't be done according to the timezone setting on the machine automatically.

[Chris Angelico]

On Wed, Sep 1, 2021 at 6:38 AM dn via Python-list
<python-list at python.org> wrote:
> > Yeah. I do recommend making good use of the IANA tzinfo database
> > though (especially since Python 3.9 made that a bit easier to access),
> > as it's usually easier to get people to tell you what city/state
> > they're in, rather than whether daylight time will be active or not.
> > (It might take a little bit of translation to figure out that, for
> > instance, New Brunswick CA is America/Halifax, but that's not too hard
> > usually.) Letting tzinfo do all the work means you don't have to fret
> > about anyone's daylight saving transition dates, or whether they've
> > decided to change their clocks by half an hour to be different from
> > Japan's clocks, or to have DST not applicable during Ramadan, or to
> > have double DST, or double-negative DST. And yes, those are all real,
> > because you can't make up anything as insane as actual clock politics.
>
> So, given that it is a NUMERIC calculation, dispense with "New Brunswick
> CA is America/Halifax"; and avoid "Atlantic Time", "Atlantic Standard
> Time", "Atlantic Daylight Time", "AT", "ADT", or "AST", and express the
> time numerically: "17:00-3"
>
> Given that, someone at UTC-4 knows that his/her rendez-vous will be
> "1600", and I can figure it to be "0800" for me:
>
>         1700 - -3 = 20:00 (to calculate UTC), then UTC-4 = 16:00
> and
>         1700 - -3 = 20:00 (to calculate UTC), then UTC+12 = 32:00,
>                                               rounding to 24hrs: 08:00
>                                               (the next day)

No, that's not reliable... because of that abomination called Daylight
Saving Time. Since I used New Brunswick, and since she's just gone
online, I'll use a specific example:

DeviCat livestreams at 6pm every Tuesday (and other times, but I'm
going to focus on a weekly event here). Since she lives in NB, Canada,
she defines that time by what IANA refers to as America/Halifax.

I want to be there at the start of each stream, since I'm one of her
moderators. But I live in a suburb of Melbourne - my clock shows what
IANA calls Australia/Melbourne.

To turn this into a purely mathematical calculation, you have to know
exactly when she will go on or off DST, and when I will go on or off.
Trying to turn it into an offset is going to fail badly as soon as you
talk about "next Tuesday" and one of us is shifting DST this weekend.

That's why it's better to let Python (or something) handle the whole
thing. Don't concern yourself with exactly what the hour differences
are, or which way DST is going, or anything; just convert Halifax time
to Melbourne time.

> For many of us, the mental-calculations are relatively easy to manage.
> For Python the code is trivial. Computation is easier than terminology
> 'translation' (particularly when one has to research the terms first!
> - did you know what "ADT" meant?)

I asked DeviCat what country and province ("state" in other regions)
she lived in, and then confirmed with her that Halifax time was what
her clock showed. The term "ADT" was never relevant.

In a lot of situations, you don't even need to ask the human - you can
let the web browser or desktop app report the timezone. The app can
say something like "In order to schedule this event, <X> will need to
know your time zone. Is that okay?" and then send the IANA timezone
name.

> Teasing @Chris: I'm not sure why it should be amusing that two entities
> called 'Ireland' should have different time-zones (pot?kettle) - after
> all, does "Western Australia" use the same time-zone as "South
> Australia"? For that matter, the same as the bulk of the Australian
> population?

Western Australia uses Australia/Perth timezone, and South Australia
uses Australia/Adelaide. They're different base times from the east
coast where I am by two hours, and half an hour, respectively; and
they have different DST rules.

On the east coast, we all have the same winter time, but in summer,
Melbourne, Sydney, and Hobart move clocks forward, but Brisbane
doesn't.

> The time-zone which perplexes me most, is India. This because it is not
> only a different hour, but also requires a 30-minute off-set - operating
> at UTC+5:30!

Yup, we got that too... Adelaide is half an hour back from Melbourne
(UTC+9:30). But it gets worse. Kathmandu is on a quarter hour. And the
Chatham Islands (part of New Zealand) are 12:45 ahead of UTC in
winter, and then they add an hour of DST in summer, putting them at
UTC+13:45.

> Fortunately, like China, the entire country (officially) operates in the
> same time-zone. Accordingly, there is less consideration about
> working-out what time it is in Pune cf Kolkata, than between (say) San
> Francisco and Denver - although they are in the same country, are they
> in the same time-zone, or not?
> (they aren't!)

That would be convenient for working within China, but on the flip
side, it means that geographically-nearby locations can have vastly
different clocks. Oh, and one of those nearby locations is Nepal. So
it's possible to step across a border and have your clock change from
3AM to 5:15AM. Yeah that's gotta be fun!

But ultimately, it all just means that timezones are too hard for
humans to handle, and we MUST handle them using IANA's database. It is
the only way.

ChrisA