The datetime function always returns GMT time unless you request it to do
something else (for example, asking for a conversion to 'localtime' using the
system c library concept of localtime and timezones -- which will almost always
be incorrect on Windows since it has extremely limited comprehension of
timezones -- being written by such a weeny company that only exists in one
timezone asnd has no customers operating in more than one timezone or for more
than one or two years).
For example:
sqlite> select datetime('2015-01-04 15:35:16.435465 -07:00');
2015-01-04 22:35:16
Notice two things: (1) the output is rounded to the second (2) the string has
no timezone offset (it is naive).
If you need to keep the milliseconds, then you need to use strftime:
sqlite> select strftime('%Y-%m-%d %H:%M:%f','2015-01-04 15:35:16.435465
-07:00');
2015-01-04 22:35:16.435
If you need more than 3 decimal places for seconds, you need to use something
other than the built in functions. Net ever strftime can be coerced into
reporting more than millisecond precision.
Unfortunately, there is no way to solve the second problem -- the string output
from strftime (and its alias functions) is always naive and you have no way of
knowing what the relevant timezone offset is. You can always append the string
'+00:00' to designate GMT, but this will be a PITA if you have to do it all
over the place. For example,
sqlite> select strftime('%Y-%m-%d %H:%M:%f +00:00','2015-01-04 15:35:16.435465
-07:00');
2015-01-04 22:35:16.435 +00:00
You may well be better served using something other than the SQLite builtin
functions to perform date / time manipulations and store only unixepoch or
julianday numbers in the database -- let your application handle the
conversions as close to the user as possible. If someone wants to use the
shell tool and convert them to strings then they can, but they will suffer the
vagaries of interpretation and expression of the results -- the data in the
database will be clear and concise and totally free of interpretational
anomolies.
---
Theory is when you know everything but nothing works. Practice is when
everything works but no one knows why. Sometimes theory and practice are
combined: nothing works and no one knows why.
>-----Original Message-----
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of MikeSnow
>Sent: Wednesday, 7 January, 2015 07:00
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Time Zone Conversions
>
>I am kind of new at this..... so if I get you, I should concat the 3
>columns
>to get one in the suggested format. But then how do you convert?
>
>"Column_Time"
>2013-10-07 04:23:19.120-04:00
>
> datetime("Column_Time", 'utc')?
>
>
>
>
>
>
>--
>View this message in context: http://sqlite.1065341.n5.nabble.com/Time-
>Zone-Conversions-tp79849p79865.html
>Sent from the SQLite mailing list archive at Nabble.com.
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
