Odd behaviour with JulianDay

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

Odd behaviour with JulianDay

Stephen Chrzanowski
I'm creating a new database to keep track of time difference between logins
and logoffs for a particular system.

I have the following query:
select  NodeOpened,
        JulianDay(NodeOpened),
        JulianDay('now')
from EventEntry
where NodeClosed is null

The results are:
NodeOpened                JulianDay(NodeOpened) JulianDay('now')
2019-09-03 19:29:15.000   43711.8119791667      2458734.32840103
2019-09-03 19:52:24.000   43711.8280555556      2458734.32840103
2019-09-03 20:08:54.000   43711.8395138889      2458734.32840103

Reading the Wiki on Julian Day (That the SQLite DateTime formats provides)
I understand why JulianDay is such a large number (Counting days back from
the BC era), but I'm not understanding why the NodeOpened is such a small
number and 'now' is such a huge number?
_______________________________________________
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: Odd behaviour with JulianDay

Stephen Chrzanowski
I think I see it.  This is the schema for the table:
CREATE TABLE [EventEntry](
  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [IPAddress] CHAR,
  [Node] INTEGER DEFAULT 0,
  [NodeOpened] DATETIME,
  [NodeClosed] DATETIME);

When I run a select * from EventEntry I'm seeing the 'float' since
UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
apples.

Now I just need to figure out how to compare apples to apples when using
'now'.

On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski <[hidden email]>
wrote:

> I'm creating a new database to keep track of time difference between
> logins and logoffs for a particular system.
>
> I have the following query:
> select  NodeOpened,
>         JulianDay(NodeOpened),
>         JulianDay('now')
> from EventEntry
> where NodeClosed is null
>
> The results are:
> NodeOpened                JulianDay(NodeOpened) JulianDay('now')
> 2019-09-03 19:29:15.000   43711.8119791667      2458734.32840103
> 2019-09-03 19:52:24.000   43711.8280555556      2458734.32840103
> 2019-09-03 20:08:54.000   43711.8395138889      2458734.32840103
>
> Reading the Wiki on Julian Day (That the SQLite DateTime formats provides)
> I understand why JulianDay is such a large number (Counting days back from
> the BC era), but I'm not understanding why the NodeOpened is such a small
> number and 'now' is such a huge number?
>
>
_______________________________________________
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: Odd behaviour with JulianDay

Keith Medcalf

You can convert a float unixepoch the same way as anything else, you just need to tell strftime (or its overloaded aliases julianday and datetime) that it is a 'unixepoch' since by default it thinks floating point means julianday and integer means unixepoch.

julianday(value, 'unixepoch') will give you the floating point julianday corresponding with the "value" relative to the unixepoch.  Similarly datetime(value, 'unixepoch') will get you the iso8601 text (though only to a precision of a second).

Getting a floating point unixepoch using the builtin functions is messy since strftime('%s') only returns whole seconds (select strftime('%s') - strftime('%S') + stftime('%f')) will get you the unixepoch offset in floating point corresponding to 'now'.

--
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 <[hidden email]> On Behalf
>Of Stephen Chrzanowski
>Sent: Saturday, 7 September, 2019 14:12
>To: sqlite-users <[hidden email]>
>Subject: Re: [sqlite] Odd behaviour with JulianDay
>
>I think I see it.  This is the schema for the table:
>CREATE TABLE [EventEntry](
>  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
>  [IPAddress] CHAR,
>  [Node] INTEGER DEFAULT 0,
>  [NodeOpened] DATETIME,
>  [NodeClosed] DATETIME);
>
>When I run a select * from EventEntry I'm seeing the 'float' since
>UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
>apples.
>
>Now I just need to figure out how to compare apples to apples when using
>'now'.
>
>On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski <[hidden email]>
>wrote:
>
>> I'm creating a new database to keep track of time difference between
>> logins and logoffs for a particular system.
>>
>> I have the following query:
>> select  NodeOpened,
>>         JulianDay(NodeOpened),
>>         JulianDay('now')
>> from EventEntry
>> where NodeClosed is null
>>
>> The results are:
>> NodeOpened                JulianDay(NodeOpened) JulianDay('now')
>> 2019-09-03 19:29:15.000   43711.8119791667      2458734.32840103
>> 2019-09-03 19:52:24.000   43711.8280555556      2458734.32840103
>> 2019-09-03 20:08:54.000   43711.8395138889      2458734.32840103
>>
>> Reading the Wiki on Julian Day (That the SQLite DateTime formats
>provides)
>> I understand why JulianDay is such a large number (Counting days back
>from
>> the BC era), but I'm not understanding why the NodeOpened is such a small
>> number and 'now' is such a huge number?
>>
>>
>_______________________________________________
>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: Odd behaviour with JulianDay

Keith Medcalf

You can also get the floating unixepoch offset by computing the difference between the unixepoch (julianday 2440587.5) and the julianday and then multiplying by 86400 (the number of seconds in a year):

sqlite> select (julianday('now') - 2440587.5) * 86400, strftime('%s') - strftime('%S') + strftime('%f');
1567889771.64401|1567889771.644

