Re: [sqlite] Using modifiers in julianday function
On Mon, Oct 24, 2011 at 11:47 AM, Doug Currie wrote: > > On Oct 24, 2011, at 11:07 AM, Dilip Ranganathan wrote: > > > But as you all know, this doesn't work: > > > > select datetime(time) from table where time >= > > julianday(datetime(max(time)),'-2 hour','localtime') order by time desc > > Try replacing datetime(max(time)) with (select datetime(max(time)) from > table) > > as in > > sqlite> select datetime(time) from table > ...> where time >= > ...> julianday((select datetime(max(time)) from t),'-2 hour','localtime') > order by time desc; > 2011-10-24 15:43:45 > 2011-10-24 15:43:39 > sqlite> > > That worked like a charm. Thanks! I should've just tried this but somehow the idea of a query going inside a sqlite function didn't seem all that obvious to me. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using modifiers in julianday function
On Oct 24, 2011, at 11:07 AM, Dilip Ranganathan wrote: > But as you all know, this doesn't work: > > select datetime(time) from table where time >= > julianday(datetime(max(time)),'-2 hour','localtime') order by time desc Try replacing datetime(max(time)) with (select datetime(max(time)) from table) as in sqlite> select datetime(time) from table ...> where time >= ...> julianday((select datetime(max(time)) from t),'-2 hour','localtime') order by time desc; 2011-10-24 15:43:45 2011-10-24 15:43:39 sqlite> e ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using modifiers in julianday function
Lets say I have a column named 'time' that stores timestamps as juliandays. Suppose I only want to extract rows for the past 2 hours from now, I could do: select datetime(time) from table where time >= julianday('now','-2 hour','localtime') order by time desc That works as expected. However what if I want to go 2 hours backwards from a *specific* julianday value? say, 2 hours from the most recent datetime value recorded in the table. This gets me the latest timestamped record: select datetime(max(time)) from table But as you all know, this doesn't work: select datetime(time) from table where time >= julianday(datetime(max(time)),'-2 hour','localtime') order by time desc I am sure I am missing something here. Any suggestions? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users