Capitolul 4. Tipuri de date de timp în standardul SQL
4.1. Sfaturi pentru a lucra cu date, ore și timbre
Sintaxa și capacitățile datei, timestampului și timpului sunt atât de puternic dependente de implementarea concretă, încât numai sfaturile generale pot fi oferite. Acest capitol presupune că implementarea SQL suportă doar cea mai simplă dată aritmetică, dar poate veți găsi o funcție de bibliotecă care poate efectua toate lucrările mai eficient. Înainte de implementarea completă a standardului SQL-92 pentru detalii, consultați manualele pentru lucrul cu produsul.
4.1.1. Formatele standardelor de date
4.1.2. Lucrul cu timbrele de dată și oră
4.1.3. Lucrați cu timpul
4.2. Solicitări care conțin date
Aproape toate implementările SQL conțin tipul de date DATE, dar funcțiile lor diferă în fiecare caz. Funcția cea mai obișnuită este constructorul care construiește date bazate pe numere întregi și șiruri; funcțiile extractor extrag o lună, o zi sau un an; alte opțiuni vă permit să formatați ieșirea.
Vă puteți aștepta ca implementarea SQL să conțină cele mai simple funcții ale datei, deși sintaxa lor poate depinde într-o oarecare măsură de produs. Acestea sunt următoarele funcții:
1. Data plus sau minus numărul de zile conduce la o nouă dată.
2. Data minus cea de-a doua dată are ca rezultat un număr întreg de zile.
Uită-te la lista de combinații de date valide valide
Există și alte reguli referitoare la lucrul cu fusurile orare și determinarea relativă a acurateței celor doi operanzi, sunt suficient de clare fără explicații.
În orice implementare, trebuie să existe o funcție care returnează data curentă pe baza ceasului sistemului. Numele acestei funcții depinde de punerea în aplicare - poate fi astăzi, SYSDATE, DATA DE CURENT, getDate (), etc De asemenea, este posibil, funcție care returnează o zi a săptămânii, pe baza data, uneori, se numește DOW () sau ZISĂPT () .. În standardul SQL-92 există funcții CURRENT_DATE, CURRENT_TIME [(
4.3. Calendare personale
Acum trebuie să creați un tabel cu datele tranzacțiilor și plăților pentru întregul an. INSERT INTO instrucțiunile pentru încărcarea celui de-al doilea tabel pot fi specificate folosind foi de calcul; ele conțin întotdeauna funcții convenabile pentru lucrul cu datele.
Acordați atenție anumitor caracteristici ale calendarului. Dacă nu s-au efectuat sărbători, datele tranzacțiilor și plăților vor fi întotdeauna separate timp de trei zile (CURRENT_DATE + INTERVAL 3 DAYS). Puteți crea o foaie de calcul de două coloane, deci datele tranzacției vor fi în una dintre ele, iar în cealaltă (tranzacționați + 3 zile). Apoi salvăm foaia de calcul în format ASCII și o încarcăm în tabela de baze de date cu următoarea definiție:
Acum masa este plină, ca și cum nu ar fi sărbătorile și zilele libere. Vom actualiza acest lucru astfel încât zilele de plată să fie setate corect. Să începem cu sărbătorile din listă:
Acest cod deplasează data plății o zi înainte dacă există o vacanță în intervalul dat. De exemplu, în cazul în care sărbătoarea se încadrează vineri, data va fi amânată până sâmbătă. Acum trebuie să transferăm data corespunzătoare de luni până luni:
Repetați aceste trei actualizări până când primiți un mesaj de sistem prin care se specifică faptul că înregistrările zero au fost modificate. Acest lucru este important; de exemplu, imaginați-vă o situație în care week-end-ul, împreună cu sărbătorile, continuă continuu timp de patru zile. În acest caz, vineri este amânată sâmbătă, sâmbătă și duminică luni și luni până marți. De luni este o sărbătoare, toate numirile sunt, de asemenea, amânate până marți. Deci, pentru a aranja toate datele în locurile potrivite, trebuie să faceți oa doua rundă a actualizării.
Dacă doriți să reduceți puțin masa, vă rugăm să rețineți că toate datele tranzacției trebuie să fie în zilele lucrătoare, nu în weekend. Acest lucru vă permite să reduceți masa finită cu aproximativ 100 de linii (52 ieșiri x 2 zile):
Pentru a afla de ce data a fost mutată, trebuie să citiți numele și datele weekendului din interogare:
Tabelul rezultat va conține aproximativ 250 de rânduri și numai două coloane. Este foarte mic și se potrivește cu ușurință pe discul aproape de orice computer. Căutarea zilei de plată se efectuează cu ajutorul unei singure solicitări; Dacă aveți doar o masă de sărbători, va trebui să scrieți un cod procedural.
4.4. Serii de timp
Una dintre problemele majore este legată de necesitatea de a procesa o serie de evenimente care au loc în aceeași perioadă de timp sau într-o anumită ordine. Codul pentru acest lucru este destul de complicat, este greu de înțeles; ideea de bază este că aveți un tabel cu orele de început și de sfârșit ale evenimentelor și doriți să obțineți informații despre ele ca grup.
4.4.1. Intervale în serii de timp
Linia de timp poate fi împărțită în intervale și apoi grupurile de ieșiri din astfel de intervale pentru crearea rapoartelor. De exemplu, una dintre principalele întrebări privind angajarea este solicitarea de a explica motivele absențelor din cartea sa de lucru pentru reclamant. De regulă, astfel de omisiuni înseamnă că persoana nu a lucrat (uneori se numește: "a fost consultant" - un sinonim pentru șomeri).
Să creăm o interogare SQL care arată durata perioadelor de angajare (șomaj) pentru candidați. Se presupune că implementarea SQL conține funcții DATE care pot efectua operații matematice simple cu calendarul.
Rețineți că data de încheiere a lucrării curente trebuie setată pentru o anumită perioadă de timp, sau puteți elimina constrângerea NOT NULL și puteți utiliza valorile NULL. Standardul SQL nu acceptă valori precum "eternitatea" sau sfârșitul timpului "(eternitatea sau sfârșitul timpului). Cea mai mare dată reprezentată în SQL este '9999-12-31 23:59 '59 .999999', folosiți-o.
Evident, o astfel de cerere trebuie să conțină, cu masa de compus, dar este necesar să se utilizeze, de asemenea, o dată aritmetică, pentru a reflecta faptul că prima zi a fiecărei perioade de inactivitate este ultima zi a perioadei de ocupat anterior, plus o, iar ultima zi a perioadei de inactivitate este prima zi a următoarei misiuni minus 1. Problema zilei de început și de sfârșit explică prezența în predicția OVERLAPS a predicatului.
Cele mai multe versiuni ale datelor de suport SQL, iar în standardul SQL-92 există o definiție a tipurilor de date și a operațiilor aritmetice pentru a lucra cu ele. Din păcate, în toate implementările, aceste tipuri sunt reprezentate în moduri diferite și nu există nimic asemănător cu un singur standard ANSI. În acest exemplu, va fi utilizată o notație bine citită, dar non-standard, pe care o puteți traduce în limba produsului dvs.
Prima încercare de a compune interogarea necesară duce de obicei la ceva similar cu lista următoare. Această interogare produce rezultatele corecte, dar și câteva rânduri suplimentare inutile. Se presupune că adăugând un anumit număr de zile până la data respectivă sau scăzându-le, veți primi o nouă dată.
Această solicitare nu este potrivită din următorul motiv. Să presupunem că tabelul meu conține numele candidatului Bill Jones și istoria operei sale:
Rezultatul obținut:
Linia numită John Smith pare destul de decentă și ați putea crede că restul mesei este în regulă. A lucrat în două locuri și, prin urmare, ar trebui să aibă o perioadă de șomaj. Cu toate acestea, linia Bill Jones nu mai poate fi corectă - cele trei perioade de angajare a acesteia ar trebui să separe două perioade de șomaj. Cu toate acestea, interogarea afișează trei astfel de lacune.
Interogarea aplică o conexiune la toate combinațiile posibile ale datelor de început și de sfârșit ale tabelului original. Există date false, deoarece sfârșitul unei perioade de angajare ("Scut Worker") și începutul celeilalte (Grand Poobah), pe care îl consideră o perioadă de șomaj. Trebuie să luăm în considerare doar ultima lucrare finalizată. Acest lucru se poate face folosind funcția MAX () și o altă subcotare corelată. Rezultatul final este următorul:
4.4.2. Perioade de timp solide
Dacă solicitantul a înaintat o listă de lucrări, începutul și sfârșitul cărora au fost la momente diferite, este necesar să se urmărească dacă a lucrat într-adevăr tot timpul fără întrerupere. Să creați un program al angajatului pentru angajat:
Este necesar să se creeze o condiție de căutare pentru toate caracteristicile unei perioade continue de timp.
Cu toate acestea, puteți să compuneți o interogare care afișează, de asemenea, perioade de angajare care limitează o perioadă continuă:
Dacă lucrați cu Sybase SQL Anywhere, puteți modifica această interogare prin includerea în ea a funcției agregate șir LIST () (pentru detalii, consultați secțiunea 21.5).
Funcția LIST () efectuează sortarea automată. Rețineți că ultimul rând al rezultatului este de fapt un subset al liniei penultimate. Dacă doriți să salvați un răspuns într-un tabel, toate aceste subseturi pot fi șterse utilizând funcția de șir POSITION ().
Aplicarea acestei proceduri la date este permisă, deși este destul de dificilă.
4.4.3. Localizarea datei
Prima abordare a rezolvării problemei constă în utilizarea fragmentelor componentelor date în condițiile de căutare. De exemplu, puteți găsi zilele de naștere ale tuturor angajaților în aceeași lună:
Cu toate acestea, o încercare de a extinde această abordare nu va funcționa, deoarece perioada de 45 de zile poate dura trei luni și, eventual, poate merge în anul următor, ceea ce se poate dovedi a fi un salt alocat. Foarte curând numărul de apeluri către funcție va crește semnificativ, iar logica va deveni mult mai complicată.
A doua abordare este de a scrie o simplă condiție de căutare cu aceste funcții și de a construi împreună cu ea zilele de naștere din anul curent din zilele de naștere (dob) ale tabelului Angajat:
În traducerea în limbaj obișnuit, expresia înseamnă: dacă angajatul devine un an mai vechi de 45 de zile, ziua lui de naștere este în acest interval.
4.4.4. În prima și ultima zi a lunii
Datele pot fi incluse în baza de date într-o varietate de moduri. Designerii unui singur produs pot prefera un format cum ar fi limba Cobol, unde pentru un an, lună și zi în fiecare dată există o zonă separată. Un alt produs poate folosi stilul UNIX, unde data este definită ca o compensare de la un anumit punct de pornire, exprimată în câteva unități mici de timp, iar formatul de afișare a datei este calculat dacă este necesar. În standardul SQL-92, formatul de stocare a datelor nu este descris, însă abordarea "Cobol" este mai ușor de afișat pe ecran, iar abordarea "biasing" face mai ușor efectuarea calculelor.
În consecință, se pare că nu există o modalitate ideală de a calcula prima sau ultima zi a unei luni. În metoda Cobol, puteți obține prima zi a lunii prin conversia datei astfel încât în câmpul zilei să puneți numărul 1.
Ultima zi a lunii este ceva mai greu de obținut. În metoda Cobol, de regulă, există o funcție care construiește o dată din câmpurile numerice ale anului, lunii și zilei. S-ar putea să vă gândiți că, construind prima zi a lunii următoare și scăzând o zi de la ea, veți obține ceea ce aveți nevoie:
Funcționează mai repede cu produsele care aplică metoda offset la date. Subexpresia generală, precum și expresia INTERVAL, sunt evaluate o singură dată. Tot restul este aritmetica muncii cu intregi. Prima zi este foarte simpla:
Amintiți-vă că diviziunea este un întreg, deoarece variabilele implicate în ea sunt de întregul tip. Uită-te la o altă versiune a algoritmului care efectuează același calcul:
Acest program se bazează pe presupunerea că funcția CAST (x AS INTEGER) trunchează x, mai degrabă decât rotunjind-o; verificați documentația procesorului dvs. SQL.
Mai jos este procedura de convertire a datelor gregorian la Julian, scrisă în Pascal, din cartea Numerical Recipes în Pascal (Press, Flannery and Vetterling, 1989).
Pentru a transforma ziua Julian într-o zi gregoriană, procedați în felul următor:
Există două probleme cu aceste algoritmi. În primul rând, astronomii încep ziua de iuliană la prânz. Dacă te gândești la asta, are sens, pentru că își fac munca mai ales noaptea. A doua problemă este legată de dimensiunile mari ale numerelor întregi implicate în calcul; nu le puteți înlocui cu numere în virgulă mobilă, deoarece erorile de rotunjire sunt mult mai mari. Mai mult decât atât, veți avea nevoie de numere de tip integer lung (numere întregi lungi), deoarece gama lor acoperă 2,5 milioane de valori.
4.6. Funcțiile de extragere a datei și orei
Funcțiile disponibile pentru lucrul cu tipurile de date
Evident, această metodă poate fi extinsă la alte funcții de șir, ceea ce va permite căutarea fragmentelor datei sau timpului, intervalelor de date etc.
Este recomandat să citiți manualul pentru utilizarea produsului dvs. SQL și să căutați o descriere a caracteristicilor funcțiilor sale de bibliotecă.
4.7. Alte funcții pentru lucrul cu datele și orele
Există un grup comun de funcții care nu sunt reprezentate în standardul SQL. Aceasta este funcția de a lucra cu săptămâni. De exemplu, Sybase SQL Anywhere (fostul WATCOM SQL) conține DOW (
Produsele DB2 și XDB SQL conțin vârsta (
Tabelul din secțiunea 10.1 oferă un rezumat al tuturor operatorilor aritmetici valabili din SQL-92, inclusiv tipurile de date
Operații cu tip de date
Operațiuni de tip
Predicatul OVERLAPS determină dacă două segmente cronologice se intersectează în timp (pentru detalii, consultați secțiunea 13.2). Ultimul este determinat fie de o pereche de valori de tip
Funcție EXTRACT (
A doua problemă poate fi șocantă. Așa cum am fost învățați la școală, în anul 365,25 de zile, iar acumularea de părți fracționate ale zilei duce la un an bătător la fiecare patru ani. Profesorii au mințit - în anul, de fapt, 365,2422 de zile; acumulând, la fiecare 400 de ani, această diferență dă o altă zi. Deoarece majoritatea dintre noi nu au încă 400 de ani, nu a trebuit să ne îngrijorăm încă acest lucru. În SQL / PSM, verificarea corectă a unui an de salt seamănă astfel:
O soluție mai compactă a fost propusă de Phil Alexander; Poate fi inclus în codul încorporat ca expresie pentru căutare:
4.8.4 Date fatale în datele moștenite
Articole similare
Trimiteți-le prietenilor: