Aggregating date column breaks PARSE_DECLTYPES

Question

I have an SQLite database in which I have declared certain columns type DATE (allowable but, of course, ignored by SQLite as it does not have a DATE type). I am storing my dates as SQLite date strings.

When connecting from Python, I pass detect_types=sqlite3.PARSE_DECLTYPES to the connection. This allows the sqlite library to correctly determine which columns contain dates and it correctly provides me with a date object, not a string when I read from these columns.

However, if I aggregate a date column, I get back a string, not a date:

 SELECT MIN(ReportDate) FROM PatientReports WHERE PatientID = ?;

produces a string, not a date value. Interestingly this SQL:

 SELECT ReportDate FROM PatientReports WHERE PatientID = ? ORDER BY ReportDate DESC LIMIT 1;

correctly produces a date value (and this is the work-around I am using).

Question: Is there some way I can modify either the first SQL statement or my database definition to allow Python to preserve the implied date conversion?

Guess already answered http://stackoverflow.com/questions/4429788/reading-back-a-datetime-in-sqlite3 — hyades, Sep 07 '14 at 16:18

score 4 · Accepted Answer · answered Sep 07 '14 at 16:14

PARSE_DECLTYPES works only when you are using a column value directly; any processing done on it, such as passing it through a function, or doing arithmetic, or simply +ReportDate, will lose the declared type information.

This means that PARSE_DECLTYPES is not very useful in practice.

If you really want to use Python's type converters, you should enable PARSE_COLNAMES and add the type to the column name when needed:

SELECT MIN(ReportDate) AS "ReportDate [date]" FROM ...

Aggregating date column breaks PARSE_DECLTYPES

1 Answers1