string - studio - Cadena de conversión Sqlite hasta la fecha
sql convert (7)
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''
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'')
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);
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'');
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'');
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;
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).