SQLite Forum

Incorrect conversion from unixepoch

Incorrect conversion from unixepoch

(1) By Serge B. (sergey-b) on 2020-12-12 15:42:19 [link] [source]

Incorrect conversion from unixepoch

Steps to reproduce:

select datetime(1607787371, 'unixepoch', 'utc');

Expected result:

2020-12-12 15:36:11

Actual result:

Time shifted back by local timezone offset. For instance, in Moscow it would be

2020-12-12 12:36:11

(2) By Keith Medcalf (kmedcalf) on 2020-12-12 16:57:35 in reply to 1 [link] [source]

No, the result is correct. You have requested the 'utc' operation on which un-applies the local computers concept of the difference between localtime and utc as if the prior speciied timestamp were localtime and you now want it converted to utc.

Assuming that the number 1607787371 represents seconds since the unix epoch UTC, then your request to "unlocalize" it is the cause of your error. That is, the datetime is already utc and there is no need to un-apply the localtime offset again.

However since you have requested it be done, the computer has merely made it so.

(3) By Serge B. (sergey-b) on 2020-12-13 16:02:28 in reply to 2 [link] [source]

I can't agree with this. Unix time 1607787371 is exactly 2020-12-12 15:36:11 UTC, and it doesn't depend on local time. If such arguments are not acceptable for this function, it is better to return an error rather than a wrong value.

(4) By Richard Damon (RichardDamon) on 2020-12-13 16:45:34 in reply to 3 [link] [source]

The Key is the documentation of 'utc'

The "localtime" modifier (12) assumes the time string to its left is in Universal Coordinated Time (UTC) and adjusts the time string so that it displays localtime. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" modifier is the opposite of "localtime". "utc" assumes that the string to its left is in the local timezone and adjusts that string to be in UTC. If the prior string is not in localtime, then the result of "utc" is undefined.

So, etc says that the time provide is presumed to be in local time, and will be adjusted to utc, and if it isn't the results are undefined.

(6) By Serge B. (sergey-b) on 2020-12-13 17:12:29 in reply to 4 [link] [source]

So wierd.

(7) By tom (younique) on 2020-12-13 19:08:14 in reply to 4 [link] [source]

Imho, this documentation indicates that Serge is right. The key is the wording "the time string to its left". But 1607787371 ain't a string.

(8) By Richard Damon (RichardDamon) on 2020-12-13 20:39:41 in reply to 7 [link] [source]

But the first parameter is described as usually a string, but this format being a POSSIBLE exception, using the term 'string' to refer to is doesn't seem that unnatural, and as I understand it, the value for that format could have been provided as a string too. The undefined behavior would also seem to be triggered if it wasn't a string in local time, so that would seem to apply if it wasn't a string.

It also look like the documentation was fixed, and now refers to time values, not strings.

(5) By David Jones (vman59) on 2020-12-13 16:52:50 in reply to 2 [link] [source]

The utc modifier is doing what it's documented to do, but I can see how it is confusing to some people if they've never encountered a system where timestamps are stored as seconds from 1970 in the local time zone (i.e. unix-like, but not UTC). Any source of 'unixepoch' time values these days can be assumed to be UTC, so the 'utc' modifier is a fix for a very rare problem.

(9) By Richard Damon (RichardDamon) on 2020-12-13 20:43:01 in reply to 5 [source]

Yes, anyone who hasn't run into software that uses unix timestamps that handles time zones by effectively changing the definition of the epoch is lucky. I've seen enough software that just reads in the current local time and treats it as UTC to build the timestamp that it isn't a surprise anymore.