Returned time UTC for RFC3339 and local for epoch

Store
1

Hello. What is the explanation for the following:
I query a record from the database with the time displayed in RFC3339 format. I get the UTC time, which was used to add the record (obviously).
> select “100-00817” from length where time = ‘2020-03-26T04:24:33.368129024Z’
> name: length
> time 100-00817
> ---- ---------
> 2020-03-26T04:24:33.368129024Z 77

Now I change the precision to ms and retrieve the same record again. The returned timestamp, however, is the local time (not so obvious).
> precision ms
> select “100-00817” from length where time = ‘2020-03-26T04:24:33.368129024Z’
name: length
time 100-00817
---- ---------
1585196673368 77
>>> datetime.fromtimestamp(1585196673.368)
datetime.datetime(2020, 3, 26, 17, 24, 33, 368000)

Is epoch time always local time?

2

No, epoch time is always in UTC.

It is your datetime.datetime(…) function which is converting to local time
for you.

For example, the result you got from your second query was 1585196673368

If I drop the milliseconds and then ask my own computer (in Germany) to tell
me what this timestamp is, I get:

$ date -d @1585196673
Thu Mar 26 05:24:33 CET 2020

whereas you got 2020-03-26 17:24:33

So it’s the conversion of the epoch time which turns it into local time. The
database itself contains epoch times, which are always UTC.

Antony.

3

Thanks. You’re right. I got confused by the timestamp obtained from python’s datetime.datetime.utcnow().timestamp(), which returns a naive datetime of utc and timestamp() interprets it as local. Better to use datetime.datetime.now(timezone.utc).timestamp() or just datetime.datetime.now().timestamp().
>>> datetime.now(timezone.utc).timestamp() - datetime.utcnow().timestamp()
46800
>>> datetime.now(timezone.utc).timestamp() - datetime.now().timestamp()
0