SQLITE DATETIME troubles

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.

Cython modules not automatically rebuild through setup.py on changes of pxd files

After some frustrating hours I discovered that if you use a distutils setup.py script to create a cython module the module is not updated if you only change a pxd header file. Especially in a bigger project with many cython modules this can be easily forgotten, so in case of unexpected behaviour just try removing all pyd files so everything will be build fresh.

It is perhaps even saver to touch all .pyx files because if you remove the pyd file the .c file is not necessarily recreated!

interp1d x-axis order must be in ascending order

Today I discovered that the nice scipy 1d interpolation function interp1d requires that the values in x-array passed as the first argument are in ascending order. Perhaps this makes sense but it took me some time to figure out that this was the reason I was getting out-of-bounds errors (my x-array was in descending order).