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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf
>Of Keith Medcalf
>Sent: Saturday, 7 September, 2019 14:43
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf
>>Of Stephen Chrzanowski
>>Sent: Saturday, 7 September, 2019 14:12
>>To: sqlite-users <sqlite-users@mailinglists.sqlite.org>
>>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 <pontia...@gmail.com>
>>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
>>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
