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 00.45, Chris Angelico wrote:
> On Tue, Aug 31, 2021 at 8:55 PM MRAB <python at mrabarnett.plus.com> wrote:
>> On 2021-08-31 02:16, dn via Python-list wrote:
>>> On 31/08/2021 11.07, Dennis Lee Bieber wrote:
>>>> On Sun, 29 Aug 2021 19:49:19 -0700 (PDT), "hongy... at gmail.com"
>>>> <hongyi.zhao at gmail.com> declaimed the following:
>>> ...
>>>
>>>>      Might have helped to mention you were in China... To me, CST is North
>>>> America Central Standard Time (and I'd have expected this time of year to
>>>> see CDT - Central Daylight Time)... That led me on a weird meaningless side
>>>> track...
>>> ...
>>>
>>>>      I'm in EDT (Eastern Daylight Time) -- so 4 hours behind UTC.
>>>
>>>
>> What annoys me is when someone starts that a webinar will start at, say,
>> xx ET. I have to know which country that person is in and whether
>> daylight savings is currently in effect (EST or EDT?) so that I can
>> convert to my local time.
> 
> If someone says "ET", then I would assume they mean America/New_York -
> it seems that only in the US do people so utterly assume that everyone
> else is in the same country. In Europe, I hear people say "CEST" and
> such (though I still prefer "Europe/Prague" or whatever country
> they're in), so the only issue there is that they don't always say
> "CEDT" when it's daylight time.

We tackle the inter-related subjects of "localisation" and
"internationalisation" in our (non-Python) training courses.

The 'problem' with a local-time, eg "0900 ET" (as above), is that it is
local. It is really only intended to communicate locally.

The definitions of "local" and 'local understanding' do vary. For
example, Australia stretches over multiple time-zones and thus people in
Melbourne are familiar with the idea of adjusting a Sydney-time to their
local clock. Similarly, US-based folk. However, many other localities
don't have such concerns and thus don't think that way!


At the level of "inter-personal communication", should one say (only)
what is in your head, or perform the 'translation' as a politeness to
your reader?

In @Chris' example, 'the reader' could be anywhere in the world. So, it
would be difficult, and lengthy, to provide for all - but should you
write such an invitation 'for all' ("Hi Everyone"), or should it be
addressed to the individual reader ("Dear Colleague")?

After years (decades!) of being woken in the early-hours of the morning
by (time-zone-ignorant) colleagues, my recommendation is to use both!


If the invitation is mine, typically I will write ("transmit") my local
time, because that is how I'm thinking as I type. On a
second-pass/editing the text, I will be considering how the reader will
"receive" the information.

If we're talking about a staff-meeting, it may be possible to list the
reader's time/date. In the case of an webinar hoping to attract
international participation, the recommendation is to add a
UTC-equivalent. (ideas developed, below)



>> It's so much easier to use UTC.
>>
>> I know what timezone I'm in and whether daylight savings is currently in
>> effect here, so I know the local offset from UTC.

Exactly! The very purpose of having an "international" standard.

Also, the very reason why Python has two groups of time-and-date
functions - so that one may work in local-time, or international-time/UTC.


Please note, the adjustments described (offsets) are *numeric*.
Accordingly, being told that one of our members resides 'at' UTC-4, and
knowing that I am currently at UTC+12, we can figure-out a 16-hour
'time-difference'.

NB in reality, having too few fingers, I would go 'the other way' (ie 24
- 16 ) and work with the idea that our colleague's clock is eight-hours
'ahead' of mine (so, as I've recently 'passed' 0800, that would make it
~1600/4pm for him - remembering that I am already 'in' Wednesday,
whereas Americans are still struggling to catch-up by finishing Tuesday)


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


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


You may have noticed that competent web-sites for courses, conferences,
and the like, will offer 'widgets' that perform these calculations for
the reader (based upon the time-zone reported by your client
web-browser). This is very handy - and may I suggest, should be
requirement for international operations - after all, don't we call it
"the WORLD-WIDE web"?


> (I find the Ireland situation particularly amusing. Northern Ireland,
> being part of the UK, operates on London time, with clocks advancing
> one hour for summer. The Republic of Ireland, on the other hand, has a
> standard time which is one hour later than Greenwich's, but then they
> subtract an hour during winter, returning to standard time in summer.
> So when the rest of Europe adds an hour, Ireland stops subtracting
> one. Clocks in Belfast and Dublin always show the same times.)

Per @Alan's response, I have had 'hairy experiences' with time-zones and
people 'doing their own thing'. It's all good-fun until someone misses a
meeting and the boss...


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?


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!

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

-- 
Regards,
=dn