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

[Robert Pollak]

Robert Pollak  added the comment:

Thank you, Paul, for your hints on sniffing. I have now asked at SqliteStudio 
for confirmation that they also do content sniffing to detect datetime text 
fields: https://github.com/pawelsalawa/sqlitestudio/issues/3449 .

So in this issue I am suggesting to embed this datetime text sniffing into 
sqlite3, for optional activation. Perhaps a new flag value of 
detect_types=DETECT_DATETIME to the sqlite3.connect() function would be 
suitable?

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

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

[Paul Ganssle]

Paul Ganssle  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 
"-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 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

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

[Serhiy Storchaka]

Change by Serhiy Storchaka :


--
nosy: +berker.peksag, ghaering

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

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

[Robert Pollak]

Robert Pollak  added the comment:

(In fact, I am currently taking the first non-missing entry of each text column 
and trying to dateutil.parser.parse it. If that works, I use pandas.to_datetime 
on the column.)

--

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com

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

[Robert Pollak]

New submission from Robert Pollak :

Currently, fields are converted to datetime as described in 
https://docs.python.org/3/library/sqlite3.html#sqlite3.PARSE_COLNAMES :

'select x as "x [datetime]" from table'

In my use case I don't know the names and locations of the datetime fields in 
advance. So I would need to do pandas.read_sql_query('select * from table', 
con) and get correct datetime columns.
(My current workaround is try calling pandas.to_datetime on each text column 
afterwards.)

The type info has to be available in the sqlite database, because I see that 
SQLiteStudio correctly detects the datetime columns.

--
components: Library (Lib)
messages: 329128
nosy: jondo
priority: normal
severity: normal
status: open
title: sqlite3: "select *" should autoconvert datetime fields
type: behavior
versions: Python 3.7

___
Python tracker 

___
___
Python-bugs-list mailing list
Unsubscribe: 
https://mail.python.org/mailman/options/python-bugs-list/archive%40mail-archive.com