--
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 <[hidden email]> On Behalf
>Of Keith Medcalf
>Sent: Saturday, 7 September, 2019 14:43
>To: SQLite mailing list <[hidden email]>
>Subject: Re: [sqlite] Odd behaviour with JulianDay
>
>
>You can convert a float unixepoch the same way as anything else, you just
>need to tell strftime (or its overloaded aliases julianday and datetime)
>that it is a 'unixepoch' since by default it thinks floating point means
>julianday and integer means unixepoch.
>
>julianday(value, 'unixepoch') will give you the floating point julianday
>corresponding with the "value" relative to the unixepoch.  Similarly
>datetime(value, 'unixepoch') will get you the iso8601 text (though only to
>a precision of a second).
>
>Getting a floating point unixepoch using the builtin functions is messy
>since strftime('%s') only returns whole seconds (select strftime('%s') -
>strftime('%S') + stftime('%f')) will get you the unixepoch offset in
>floating point corresponding to 'now'.
>
>--
>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 <[hidden email]> On
>Behalf
>>Of Stephen Chrzanowski
>>Sent: Saturday, 7 September, 2019 14:12
>>To: sqlite-users <[hidden email]>
>>Subject: Re: [sqlite] Odd behaviour with JulianDay
>>
>>I think I see it.  This is the schema for the table:
>>CREATE TABLE [EventEntry](
>>  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
>>  [IPAddress] CHAR,
>>  [Node] INTEGER DEFAULT 0,
>>  [NodeOpened] DATETIME,
>>  [NodeClosed] DATETIME);
>>
>>When I run a select * from EventEntry I'm seeing the 'float' since
>>UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
>>apples.
>>
>>Now I just need to figure out how to compare apples to apples when using
>>'now'.
>>
>>On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski <[hidden email]>
>>wrote:
>>
>>> I'm creating a new database to keep track of time difference between
>>> logins and logoffs for a particular system.
>>>
>>> I have the following query:
>>> select  NodeOpened,
>>>         JulianDay(NodeOpened),
>>>         JulianDay('now')
>>> from EventEntry
>>> where NodeClosed is null
>>>
>>> The results are:
>>> NodeOpened                JulianDay(NodeOpened) JulianDay('now')
>>> 2019-09-03 19:29:15.000   43711.8119791667      2458734.32840103
>>> 2019-09-03 19:52:24.000   43711.8280555556      2458734.32840103
>>> 2019-09-03 20:08:54.000   43711.8395138889      2458734.32840103
>>>
>>> Reading the Wiki on Julian Day (That the SQLite DateTime formats
>>provides)
>>> I understand why JulianDay is such a large number (Counting days back
>>from
>>> the BC era), but I'm not understanding why the NodeOpened is such a
>small
>>> number and 'now' is such a huge number?
>>>
>>>
>>_______________________________________________
>>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: Odd behaviour with JulianDay

Stephen Chrzanowski
In reply to this post by Keith Medcalf
I was just writing the reply to my own emails to say I found a work around
when your email showed up.

I kind of walked around this issue by re-looking at how I was inserting the
data.  I was using the DateTime type in Delphi which counts days up since
Jan 1, 1970.  So what I did is just change my insert statement to reformat
the datetime I want to insert into a string, then have SQLite handle the
conversions as needed.



On Sat, Sep 7, 2019 at 4:43 PM Keith Medcalf <[hidden email]> wrote:

>
> You can convert a float unixepoch the same way as anything else, you just
> need to tell strftime (or its overloaded aliases julianday and datetime)
> that it is a 'unixepoch' since by default it thinks floating point means
> julianday and integer means unixepoch.
>
> julianday(value, 'unixepoch') will give you the floating point julianday
> corresponding with the "value" relative to the unixepoch.  Similarly
> datetime(value, 'unixepoch') will get you the iso8601 text (though only to
> a precision of a second).
>
> Getting a floating point unixepoch using the builtin functions is messy
> since strftime('%s') only returns whole seconds (select strftime('%s') -
> strftime('%S') + stftime('%f')) will get you the unixepoch offset in
> floating point corresponding to 'now'.
>
> --
> 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 <[hidden email]> On
> Behalf
> >Of Stephen Chrzanowski
> >Sent: Saturday, 7 September, 2019 14:12
> >To: sqlite-users <[hidden email]>
> >Subject: Re: [sqlite] Odd behaviour with JulianDay
> >
> >I think I see it.  This is the schema for the table:
> >CREATE TABLE [EventEntry](
> >  [EventID] INTEGER PRIMARY KEY AUTOINCREMENT,
> >  [IPAddress] CHAR,
> >  [Node] INTEGER DEFAULT 0,
> >  [NodeOpened] DATETIME,
> >  [NodeClosed] DATETIME);
> >
> >When I run a select * from EventEntry I'm seeing the 'float' since
> >UnixEpoch, so, 43711.819791667 as an example.  So I'm comparing oranges to
> >apples.
> >
> >Now I just need to figure out how to compare apples to apples when using
> >'now'.
> >
> >On Sat, Sep 7, 2019 at 4:01 PM Stephen Chrzanowski <[hidden email]>
> >wrote:
> >
> >> I'm creating a new database to keep track of time difference between
> >> logins and logoffs for a particular system.
> >>
> >> I have the following query:
> >> select  NodeOpened,
> >>         JulianDay(NodeOpened),
> >>         JulianDay('now')
> >> from EventEntry
> >> where NodeClosed is null
> >>
> >> The results are:
> >> NodeOpened                JulianDay(NodeOpened) JulianDay('now')
> >> 2019-09-03 19:29:15.000   43711.8119791667      2458734.32840103
> >> 2019-09-03 19:52:24.000   43711.8280555556      2458734.32840103
> >> 2019-09-03 20:08:54.000   43711.8395138889      2458734.32840103
> >>
> >> Reading the Wiki on Julian Day (That the SQLite DateTime formats
> >provides)
> >> I understand why JulianDay is such a large number (Counting days back
> >from
> >> the BC era), but I'm not understanding why the NodeOpened is such a
> small
> >> number and 'now' is such a huge number?
> >>
> >>
> >_______________________________________________
> >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