Tuesday, October 7, 2014


Recently, I have started to play with SQLITE. For the database creation and queries I actually use PyQt but that is not really the issue. I was unaware of the complications with SQL databases and DATETIME formats. I made the wrong assumption that if I provided input that did not give any errors and looked good in the nice SQLite Database Browser everything would be okay. What happened was that I filled a datetime column with strings like '09-11-2014 12:43:00'. This actually is one of the few not supported datetime formats. When running a query where the datetime has to be before or after another (similarly formatted) datetime this actually seemed to produce sensible results. Only when I tried to do more fancy datetime stuff like datetime('09-11-2014 12:43:00','-1 month') did things suddenly stop working. I think sqlite is treating the wrong input for a datetime column as a string and is actually doing a string comparison when comparing two wrongly formatted datetime values (which can sometimes give sensible output). The solution is of course simple: only use one of the supported datetime formats when inserting data into a table.

No comments:

Post a Comment