Re: [sqlite] Odd behaviour with JulianDay
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 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 On
> Behalf
> >Of Stephen Chrzanowski
> >Sent: Saturday, 7 September, 2019 14:12
> >To: sqlite-users
> >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
> >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:
> >> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
> >> 2019-09-03 19:29:15.000 43711.8119791667 2458734.32840103
> >> 2019-09-03 19:52:24.000 43711.828056 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
> >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
Re: [sqlite] Odd behaviour with JulianDay
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 On Behalf
>Of Keith Medcalf
>Sent: Saturday, 7 September, 2019 14:43
>To: SQLite mailing list
>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 On
>Behalf
>>Of Stephen Chrzanowski
>>Sent: Saturday, 7 September, 2019 14:12
>>To: sqlite-users
>>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
>>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:
>>> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
>>> 2019-09-03 19:29:15.000 43711.8119791667 2458734.32840103
>>> 2019-09-03 19:52:24.000 43711.828056 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
>>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
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 On Behalf
>Of Stephen Chrzanowski
>Sent: Saturday, 7 September, 2019 14:12
>To: sqlite-users
>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
>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:
>> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
>> 2019-09-03 19:29:15.000 43711.8119791667 2458734.32840103
>> 2019-09-03 19:52:24.000 43711.828056 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
>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
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
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:
> NodeOpenedJulianDay(NodeOpened) JulianDay('now')
> 2019-09-03 19:29:15.000 43711.8119791667 2458734.32840103
> 2019-09-03 19:52:24.000 43711.828056 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
