The result of datetime(0, 'unixepoch') is '1970-01-01 00:00:00'. This is what
does not have milliseconds (the output). It is a text string in the format
YYYY-MM-DD HH:MM:SS. Similarly datetime(0.123, 'unixepoch') is also
'1970-01-01 00:00:00'.
The result of datetime(0) is, of course, '-471-11-24 12:00:00' (actually
-4713-11-24 12:00:00) since this the proleptic Gregorian date corresponding to
julian day number 0 where all date strings are UT1 AD and the year is limited
to 4 positions, one of which is taken up by the - sign.
If looking at the result of the datetime() function execution shows
milliseconds then something is interpreting the output of the datetime function
before you see it, and it is likely that this processing that is what is
causing the issue you are observing.
To get output of a unixepoch x with milliseconds in the string you would need
to use the function strftime('%Y-%m-%d %H:%M:%f', x, 'unixepoch')
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Dominik Ohnezeit
>Sent: Monday, 25 November, 2019 14:49
>To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] wrong timestamp using strftime('%s')
>
>the type of CreationDate is integer.
>The CreationDate is inserted with strftime('%s', '1970-01-01
>00:00:00.000')
>
>need to check which value is written to the database.
>I also tried without milliseconds - same result.
>
>
>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>Im Auftrag von Keith Medcalf
>Gesendet: Montag, 25. November 2019 22:41
>An: SQLite mailing list
>Betreff: Re: [sqlite] wrong timestamp using strftime('%s')
>
>
>
>
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>On Sunday, 24 November, 2019 13:21, Dominik Ohnezeit
><dominik.ohnez...@timeless-games.com> wrote:
>
>>I am trying to convert a date to timestamp, but after the conversion
>>with
>>strftime('%s') the integer result is wrong
>
>>Example:
>
>>I insert a integer timestamp into a integer table column named
>>CreationDate with strftime('%s', '1970-01-01 00:00:00.000')
>
>>After getting it from the table with
>
>>datetime(CreationDate, 'unixepoch')
>
>>or
>
>>datetime(CreationDate)
>
>>the date I get back is not 1970-01-01 00:00:00.000 but 1969-12-31
>>22:29:11.000
>
>This corresponds to Unixepoch time -5449
>
>However, the builtin datetime function does not return milliseconds, only
>seconds, so it cannot return a text string ending in .000
>
>>Does anyone know why?
>
>Your wrapper is probably mucking about with the ISO timestring.
>
>What is the value and type of CreationDate?
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
