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
