wake-up-neo.com

Sqlite konvertiert die Zeichenfolge in Datum

Ich habe das Datum als Zeichenfolge in einer SQLite-Datenbank wie "28/11/2010" ..__ gespeichert. Ich möchte die Zeichenfolge in ein Datum konvertieren. 

Insbesondere muss ich viele String-Datumsangaben zwischen zwei Datumsangaben konvertieren.

In postgresql verwende ich to_date('30/11/2010','dd/MM/yyyy'). Wie mache ich dasselbe mit sqlite?

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

Da Sqlite keinen Datumstyp hat , müssen Sie dazu einen Stringvergleich durchführen. Damit dies funktioniert, müssen Sie die Reihenfolge umkehren - z. B. von TT/MM/JJJJ bis JJJJMMTD 

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

Datum als TEXT (20.10.2013 03:26) gespeichert. __ Um Datensätze zwischen Datumsabfragen abzufragen und auszuwä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);

das substr vom 20/10/2013 gibt das Format DATE (20131021) des Datums 20131020date an. Dadurch kann SQL mit Datumsangaben arbeiten und Datums- und Uhrzeitfunktionen verwenden.

OR

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');
18
John BG

Eine Sache, die Sie sich ansehen sollten, ist die SQLite-Datums- und -Uhrzeitfunktion , besonders wenn Sie viele Datumsangaben manipulieren müssen. Es ist die vernünftigste Art, Datumsangaben zu verwenden, wobei das interne Format geändert werden muss (muss ISO sein, d. H. JJJJ-MM-TT).

10
MPelletier

Der UDF-Ansatz ist meine Präferenz gegenüber 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 __== "__main__":
    main()

Dadurch erhalten Sie diese Ergebnisse:

[{'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 dieses robuste ist, ist ein verworrenes Geflecht von substr- und instr-Aufrufen, die Sie vermeiden sollten.

3
mattmc3

Wenn das Format des Quelldatums nicht konsistent ist, besteht ein Problem .__ mit der Funktion substr, z.

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

Also folgte ich einem anderen Ansatz mit einer temporären Tabelle. Dieses Snippet gibt 'JJJJ-MM-TT' + Zeit aus, falls vorhanden.

Beachten Sie, dass diese Version das Format Tag/Monat/Jahr akzeptiert. Wenn Sie möchten, dass Monat/Tag/Jahr Die ersten beiden Variablen DayPart und MonthPart vertauscht. Zwei-Jahres-Termine '44 -'99 gehen von 1944-1999 aus, wohingegen '00 -'43 von 2000-2043 ausgehen.

 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;
1
anefeletos

Dies ist für das Datumsformat Fecha (TEXT) JJJJ-MM-TT HH: mm: ss zum Beispiel möchte ich 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);
1
Jesus

Ich habe eine Datenbank, in der Datumsangaben im Format d F Y Gespeichert sind (20. November 2017), und um sie in ein maschinenlesbares Datum (Y-m-d) umzuwandeln, aktualisiere ich die gesamte Tabelle mit dieser Methode in ein geeignetes Format.

Wenn Sie nur das Datum formatieren möchten, schauen Sie sich das Innere an und wählen Sie aus, wie ich das Datum formatiert habe.

update TABLENAME as realTABLE set created_at = (
select 
    -- Get Year
    substr(tmpTABLE.created_at ,-4, 4)
     || '-' ||
     -- Get Month
      substr(
           replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (replace (tmpReis.szAanmaakDatum
           , ' Dec ', '-12-') , ' Nov ', '-11-') , ' Oct ', '-10-') , ' Sep ', '-09-') , ' Aug ', '-08-') , ' Jul ', '-07-') , ' Jun ', '-06-') , ' May ', '-05-') , ' Apr ', '-04-') , ' Mar ', '-03-') , ' Feb ', '-02-') , ' Jan ', '-01-')
           -- Get it from the original space location + 1, then get the two numbers.
           ,instr(tmpTABLE.created_at, ' ')+1, 2)
     || '-' ||
     -- Get day, prepend with a zero if there's a zero lacking.
     substr('00' || tmpTABLE.created_at, -2, 2) as foo
from TABLENAME as tmpTABLE 
where tmpTABLE.id = realTABLE.id    
-- Check for valid matching formats. don't do those that already were converted.
) where created_at like '_ ___ ____' 
or created_at like '__ ___ ____';
0
Tschallacka

Ich speichere das Datum als 'DD-MON-YYYY-Format (10-Jun-2016) und die Abfrage funktioniert bei mir, um Datensätze zwischen zwei Datumsangaben 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');
0
Karthi G