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
