Now, sqlite3 does not support the datetime type and I think it is not the job of Python to do this operation but the caller (your code) but you can see the example in Doc/includes/sqlite3/pysqlite_datetime.pyĬon = nnect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)Ĭur.execute("create table test(d date, ts timestamp)")Ĭur.execute("insert into test(d, ts) values (?, ?)", (today, now))Ĭur.execute('select current_date as "d ", current_timestamp as "ts "') You can read this doc about the read_sql If you use sqliteman you get a TEXT and not "datetime" Perhaps a new flag value of detect_types=DETECT_DATETIME to the nnect() function would be suitable?įor my part, we could close this issue just because I am sure they sniff the format of the string. So in this issue I am suggesting to embed this datetime text sniffing into sqlite3, for optional activation.
I have now asked at SqliteStudio for confirmation that they also do content sniffing to detect datetime text fields.
Thank you, Paul, for your hints on sniffing. It is not designed to tell you whether something is or is not a datetime. I would not recommend using ``, 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.
Sqlitestudio printing iso#
Or if you want to be faster and less strict (this will allow several other variations on ISO 8601): 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:ĭatetime.strftime(text_column, "%Y-%m-%d %H:%M:%S.%f") 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 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" ( ) Instead, the built-in Date And Time Functions of SQLiteĪre capable of storing dates and times as TEXT, REAL, or INTEGER values SQLite does not have a storage class set aside for storing datesĪnd/or times. If that works, I use pandas.to_datetime on the column.)Īccording to the sqlite documentation, there's no fundamental datetime type in sqlite: (In fact, I am currently taking the first non-missing entry of each text column and trying to it. The type info has to be available in the sqlite database, because I see that SQLiteStudio correctly detects the datetime columns. (My current workaround is try calling pandas.to_datetime on each text column afterwards.) So I would need to do pandas.read_sql_query('select * from table', con) and get correct datetime columns. In my use case I don't know the names and locations of the datetime fields in advance. FilesĬurrently, fields are converted to datetime as described in : Sqlite3: optionally autoconvert table_info's DATETIME fieldsīerker.peksag, ghaering, jondo, matrixise, p-ganssleĬreated on 12:16 by jondo, last changed 14:59 by admin.
0 kommentar(er)
