Informazioni sul mio data data cratteggio dati: valido? Vale la pena? Qualcun altro lo sente?

13

Passo molto tempo a rispondere alle domande SQL su SO. Spesso mi imbatto in domande di questo tipo:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

SELECT * FROM person WHERE birthdate BETWEEN 'some string' AND 'other string'

vale a dire. o fare affidamento su una conversione implicita da stringa a data (errata), dei parametri specificati o affidarsi al database convertire x milioni di valori di riga del database in stringa e fare un confronto di stringhe (peggio)

Di tanto in tanto faccio un commento, soprattutto se si tratta di un utente con un alto rappresentante che scrive una risposta intelligente, ma che ritengo debba essere tipicamente meno sciatto / tipizzato con i suoi tipi di dati

Il commento di solito assume la forma che sarebbe probabilmente meglio se convertissero le stringhe in modo esplicito alle date, usando to_date (Oracle), str_to_date (MySQL), convert (SQLSERVER) o un meccanismo simile:

    --oracle
    SELECT * FROM person WHERE birthdate BETWEEN TO_DATE('20170101', 'YYYYMMDD') AND TO_DATE('20170301', 'YYYYMMDD')

    --mysql
    SELECT * FROM person WHERE birthdate BETWEEN STR_TO_DATE('20170101', '%Y%m%d') AND STR_TO_DATE('20170301', '%Y%m%d')

    --SQLS, ugh; magic numbers
    SELECT * FROM person WHERE birthdate BETWEEN CONVERT(datetime, '20170101', 112) AND CONVERT(datetime, '20170301', 112)

La mia giustificazione tecnica per farlo è che è esplicito in merito al formato della data e garantisce che i pochi parametri sorgente diventino definitivamente il tipo di dati della colonna di destinazione. Ciò impedisce qualsiasi possibilità che il database ottenga una conversione implicita errata (l'argomento 3 gennaio / 1 marzo del primo esempio) e impedisce al db di decidere di convertire un milione di valori di data nella tabella in stringhe (utilizzando una data specifica del server formattazione che potrebbe non corrispondere nemmeno al formato della data nei parametri stringa all'interno di sql) per fare il confronto - gli orrori abbondano

La mia giustificazione sociale / accademica per farlo è che SO è un sito di apprendimento; le persone su di esso acquisiscono conoscenze sia implicitamente che esplicitamente. Per colpire un principiante con questa query come risposta:

SELECT * FROM person WHERE birthdate BETWEEN '2017-01-01' AND '2017-03-01'

Potrebbe portarli a pensare che sia sensato, regolando la data per il formato che preferiscono:

SELECT * FROM person WHERE birthdate BETWEEN '01/01/2017' AND '01/03/2017'

Se hanno almeno visto un tentativo esplicito di convertire la data, potrebbero iniziare a farlo per il loro formato di data strano e uccidere alcuni bug per sempre prima che si verifichino. Dopo tutto, noi (I) cerchiamo di dissuadere le persone dall'entrare nell'abitudine di SQL injection (e qualcuno dovrebbe difendere la parametrizzazione di una query e quindi dichiarare al driver che @pBirthdate è una stringa, quando il frontend ha un tipo datetime?)

Torna a quello che succede dopo che ho fatto la mia raccomandazione: di solito ottengo qualche pushback alla raccomandazione "essere esplicito, usa x", come "tutti gli altri lo fanno", "funziona sempre per me", "mostrami un po 'di manuale o un documento di riferimento che dice che dovrei essere esplicito "o anche" che cosa ?? "

Ho chiesto, in risposta ad alcuni di questi, se avrebbero cercato una colonna int facendo WHERE age = '99' passando l'età come una stringa. "Non essere stupido, non abbiamo bisogno di mettere 'durante la ricerca di int" viene la risposta, quindi c'è qualche apprezzamento per diversi tipi di dati nella loro mente da qualche parte, ma forse non c'è alcuna connessione con il salto logico che la ricerca di un int colonna passando una stringa (apparentemente sciocca) e cercando una colonna data passando una stringa (apparentemente sensibile) è ipocrisia

Quindi nelle nostre SQL abbiamo un modo di scrivere le cose come numeri (usa numeri, senza delimitatori), cose come stringhe di stringhe (usa qualsiasi cosa tra i delimitatori degli apostrofi). Perché non esistono delimitatori per le date? È un tipo di dati così fondamentale nella maggior parte dei DB? Questa cosa potrebbe forse essere risolta semplicemente avendo un modo di scrivere una data nello stesso modo in cui javascript ci consente di specificare un'espressione regolare mettendo / su entrambi i lati di alcuni caratteri. %codice%. Perché non avere qualcosa per le date?

In realtà, per quanto ne so, (solo) Microsoft Access in realtà ha simboli che indicano "una data è stata scritta tra questi delimitatori" così possiamo ottenere una buona scorciatoia come /Hello\s+world/ ma la presentazione della data è ancora suscettibile di dare problemi per esempio mm / di vs dd / mm, perché MS ha sempre giocato veloce e libero con le cose che la folla di VB pensava fosse una buona idea

Ritorno al punto principale: sto sostenendo che è saggio essere espliciti con questo mezzo che ci obbliga a passare una moltitudine di diversi tipi di dati come stringhe ..

È un'affermazione valida?

Devo continuare questa crociata? È un punto valido che la tipizzazione a caratteri stringati sia un no-no moderno? O tutti i RDBMS (incluse le versioni antiche) là fuori, quando si spinge una query WHERE datecolumn = #somedate# , sicuramente convertono la stringa in una data e fanno la ricerca senza convertire i dati della tabella / perdere l'uso degli indici? Sospetto di no, almeno per esperienza personale di Oracle 9. Sospetto anche che possano esserci alcuni scenari di allontanamento con essa se le stringhe sono sempre scritte in qualche formato standard ISO, e la colonna ha un certo sapore di data, quindi la il parametro stringa sarà sempre convertito in modo implicito corretto. Questo è giusto?

È un compito utile?

Molte persone non sembrano averlo capito, o se ne importano, o mostrano qualche ipocrisia in quanto sono i loro intatti ma le loro date sono stringhe .. Comune a molti però è che poche persone si sono mai voltate e hanno detto "sai cosa, sono d'accordo con il tuo punto, sarò esplicito sulle mie date d'ora in poi".

    
posta Caius Jard 06.09.2017 - 07:58
fonte

4 risposte

7

Hai scritto:

are those parameters 1st Jan to 3rd Jan, or 1st Mar..

Questa è davvero una potenziale fonte di errori. Indicarlo a un richiedente può essere d'aiuto agli altri lettori, quindi sì, questa è una preoccupazione valida. Tuttavia, per essere costruttivo, vorrei

  • fai riferimento a ANSI SQL e utilizza i letterali DATE o DATETIME da quello standard

  • usa il consueto, non ambiguo formato datetime di uno specifico DBMS (e menziona quale dialetto SQL è usato)

Sfortunatamente, non tutti i DBMS supportano letterali di data ANSI SQL esattamente nello stesso modo (se lo supportano affatto), quindi questo in genere porterà a una variante del secondo approccio. Il fatto che "lo standard" non sia rigidamente implementato da diversi fornitori di DB è probabilmente parte del problema qui.

Nota inoltre, per molti sistemi del mondo reale, le persone possono effettivamente fare affidamento su una specifica localizzazione fissa sul server del database, anche se le applicazioni client sono localizzate, perché c'è solo un tipo di server, sempre configurato allo stesso modo . Pertanto, si presume che il '01 / 03/2017 'abbia il formato fisso' gg / mm / aaaa 'o' mm / gg / aaaa 'per qualsiasi SQL utilizzato nel sistema specifico con cui stanno lavorando. Quindi se qualcuno ti dice "funziona sempre per me", questa forse è davvero una risposta sensata per il suo ambiente . Se questo è il caso, rende meno utile discutere di questo argomento.

Parlando di "motivi di rendimento": finché non ci sono problemi di prestazioni misurabili, questo è abbastanza superstizioso da discutere con "potenziali problemi di prestazioni". Se un database esegue un milione di conversioni string-to-date o probabilmente non importa quando la differenza di fuso orario è solo 1/1000 di secondo, e il vero collo di bottiglia è la rete che fa durare la query per 10 secondi. Quindi meglio mettere da parte queste preoccupazioni finché qualcuno chiede esplicitamente considerazioni sulle prestazioni.

Should I continue this crusade?

Ti dico un segreto: odio le guerre religiose. Non portano a nulla di utile. Quindi, se le specifiche di data / ora ambigue in SQL potrebbero portare a problemi, menzionarle, ma non cercare di forzare le persone a essere più rigide se non apportano loro alcun vantaggio nel loro contesto attuale.

    
risposta data 06.09.2017 - 08:51
fonte
5

La tua crociata non risolve il problema.

Ci sono due problemi separati:

  • conversione di tipo implicita in SQL

  • formati di date ambigue come 05/06/07

