On 21 October 2010 11:58, Danilo Cicerone <cyds...@gmail.com> wrote:
> Thanks Simon, but I've the following situation:
>
.
.
.
> 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.
>
-- Time in seconds of overlap between two time intervals
select case
when dateStart <= p.periodStart and dateEnd >= p.periodEnd then
strftime('%s', p.periodEnd) - strftime('%s',
p.periodStart)
when dateStart <= p.periodStart then
strftime('%s', dateEnd) - strftime('%s', p.periodStart)
when dateEnd >= p.periodEnd then
strftime('%s', p.periodEnd) - strftime('%s', dateStart)
else
strftime('%s', dateEnd) - strftime('%s', dateStart)
end
from t1, ( select '2010-01-20 17:00:00' periodStart, '2010-01-21
22:00:00' periodEnd ) p
where ( dateStart >= p.periodStart AND dateStart <= p.periodEnd ) OR
( dateEnd >= p.periodStart AND dateEnd <= p.periodEnd );
Regards,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
