On 5/16/08, P Kishor <[EMAIL PROTECTED]> wrote:
> On 5/16/08, Scott Baker <[EMAIL PROTECTED]> wrote:
> > Miguel wrote:
> > > Estimates,
> > > First of all, excuse my English, I recognise that it is not my strong.
> > > I need to do a query on a table and I return the difference in minutes
> > > between
> > > two times loaded in the table.
> > > Which would be the best way to make these differences.
> > > Since already thank you very much and greetings.
> >
> >
> > If you convert both dates to unixtime (seconds) and subtract you'll get
> > seconds between the two dates. Then divide by 60.
> >
> > SELECT (strftime('%s','now') - strftime('%s','2004-01-01 02:34:56')) / 60;
> >
> >
> > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> >
> >
>
> well, no not really. The original question is about subtracting date
> in one row from the date in another row. This is an Igor-level
> question, but the following comes to my mind --
>
> Given the following table,
>
> qlite> create table t (a, b datetime);
> sqlite> insert into t values (1, '1993-01-01 00:00:30');
> sqlite> insert into t values (2, '1992-02-12 00:12:29');
> sqlite> select * from t;
> 1|1993-01-01 00:00:30
> 2|1992-02-12 00:12:29
> sqlite> select (julianday(t1.b) - julianday(t2.b)) d from t t1 left
> join t t2 on t1.a = t2.a and t1.a = 1 or t2.a = 2 and d > 0;
>
> Replace julianday with the datetime function of your choice. Still, my
> solution is not really good because I can't figure out how to get
> exactly the result I want, but it almost gets me there.
>
>
fwiw, here is the correct solution for "returning the difference in
minutes between two times loaded in a table"
sqlite> .s
CREATE TABLE t (i, a);
sqlite> select * from t;
i a
---------- -------------------
1 1993-01-01 00:00:00
2 1993-01-10 00:19:00
3 1994-01-10 00:19:00
4 1994-01-12 00:19:00
sqlite> select (strftime('%s',t1.a) - strftime('%s',t2.a))/60 d from t
t1 join t t2 on t1.i != t2.i and t1.i = 1 and t2.i = 2;
d
----------
-12979
sqlite>
replace 1 and 2 in the query above with the row ids that you want to query.
>
> > --
>
> > Scott Baker - Canby Telcom
> > RHCE - System Administrator - 503.266.8253
> >
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
