Difference between localtime and utc is 8 hours, but should be 4

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Difference between localtime and utc is 8 hours, but should be 4

Rob Richardson-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between localtime and utc is 8 hours, but should be 4

Stephen Chrzanowski
Because you're converting your UTC time to UTC.

On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson <[hidden email]>
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between localtime and utc is 8 hours, but should be 4

Stephen Chrzanowski
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 <[hidden email]>
wrote:

> Because you're converting your UTC time to UTC.
>
> On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between localtime and utc is 8 hours, but should be 4

Rob Richardson-3
That makes sense.  Thank you very much.

RobR

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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 <[hidden email]>
wrote:

> Because you're converting your UTC time to UTC.
>
> On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson
> <[hidden email]>
> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Difference between localtime and utc is 8 hours, but should be 4

Chris Waters
In reply to this post by Rob Richardson-3
As Stephen observed when replying to your query, time math is fraught with problems.

So I think it will be nothing to do with SQLite per se. I'm guessing it will be in the time offset specification you have entered somewhere, for your environment. You are in what is termed time zone utc  -4.
Time zones are expressed as what you have to do to UTC to get your local time. So for you, -4.
A common error is to express it as what you have to do to your local time to get UTC. That expression would be +4 for you! Wrong.

I suspect this is where your 8 hour difference is coming from.

In a layered environment where you have O/S, plus "international" application layers such as mail environments and ERPs you have multiple competing transforms, which in a round trip (my location back to my location) will appear as everything is set correctly, but when you move out of your zone can appear bizarre.

Hope this is useful
Chris

>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users