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

[dn]

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.


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!

[the song says "Summer Time, and the living is easy". Unfortunately,
Ella Fitzgerald isn't here to wrap her head around the problem, so I
guess it's up to us...]

Once again, it is a localisation/internationalisation problem. Why would
you/I know, if and when, New Brunswick moves in/out of Summer Time? As
described, it is another 'big ball of mud'.

Far better to have the local deal with 'local issues', and for us to
handle our own (time-zone) problems!
(why should Devicat concern herself with MLB-based issues, or NZST, or
wherever and etc?)

[BTW: welcome to the first day of 'summer'!]


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!


>> 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.

Ah, but we are back to the original complaint about "CST" or "China 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)

Confusing the two New Brunswick-s would make you late-to-the-party!


> 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!)


>> 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.

Did I say that "Summer Time" is a pain?

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!

All this extra daylight is not good anyway - it fades the curtains more
quickly!

Living in the tropics I can't say I ever missed the sleep-disturbances
caused by Summer time!


>> 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.

...the pain keeps getting worse...


>> 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!

Been there. Done that. It is!
(not that specific border-crossing)

"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!


> 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.

I had (still have) a 'digi-ana' watch. It shows local time using the
(analog) hands, and keeps 'home time' as a (smaller) digital display.

Similarly, a travel alarm clock with a rotational-switch on the front,
allowing the time (and/or the alarm time) to be set and displayed using
any time-zone (not counting the half- and quarter-hour off-sets. Grrr!)


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.


The Internet 'standard' which "defines a date and time format for use in
Internet protocols" is RFC3339:
https://datatracker.ietf.org/doc/html/rfc3339
It describes itself as "a profile of the ISO 8601 standard"

ISO do not publish their standards on the web (nor for $free)
Wikipedia has good coverage of ISO 8601, starting at
https://en.wikipedia.org/wiki/ISO_8601

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)

(there are other (accepted and implemented) PEPs dealing with 'time', eg
improving accuracy to parts of a second, dealing with the "fold"
('losing' or 'duplicating' times/timestamps caused by Daylight Saving
clock shifts), etc.)

The tzdb is maintained by IANA (the Internet Assigned Numbers
Authority): https://www.iana.org/time-zones; which is where we find such
terms as "America/Halifax". It aims to handle the pain of "time zone
boundaries, UTC offsets, and daylight-saving rules". However, it is not
a standard!

A couple of our training-courses' web-refs [usual disclaimer]:
https://www.w3.org/International/articles/definitions-time/index.en
https://www.w3.org/International/core/2005/09/timezone.html

If you still want to know more/have managed to keep your head in the
game for all this time, there's an extensive discussion and list of
resources at https://data.iana.org/time-zones/tz-link.html
-- 
-- 
Regards,
=dn