[issue35145] sqlite3: "select *" should autoconvert datetime fields

[Paul Ganssle]

Paul Ganssle <p.ganssle at gmail.com> added the comment:

According to the sqlite documentation, there's no fundamental datetime type in sqlite: https://www.sqlite.org/datatype3.html#date_and_time_datatype


    SQLite does not have a storage class set aside for storing dates
    and/or times. Instead, the built-in Date And Time Functions of SQLite
    are capable of storing dates and times as TEXT, REAL, or INTEGER values


If you have an arbitrary database whose schema you don't know, I'm not sure it would be possible to automatically determine that it's a datetime, though it appears that Python already provides this functionality by exposing the converters "date" and "timestamp" ( https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters )

If you don't know the schema you can't be 100% accurate on which columns are datetime, but apparently datetime types that are text will be of the format "YYYY-MM-DD HH:MM:SS.SSS", which is a variant of iso8601, REAL columns will be Julian day numbers and integers will be epoch time.

If you assume that all your datetime columns will be TEXT and that any TEXT column that happens to be a valid date of is a datetime column, then you can either use:

    datetime.strftime(text_column, "%Y-%m-%d %H:%M:%S.%f")

Or if you want to be faster and less strict (this will allow several other variations on ISO 8601):

    datetime.fromisoformat(text_column)

I would not recommend using `dateutil.parser.parse`, as the dateutil parser is intended for taking something you know to be a string representing a datetime and getting you a datetime object from it. It is not designed to tell you whether something is or is not a datetime.

----------
nosy: +p-ganssle

_______________________________________
Python tracker <report at bugs.python.org>
<https://bugs.python.org/issue35145>
_______________________________________