That makes sense. Thank you very much. RobR
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski Sent: Friday, April 21, 2017 4:29 PM To: SQLite mailing list Subject: Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4 Let me clarify; What you store in the database is just a number. There is no indication to what timezone that references. So when you convert UTC to UTC, you're taking a time already set for UTC and converting it to another 4 hours earlier (Or later? -- i hate time math). When I say "UTC to UTC", the first UTC isn't actually UTC, but assumed local. From https://sqlite.org/lang_datefunc.html under Modifiers section: 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. On Fri, Apr 21, 2017 at 4:24 PM, Stephen Chrzanowski <pontia...@gmail.com> wrote: > Because you're converting your UTC time to UTC. > > On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson > <rdrichard...@rad-con.com> > wrote: > >> Hello! >> >> I'm in the Eastern US time zone, in daylight savings time. I am four >> hours earlier than UTC time. I have a column that stores UTC times >> as Julian times (floating-point numbers). The latest data point in >> the table was stored at about 8:41 this morning (4/21). >> >> I am getting strange results from this query: >> select max(value_timestamp), >> datetime(max(julianday(value_timestamp)), 'localtime'), >> datetime(max(julianday(value_timestamp)), 'utc') from trend_data >> >> The results are: >> 2457864.86179398 >> 2017-04-21 04:40:59 >> 2017-04-21 12:40:59 >> >> How is it that switching from local time to UTC gives an eight-hour >> difference? >> >> Thank you very much. >> >> RobR >> _______________________________________________ >> 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
