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 9:20 AM dn via Python-list
<python-list at python.org> wrote:
>
> On 01/09/2021 09.13, Chris Angelico wrote:
> > 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.
>
> OK, I admit it: I am so lazy that I don't use my fingers (nor my toes!)
> but expect my poor, over-worked (and under-paid) computer to calculate
> it all for me...
>
>
> I should have split the earlier explanation of two calculations, more
> clearly:
>
> Devicat can declare the start as "6pm" ("localisation")
> and state that the time-zone is UTC-3
> - or as @MRAB suggested, translate it to "21:00 UTC"
> ("internationalisation")
>
> You (@Chris) then perform the second-half calculation, by adjusting the
> UTC-value to your time-zone.
>
> - and were I to attend, would personalise ("localise") the time
> similarly - but using my locality's (different) UTC-offset.

Gotcha gotcha. Unfortunately that, while theoretically easier, is not
correct; she streams at 6pm every week, which means that the UTC time
is *different* in June and December.

> I agree, the idea of 'Summer Time' is a thorough pain - even more-so
> when the host publishes in local-time but forgets that there will be a
> "spring forward" or "fall back" between the time of publication and the
> meeting-date!

Right. Which is basically guaranteed when it's a recurring event.

> Accordingly, when the Néo-Brunswickoise publishes "6pm", all the locals
> will be happy.
>
> If she adds UTC, or the locally-applicable UTC-offset (for Summer-Time,
> or not), the international community can make their own and personal
> arrangements, without winding-through the opaque and arcane
> seasonal-adjustments described!
>
> Plus the virtue(?) of using International Standards!

She'd have to either specify two different UTC times, or two different
local times. Instead, it's safer to just have her publish one local
time (since that's how she's defining things), and let us all convert
directly to our own timezones every time.

> The "New Brunswick" in Canada is quite close to the "New Brunswick" in
> New Jersey (USA) - in physical distance, but the two are in distinct
> time-zones. (Yes, you did say "CA", but easily 'missed' - by author and
> reader alike)

Fair point. I'm not familiar with New Brunswick, New Jersey, USA, but
I know that there are a lot of those kinds of collisions.

> > 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.
>
> Those are the better publishers.
>
> Too many do not - have yet to figure-out the implications of the words
> behind "www". Sigh!
>
> (have been glad to hear others grumping about the expression of times -
> it's not just me then!)

Yup yup. That's why I keep trying to encourage people to post IANA
city names where possible. In a lot of cases, I can just ask someone
"So that's the same as Halifax time?" or "You mean 3pm New York time?"
and it's fine; they don't need to know why I chose some particular
city, but people who live in an area will know what the major nearby
cities are.

> When (ordinary) folk try to explain it to me, they talk about 'the
> farmers' and agriculture. Every dairy farmer I've spoken with says it's
> all too-stupid - instead of milking at 0500, they start at 4am - no cow
> wears a watch!

Yeah, I have no idea where the "farmers" explanation came from.
Growing up, we had some friends in countrified areas, including a
sheep farmer who showed us a ton of stuff about how shearing worked,
how moronic sheep are, and how farming life actually operates. It's
not uncommon for such people to have two clocks: Government Time and
Sun Time. Like you say, no cow wears a watch!

> "Jet lag" is one thing. Switching hemispheres I find dislocating. Having
> one's 'clock' switched by amounts other than round-hour amounts is just
> plain cruel!

I learned to be completely flexible about things, and just slide into
whatever clock the destination has. But it does get very confusing
when you're transiting through some airport and your watch is wrong by
a weird amount...

> PS I think my (Linux) computer is basically set to UTC. The desktop
> performs localisation. Then I added a 'widget' which extends the usual
> clock to give World Times. Failing that, there are numerous web-sites
> which offer time-translations or clocks showing times at chosen locales.

Yes, that's how Linux systems run. I believe Windows always sets the
battery-backed clock in local time though, making dual-booting kinda
finicky.

> Python's (and *nix - but not MS-Win) times are managed through the
> "tzdb". This was recently updated/upgraded with "PEP 615 -- Support for
> the IANA Time Zone Database in the Standard Library":
> https://www.python.org/dev/peps/pep-0615/ (implemented in Python 3.9)

Not sure what you mean by "tzdb" but I presume that's tzdata?
Sometimes called the Olson database?

The change in 3.9 is mainly to make tzdata more generally available,
instead of depending on installing it from PyPI. But whatever your way
of getting it, that's the database to look for.

So many insanities.

ChrisA