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 <kmedc...@dessus.com> 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 <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
