well, I’d rather use to_char functions, than date() and time().
to_char(‘dd.mm.yyyy’,$datetime) returns the date in that format.
where
to_char(‘hh24:mi’,$datetime) returns the time in 24h:minute format.
you can use advanced formatting for those.
Im coming from oracle though, so I dont know if thats available in sqlite. But i think its sql standard.
well, the VIEW wouldnt be like:
SELECT count(path), path FROM formatedlog
WHERE play_time BETWEEN '18:00:00' AND '18:30:00'
GROUP BY path
HAVING count(path) > 1;
however you’d like to format your date/time fields. the having is the key though. that should even order it by the count(path).