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:
------------------------->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:
CREATE TABLE t1(
id integer primary key,
dateStart text, -- 'YYYY-MM-DD HH:MM:SS'
dateEnd text -- 'YYYY-MM-DD HH:MM:SS'
);
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
and it works, but if add the "WHERE" clausole to select only that record it
starts the troubles:
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!!!
Is There a way to calculate that in a single SQL statement?
Thanks,
Danilo
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