Vedo da dove vieni con la tua crociata, ma non penso che la conversione esplicita in realtà risolva il problema a portata di mano:

  • La conversione implicita si verifica ancora in caso di mancata corrispondenza tra i tipi in un confronto. Se una stringa viene confrontata con una data, SQL tenterà di convertire prima la stringa in una data. Pertanto, confrontare una colonna di tipo data con un valore di data convertito in modo esplicito equivale esattamente a confrontarsi con una data in formato stringa. L'unica differenza che vedo è se si confronta un valore di data con una colonna che non contiene effettivamente date ma stringhe, ma questo sarebbe comunque un errore.

  • L'utilizzo della conversione esplicita non risolve l'ambiguità nei formati di data non ISO.

L'unica soluzione che vedo:

  • non confrontare colonne di tipo stringa con valori non stringa.
  • usa sempre solo formati di data di tipo ISO.

E, naturalmente, non memorizzare mai le date in una colonna di tipo stringa. Ma ancora una volta, la conversione esplicita dei valori letterali delle date non impedirà questo.

Probabilmente, le conversioni implicite erano un errore in SQL, ma dato il modo in cui è progettato il linguaggio, non vedo il beneficio della conversione esplicita. Non eviterà comunque la conversione implicita e renderà il codice più difficile da leggere e scrivere.

    
risposta data 06.09.2017 - 11:40
fonte
3

Prima di tutto, hai un punto. Le date non dovrebbero essere messe nelle corde. I motori di database sono bestie complesse in cui non sei mai sicuro al 100% di cosa accadrà esattamente sotto la cappa a causa di una query arbitraria. La conversione in date rende le cose univoche e può aumentare le prestazioni.

MA

Non è un problema che merita l'ulteriore sforzo di riflessione da risolvere per la maggior parte delle persone. Se fosse facile usare i valori letterali delle date in una query, sarebbe facile difendere la tua posizione. Ma non lo è. Per lo più utilizzo SQL Server, quindi cercare di ricordare che il caos per convertire una data non sta succedendo.

Per la maggior parte delle persone, il guadagno in termini di prestazioni è trascurabile. "Perchè si, signor Boss-man, ho passato 10 minuti in più a risolvere questo semplice bug (dovevo google come convertire le date perché quella sintassi è ... speciale ...), ma ho salvato un extra 0.00001 secondi su una query eseguita raramente. " Non farò volare la maggior parte dei posti in cui ho lavorato.

Ma rimuove l'ambiguità nei formati delle date che dici. Ancora una volta, per molte applicazioni (applicazioni interne all'azienda, materiale delle amministrazioni locali, ecc. Ecc.) Non è davvero una preoccupazione. E per quelle applicazioni in cui è una preoccupazione (grandi, applicazioni internazionali o aziendali), che diventa un problema di interfaccia utente / livello aziendale o quelle aziende hanno già un team di DBA ben versati che già lo sanno. TL / DR: se l'internazionalizzazione è una preoccupazione, qualcuno ci sta già pensando e ha già fatto come lei suggerisce (o ha mitigato in altro modo il problema).

So What Now?

Se ti senti così incline, continua a combattere la bella battaglia. Ma non sorprenderti se la maggior parte delle persone non ritiene che questo sia abbastanza importante da preoccuparsi. Solo perché ci sono situazioni in cui è importante, non significa che questa è la situazione di tutti (e probabilmente non lo è). Quindi non essere sorpreso quando si ottiene un po 'indietro per qualcosa che è tecnicamente corretto e migliore ma non rilevante.

    
risposta data 06.09.2017 - 15:00
fonte
2

I'm arguing that it's wise to be explicit with this medium that forces us to pass a multitude of different datatypes as strings.

Supponendo che "date" vengano passate "in" Stringhe allora sì; sono assolutamente d'accordo che hai ragione a farlo.

Quando è "01/04/07"?
* 4 gennaio?
* 1 ° aprile?
* 7 aprile [2001]?

Alcuni o tutti questi potrebbero essere corretti, a seconda di come "il computer" sceglie di interpretarli.

Se hai per creare SQL dinamico con valori letterali al loro interno, la formattazione della data deve essere ben definita e, preferibilmente, indipendente dalla macchina (ne avevo uno strano su un server Windows in cui l'elaborazione basata sulla data all'interno di un servizio di Windows è andata male perché un operatore ha effettuato l'accesso alla console con diverse preferenze di formato data!). Personalmente, utilizzo esclusivamente [d] il formato "aaaa-mm-gg".

Tuttavia ...

La soluzione migliore è quella di utilizzare query parametrizzate che forzano il tipo di dati da convertire prima SQL viene coinvolto - ottenendo un valore "data" in una data Il parametro forza la conversione del tipo all'inizio (rendendolo puramente un problema di codifica, non uno di tipo SQL).

    
risposta data 06.09.2017 - 11:37
fonte

Leggi altre domande sui tag