Re: [sqlite] Using modifiers in julianday function

[Dilip Ranganathan]

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

[Doug Currie]

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

[Dilip Ranganathan]

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