SQLite wandelt die Zeichenfolge in das Datum um

Ich habe Datum als Zeichenfolge in einer SQLite-database wie “28/11/2010” gespeichert. Ich möchte die Zeichenfolge in Datum konvertieren.

Speziell muss ich viele String-Daten zwischen zwei Daten konvertieren.

In postgresql verwende ich to_date('30/11/2010','dd/MM/yyyy') , wie kann ich das gleiche mit to_date('30/11/2010','dd/MM/yyyy') tun?

Etwas wie das:

 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') 

   

Da Sqlite keinen Datumstyp hat, müssen Sie einen Stringvergleich durchführen, um dies zu erreichen. Damit das funktioniert, müssen Sie die Reihenfolge umkehren – zB von TT / MM / JJJJ bis JJJJMMTT, mit etwas wie

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

Gespeichertes Datum als TEXT (20/10/2013 03:26) Um Query zu tun und Aufzeichnungen zwischen Daten zu wählen?

Bessere Version ist:

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

Der Substr vom 20.10.2013 gibt 20131020 das Datumsformat DATE (20131021) – damit arbeitet SQL mit Datumsangaben und verwendet Datums- und Uhrzeitfunktionen.

ODER

 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'); 

und hier ist in einer Zeile

 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'); 

Eine Sache, auf die Sie achten sollten, sind die SQLite-functionen für Datum und Uhrzeit , besonders wenn Sie viele Daten manipulieren müssen. Es ist der vernünftige Weg, Daten zu verwenden, auf Kosten der Änderung des internen Formats (muss ISO sein, dh JJJJ-MM-TT).

Der UDF-Ansatz ist meine Präferenz im Vergleich zu spröden substr Werten.

 #!/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() 

Dies wird Ihnen diese Ergebnisse geben:

 [{'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'}] 

Das SQLite-Äquivalent von allem, was so robust ist, ist ein Wirrwarr von substr und instr Aufrufen, die Sie vermeiden sollten.

Dies ist für fecha (TEXT) Format Datum JJJJ-MM-TT HH: mm: ss zum Beispiel Ich möchte alle Datensätze von 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); 

Wenn das Format für das Datumsformat nicht konsistent ist, liegt ein Problem mit der function substr , zB:

1/1/2017 oder 1/11/2017 oder 11/11/2017 oder 1/1/17 usw.

Also folgte ich einem anderen Ansatz mit einem temporären Tisch. Dieses Snippet gibt “YYYY-MM-DD” + time aus, sofern vorhanden.

Beachten Sie, dass diese Version das Format Tag / Monat / Jahr akzeptiert. Wenn Sie Monat / Tag / Jahr möchten, tauschen Sie die ersten beiden Variablen DayPart und MonthPart . Die zweijährigen Daten ’44 -’99 nehmen 1944-1999 an, während ’00 -’43 von 2000-2043 ausgeht.

  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; 

Ich speichere das Datum als ‘DD-MON-YYYY-Format (10-Jun-2016) und unterhalb der Abfrage funktioniert für mich Datensätze zwischen 2 Daten zu suchen.

 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');