Almacenamiento de fecha SQLite y conversión

Estoy teniendo problemas de diseño con el almacenamiento / recuperación de fechas usando Python y SQLite.

Entiendo que una columna de fecha de SQLite almacena las fechas como texto en formato ISO (es decir, '2010-05-25' ). Así que cuando muestro una fecha británica (por ejemplo, en una página web), convierto la fecha usando

 datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y') 

Sin embargo, cuando se trata de escribir datos en la tabla, SQLite es muy tolerante y está feliz de almacenar '25/06/2003' en un campo de fecha, pero esto no es ideal porque

Por lo tanto, necesito volver a convertir la cadena de fecha al formato ISO antes de confirmar, pero luego necesitaría una función genérica que verifique los datos que están a punto de escribirse en todos los campos de fecha y que se convierta a ISO si es necesario. Eso me suena un poco tedioso, pero tal vez sea inevitable.

¿Hay soluciones más simples? ¿Sería más fácil cambiar el campo de fecha a un campo de 10 caracteres y almacenar 'dd/mm/yyyy' toda la tabla? De esta manera no se requiere conversión al leer o escribir de la tabla, y podría usar las funciones datetime () si fuera necesario para realizar cualquier aritmética de fechas.

¿Cómo han superado este problema otros desarrolladores? Cualquier ayuda sería apreciada. Para el registro, estoy usando SQLite3 con Python 3.1.

Si establece detect_types=sqlite3.PARSE_DECLTYPES en sqlite3.connect , la conexión intentará convertir los tipos de datos de sqlite a los tipos de datos de Python cuando extraiga datos de la base de datos.

Esto es algo muy bueno, ya que es mucho más agradable trabajar con objetos datetime que con cadenas aleatorias parecidas a la fecha, que luego tiene que analizar con datetime.datetime.strptime o dateutil.parser.parse .

Desafortunadamente, el uso de detect_types no impide que sqlite acepte cadenas como datos de FECHA, pero obtendrá un error cuando intente extraer los datos de la base de datos (si se insertó en otro formato que no sea YYYY-MM-DD) porque la conexión no podrá convertirlo en un objeto datetime.date:

 conn=sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES) cur=conn.cursor() cur.execute('CREATE TABLE foo(bar DATE)') # Unfortunately, this is still accepted by sqlite cur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',)) # But you won't be able to draw the data out later because parsing will fail try: cur.execute("SELECT * FROM foo") except ValueError as err: print(err) # invalid literal for int() with base 10: '25/06/2003' conn.rollback() 

Pero al menos el error lo alertará sobre el hecho de que ha insertado una cadena para una FECHA cuando realmente debería insertar objetos datetime.date:

 cur.execute("INSERT INTO foo(bar) VALUES (?)",(datetime.date(2003,6,25),)) cur.execute("SELECT ALL * FROM foo") data=cur.fetchall() data=zip(*data)[0] print(data) # (datetime.date(2003, 6, 25),) 

También puede insertar cadenas como datos de FECHA siempre que utilice el formato YYYY-MM-DD. Tenga en cuenta que aunque insertó una cadena, vuelve a salir como un objeto datetime.date :

 cur.execute("INSERT INTO foo(bar) VALUES (?)",('2003-06-25',)) cur.execute("SELECT ALL * FROM foo") data=cur.fetchall() data=zip(*data)[0] print(data) # (datetime.date(2003, 6, 25), datetime.date(2003, 6, 25)) 

Por lo tanto, si tiene la disciplina de insertar solo objetos datetime.date en el campo DATE , no tendrá problemas más adelante al extraer los datos.

Si sus usuarios están ingresando datos de fecha en varios formatos, revise dateutil.parser.parse . Es posible que pueda ayudarle a convertir esas diversas cadenas en objetos datetime.datetime .

Tenga en cuenta que SQLite en sí no tiene un tipo de fecha / hora nativo. Como respondió @unutbu, puede hacer que el módulo pysqlite / sqlite3 intente adivinar (y tenga en cuenta que realmente es una adivinación) qué columnas / valores son fechas / horas. Las expresiones SQL lo confundirán fácilmente.

SQLite tiene una variedad de funciones de fecha y hora y puede trabajar con varias cadenas, números tanto en formato unixepoch como en juliano, y puede hacer transformaciones. Vea la documentación:

http://www.sqlite.org/lang_datefunc.html

Puede que le resulte más conveniente hacer que SQLite realice el trabajo de fecha / hora que necesita en lugar de importar los valores en Python y usar las bibliotecas de Python para hacerlo. Tenga en cuenta que puede poner restricciones en la definición de la tabla SQL, por ejemplo, si requiere que el valor de la cadena esté presente, tenga una cierta longitud, etc.