Cadena de conversión Sqlite hasta la fecha

Tengo la fecha almacenada como una cadena en una base de datos sqlite como “28/11/2010”. Quiero convertir la cadena a la fecha.

Específicamente, tengo que convertir muchas fechas de cadena entre dos fechas.

En postgresql, utilizo to_date('30/11/2010','dd/MM/yyyy') , ¿cómo puedo hacer lo mismo con sqlite?

Algo como esto:

 SELECT * FROM table WHERE to_date(column,'dd/MM/yyyy') BETWEEN to_date('01/11/2010','dd/MM/yyyy') AND to_date('30/11/2010','dd/MM/yyyy') 

Como Sqlite no tiene un tipo de fecha , tendrá que hacer una comparación de cadenas para lograr esto. Para que funcione, debe invertir el orden, por ejemplo, de dd / MM / aaaa a aaaaMMdd, usando algo como

 where substr(column,7)||substr(column,4,2)||substr(column,1,2) between '20101101' and '20101130' 

Fecha guardada como TEXTO (20/10/2013 03:26) ¿Para hacer consultas y seleccionar registros entre fechas?

Mejor versión es:

 SELECT TIMSTARTTIMEDATE FROM TIMER WHERE DATE(substr(TIMSTARTTIMEDATE,7,4) ||substr(TIMSTARTTIMEDATE,4,2) ||substr(TIMSTARTTIMEDATE,1,2)) BETWEEN DATE(20131020) AND DATE(20131021); 

el substr de 20/10/2013 da el formato de fecha 20131020 DATE (20131021) – que hace que SQL trabaje con fechas y use funciones de fecha y hora.

O

 SELECT TIMSTARTTIMEDATE FROM TIMER WHERE DATE(substr(TIMSTARTTIMEDATE,7,4) ||'-' ||substr(TIMSTARTTIMEDATE,4,2) ||'-' ||substr(TIMSTARTTIMEDATE,1,2)) BETWEEN DATE('2013-10-20') AND DATE('2013-10-21'); 

y aquí está en una línea

 SELECT TIMSTARTTIMEDATE FROM TIMER WHERE DATE(substr(TIMSTARTTIMEDATE,7,4)||'-'||substr(TIMSTARTTIMEDATE,4,2)||'-'||substr(TIMSTARTTIMEDATE,1,2)) BETWEEN DATE('2013-10-20') AND DATE('2013-10-21'); 

Una cosa que debe considerar es las funciones de fecha y hora de SQLite , especialmente si va a tener que manipular muchas fechas. Es la forma correcta de usar las fechas, a cambio de cambiar el formato interno (tiene que ser ISO, es decir, aaaa-MM-dd).

El enfoque UDF es mi preferencia en comparación con los valores de substr frágiles.

 #!/usr/bin/env python3 import sqlite3 from dateutil import parser from pprint import pprint def date_parse(s): ''' Converts a string to a date ''' try: t = parser.parse(s, parser.parserinfo(dayfirst=True)) return t.strftime('%Y-%m-%d') except: return None def dict_factory(cursor, row): ''' Helper for dict row results ''' d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d def main(): ''' Demonstrate UDF ''' with sqlite3.connect(":memory:") as conn: conn.row_factory = dict_factory setup(conn) ################################################## # This is the code that matters. The rest is setup noise. conn.create_function("date_parse", 1, date_parse) cur = conn.cursor() cur.execute(''' select "date", date_parse("date") as parsed from _test order by 2; ''') pprint(cur.fetchall()) ################################################## def setup(conn): ''' Setup some values to parse ''' cur = conn.cursor() # Make a table sql = ''' create table _test ( "id" integer primary key, "date" text ); ''' cur.execute(sql) # Fill the table dates = [ '2/1/03', '03/2/04', '4/03/05', '05/04/06', '6/5/2007', '07/6/2008', '8/07/2009', '09/08/2010', '2-1-03', '03-2-04', '4-03-05', '05-04-06', '6-5-2007', '07-6-2008', '8-07-2009', '09-08-2010', '31/12/20', '31-12-2020', 'BOMB!', ] params = [(x,) for x in dates] cur.executemany(''' insert into _test ("date") values(?); ''', params) if __name__ == "__main__": main() 

Esto le dará estos resultados:

 [{'date': 'BOMB!', 'parsed': None}, {'date': '2/1/03', 'parsed': '2003-01-02'}, {'date': '2-1-03', 'parsed': '2003-01-02'}, {'date': '03/2/04', 'parsed': '2004-02-03'}, {'date': '03-2-04', 'parsed': '2004-02-03'}, {'date': '4/03/05', 'parsed': '2005-03-04'}, {'date': '4-03-05', 'parsed': '2005-03-04'}, {'date': '05/04/06', 'parsed': '2006-04-05'}, {'date': '05-04-06', 'parsed': '2006-04-05'}, {'date': '6/5/2007', 'parsed': '2007-05-06'}, {'date': '6-5-2007', 'parsed': '2007-05-06'}, {'date': '07/6/2008', 'parsed': '2008-06-07'}, {'date': '07-6-2008', 'parsed': '2008-06-07'}, {'date': '8/07/2009', 'parsed': '2009-07-08'}, {'date': '8-07-2009', 'parsed': '2009-07-08'}, {'date': '09/08/2010', 'parsed': '2010-08-09'}, {'date': '09-08-2010', 'parsed': '2010-08-09'}, {'date': '31/12/20', 'parsed': '2020-12-31'}, {'date': '31-12-2020', 'parsed': '2020-12-31'}] 

