Thanks Simon, GREAT solution!!!
A very interesting sintax.
2010/10/21 Simon Davies <simon.james.dav...@gmail.com>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
