Note that if you want the extra precision to show as fractional seconds you
have to ensure floating point is passed (particularly if column dt is of
integer affinity) and use strftime() rather than datetime() so you can specify
the format string explicitly using %f to get SS.SSS.
Datetime() uses %S for the seconds part which ignores the fractional seconds.
SELECT CASE
WHEN (unix10and13.dt < 10000000000)
THEN strftime('%Y-%m-%d %H:%M:%f', unix10and13.dt, 'unixepoch')
WHEN (unix10and13.dt > 10000000000)
THEN strftime('%Y-%m-%d %H:%M:%f', unix10and13.dt / 1000.0, 'unixepoch')
ELSE dt
END AS converted
FROM unix10and13;
2009-02-11 09:38:07.123
2009-02-13 23:31:30.000
2015-05-20 05:53:54.000
2016-02-29 13:58:43.012
Personally I would also use '%Y-%m-%d %H:%M:%f Z' for the format string as well
to make sure that it cannot be misinterpreted down the line -- most parsers
will be able to properly handle strings of the format '2009-02-11 09:38:07.123
Z' -- but it is the humans reading the string that might get confused too.
Paul didn't ask these questions ... but just for completeness.
> -----Original Message-----
> From: sqlite-users [mailto:[email protected]]
> On Behalf Of Paul Sanderson
> Sent: Thursday, 29 September, 2016 07:51
> To: SQLite mailing list
> Subject: Re: [sqlite] converting unix10 and unix13 dates in the same
> column
>
> All sorted now thank you
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
