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.
> --
> Scott Baker - Canby Telcom
> RHCE - System Administrator - 503.266.8253
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
