Thanks Simon, but I've the following situation:
BEGIN TRANSACTION;
CREATE TABLE t1(
id integer primary key,
dateStart text, -- 'YYYY-MM-DD HH:MM:SS'
dateEnd text -- 'YYYY-MM-DD HH:MM:SS'
);
INSERT INTO "t1" VALUES(1,'2010-01-20 18:00:00','2010-01-21 02:00:00');
COMMIT;
and I'd like to know how many hours and minutes are in my query that starts
from '2010-01-20 09:00:00' to '2010-01-21 00:00:00'. The result should be
the difference between C and B:
A B
| |
C D
| |
Query:
A = '2010-01-20 09:00:00'
B = '2010-01-21 00:00:00'
Database:
C = '2010-01-20 18:00:00'
D = '2010-01-21 02:00:00'
C B
Result: | |
The SQL query should be something like that:
select case when dateStart <= '2010-01-21 00:00:00' and dateEnd >=
'2010-01-21
00:00:00' then
strftime('%s', '2010-01-21 00:00:00') - strftime('%s', dateStart)
else
strftime('%s', dateEnd) - strftime('%s', dateStart)
end
from t1
where dateStart >= '2010-01-20 09:00:00' and dateEnd <= '2010-01-21
00:00:00';
this statement doesn't work due the "and dateEnd <= '2010-01-21
00:00:00';" that cut out the record.
2010/10/21 Simon Davies <simon.james.dav...@gmail.com>
> On 21 October 2010 11:03, Danilo Cicerone <cyds...@gmail.com> wrote:
> > Hi to all,
> > I'd like to calculate hours and minutes having the following situation
> where
> > A and B are query, B and C are the data stored in a table:
>
> ? B is query and data?
>
> >
> > ------------------------->Time
> >
> > A B
> > | |
> >
> > C D
> > | |
> >
> > A = '2010-01-20 09:00:00'
> > B = '2010-01-21 00:00:00'
> > C = '2010-01-20 18:00:00'
> > D = '2010-01-21 02:00:00'
> >
> > it should be the difference between C and B. I've tried:
> >
> .
> .
> .
> > select case when dateStart <= '2010-01-21 00:00:00' and dateEnd >=
> '2010-01-21
> > 00:00:00' then
> > strftime('%s', '2010-01-21 00:00:00') - strftime('%s', dateStart)
> > else
> > strftime('%s', datae) - strftime('%s', datas)
> > end
> > from t1
> > where dateStart >= '2010-01-20 09:00:00' and dateEnd <= '2010-01-21
> > 00:00:00';
> >
> > returning obviously nothing!!!
>
> Difficult to interpret what you are trying to do, but is this close?
>
> SQLite version 3.6.11
> Enter ".help" for instructions
> sqlite>
> sqlite> create table t1( id integer primary key,
> ...> dateStart text,
> ...> dateEnd text );
> sqlite>
> sqlite> insert into t1( dateStart, dateEnd ) values( '2010-01-20
> 09:00:00', '2010-01-21 00:00:00' );
> sqlite> insert into t1( dateStart, dateEnd ) values( '2010-01-20
> 18:00:00', '2010-01-21 02:00:00' );
> sqlite>
> sqlite> select case when dateStart <= '2010-01-21 00:00:00' and
> dateEnd >= '2010-01-21 00:00:00' then
> ...> strftime('%s', '2010-01-21 00:00:00') - strftime('%s',
> dateStart)
> ...> else
> ...> strftime('%s', dateEnd) - strftime('%s', dateStart)
> ...> end from t1;
> 54000
> 21600
> sqlite>
> sqlite> select * from t1 where dateStart >= '2010-01-20 09:00:00' and
> dateEnd <= '2010-01-21 00:00:00';
> 1|2010-01-20 09:00:00|2010-01-21 00:00:00
> sqlite>
> sqlite> select case when dateStart <= '2010-01-21 00:00:00' and
> dateEnd >= '2010-01-21 00:00:00' then
> ...> strftime('%s', '2010-01-21 00:00:00') - strftime('%s',
> dateStart)
> ...> else
> ...> strftime('%s', dateEnd) - strftime('%s', dateStart)
> ...> end from t1
> ...> where dateStart >= '2010-01-20 09:00:00' and dateEnd <=
> '2010-01-21 00:00:00';
> 54000
> sqlite>
>
> > Is There a way to calculate that in a single SQL statement?
>
> After correcting some typos, your SQL produces results for me.
>
> >
> > Thanks,
> > Danilo
>
> Regards,
> Simon
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
