I have following table structure where are saved some dates:
I tried to group results by hours using strftime sqlite function, but i found the problem if datetime value is stored like:
2015-01-21 11:49:16CET
In this case is value not converted. But if i erased "CET" value to have something like this:
2015-01-21 10:44:09
I got the correct result.
Query:
SELECT strftime('%H', dc.date) as hr,
COUNT(*) AS DIALS_CNT,
SUM(CASE WHEN dc.call_result = 'APPT' THEN 1 ELSE 0 END) AS APPT_CNT,
SUM(CASE WHEN dc.call_result = 'CONV_NO_APPT' THEN 1 ELSE 0 END) AS CONVERS_CNT,
SUM(CASE WHEN dc.call_result = 'CANNOT_REACH' THEN 1 ELSE 0 END) AS CANNOT_REACH_CNT
FROM dialed_calls dc
GROUP BY strftime('%H', dc.date);
Should i remove timezone from date column or how can i solve it please?
Many thanks for any advice.
Aucun commentaire:
Enregistrer un commentaire