mercredi 4 mars 2015

SQLite strftime function issue with timezone

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