El equivalente SQLite de algo así de robusto es un instr llamadas substr y instr que debes evitar.

Esto es para fecha de formato fecha (TEXTO) AAAA-MM-dd HH: mm: ss, por ejemplo, quiero todos los registros de Ene-05-2014 (2014-01-05):

 SELECT fecha FROM Mytable WHERE DATE(substr(fecha ,1,4) ||substr(fecha ,6,2)||substr(fecha ,9,2)) BETWEEN DATE(20140105) AND DATE(20140105); 

Si el formato de fecha de origen no es consistente, hay algún problema con la función de substr , por ejemplo:

1/1/2017 o 1/11/2017 o 11/11/2017 o 1/1/17, etc.

Entonces seguí un enfoque diferente usando una tabla temporal. Este fragmento produce ‘YYYY-MM-DD’ + tiempo si existe.

Tenga en cuenta que esta versión acepta el formato Día / Mes / Año. Si desea mes / día / año, cambie las primeras dos variables DayPart y MonthPart . Además, las fechas de dos años ’44 -’99 suponen 1944-1999 mientras que ’00 -’43 supone 2000-2043.

  BEGIN; CREATE TEMP TABLE [DateconvertionTable] (Id TEXT PRIMARY KEY, OriginalDate TEXT , SepA INTEGER, DayPart TEXT,Rest1 TEXT, SepB INTEGER, MonthPart TEXT, Rest2 TEXT, SepC INTEGER, YearPart TEXT, Rest3 TEXT, NewDate TEXT); INSERT INTO [DateconvertionTable] (Id,OriginalDate) SELECT SourceIdColumn, SourceDateColumn From [SourceTable]; --day Part (If day is first) UPDATE [DateconvertionTable] SET SepA=instr(OriginalDate ,'/'); UPDATE [DateconvertionTable] SET DayPart=substr(OriginalDate,1,SepA-1) ; UPDATE [DateconvertionTable] SET Rest1=substr(OriginalDate,SepA+1); --Month Part (If Month is second) UPDATE [DateconvertionTable] SET SepB=instr(Rest1,'/'); UPDATE [DateconvertionTable] SET MonthPart=substr(Rest1, 1,SepB-1); UPDATE [DateconvertionTable] SET Rest2=substr(Rest1,SepB+1); --Year Part (3d) UPDATE [DateconvertionTable] SET SepC=instr(Rest2,' '); --Use Cases In case of time string included UPDATE [DateconvertionTable] SET YearPart= CASE WHEN SepC=0 THEN Rest2 ELSE substr(Rest2,1,SepC-1) END; --The Rest considered time UPDATE [DateconvertionTable] SET Rest3= CASE WHEN SepC=0 THEN '' ELSE substr(Rest2,SepC+1) END; -- Convert 1 digit day and month to 2 digit UPDATE [DateconvertionTable] SET DayPart=0||DayPart WHERE CAST(DayPart AS INTEGER)<10; UPDATE [DateconvertionTable] SET MonthPart=0||MonthPart WHERE CAST(MonthPart AS INTEGER)<10; --If there is a need to convert 2 digit year to 4 digit year, make some assumptions... UPDATE [DateconvertionTable] SET YearPart=19||YearPart WHERE CAST(YearPart AS INTEGER)>=44 AND CAST(YearPart AS INTEGER)<100; UPDATE [DateconvertionTable] SET YearPart=20||YearPart WHERE CAST(YearPart AS INTEGER)<44 AND CAST(YearPart AS INTEGER)<100; UPDATE [DateconvertionTable] SET NewDate = YearPart || '-' || MonthPart || '-' || DayPart || ' ' || Rest3; UPDATE [SourceTable] SET SourceDateColumn=(Select NewDate FROM DateconvertionTable WHERE [DateconvertionTable].id=SourceIdColumn); END; 

Estoy almacenando la fecha como el formato ‘DD-MON-YYYY (10-jun-2016) y la consulta siguiente funciona para buscar registros entre 2 fechas.

 select date, substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), case substr(date, 4,3) when 'Jan' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jan' , '01')) when 'Feb' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Feb' , '02')) when 'Mar' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Mar' , '03')) when 'Apr' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Apr' , '04')) when 'May' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'May' , '05')) when 'Jun' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jun' , '06')) when 'Jul' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Jul' , '07')) when 'Aug' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Aug' , '08')) when 'Sep' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Sep' , '09')) when 'Oct' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Oct' , '10')) when 'Nov' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Nov' , '11')) when 'Dec' then strftime('%s', replace(substr(date,8,11) || '-' || substr(date,4,4) || substr(date, 1,2), 'Dec' , '12')) else '0' end as srcDate from payment where srcDate >= strftime('%s', '2016-07-06') and srcDate <= strftime('%s', '2016-09-06');