Sql_celko - 4. fejezet
4. fejezet: Az SQL szabványban szereplő időadatok típusai
4.1. Tippek a dátumok, idők és időbélyegek kezeléséhez
A dátum, az időbélyeg és az idő szintaxisa és képessége annyira erősen függ a konkrét megvalósítástól, hogy csak általános tanácsokat lehet kínálni. Ez a fejezet feltételezi, hogy az SQL-implementáció csak a legegyszerűbb dátummérést támogatja, de talán talál egy olyan könyvtári funkciót, amely hatékonyabban tudja elvégezni az összes munkát. Az SQL-92 szabvány teljes körű végrehajtása előtt részletesen olvassa el a termékkel való munkavégzés kézikönyvét.
4.1.1. Dátum formátum szabványok
4.1.2. A dátum- és időbélyegzőkkel dolgozhat
4.1.3. Idővel dolgozni
4.2. Dátumokat tartalmazó kérések
Szinte az összes SQL-implementáció tartalmazza a DATE adat típusát, de funkcióik eltérnek minden esetben. A leggyakoribb függvény egy olyan konstruktor, amely dátumokat épít ki egész számok és karakterláncok alapján; az extraháló funkciók egy hónapot, egy napot vagy egy évet kivonnak; más beállítások lehetővé teszik a kimenet formázását.
Az SQL végrehajtása a legegyszerűbb dátumfüggvényeket tartalmazza, bár szintaktikája bizonyos mértékben függhet a terméken. Ezek a következő funkciók:
1. A napi, plusz vagy mínusz a napok száma egy új dátumhoz vezet.
2. A dátum mínusz a második dátum eredményeként egész számú napot eredményez.
Nézd meg az érvényes adattípus-kombinációk listáját
Vannak más szabályok, amelyek az időzónákkal való munkavégzéshez és a két operandus relatív pontosságának meghatározásához kötődnek, elég egyértelműek magyarázata nélkül.
Bármely megvalósításban olyan funkciónak kell lennie, amely az aktuális dátumot a rendszeróra alapján állítja vissza. A neve ennek a működése függ a végrehajtás - lehet MA, SYSDATE, az aktuális dátum, getdate (), stb Az is lehetséges, függvény, amely visszaadja a nap a héten alapján dátum, néha nevezik DOW () vagy WEEKDAY () .. Az SQL-92 szabványban vannak CURRENT_DATE, CURRENT_TIME [(
4.3. Személyes naptárak
Most létre kell hoznunk egy táblázatot a tranzakciók és kifizetések dátumával egész évben. INSERT INTO a második táblázat betöltésére vonatkozó nyilatkozatok megadhatók táblázatok segítségével; mindig kényelmes funkciókat tartalmaznak a dátumokkal való munkavégzéshez.
Ügyeljen a naptár egyes funkcióira. Ha nem volt ünnepnap, a tranzakciók és a kifizetések dátumát mindig három napig tartanák szét (CURRENT_DATE + INTERVAL 3 DAYS). Két oszlopból álló táblázatot hozhat létre, így a tranzakció dátumai az egyikben vannak, a másikban (kereskedett + 3 nap). Ezután a táblázatot ASCII formátumban mentjük és betöltjük az adatbázis táblába a következő definícióval:
Most az asztal tele van, mintha ünnepek és szabadnapok nem lennének. Frissítjük, hogy a fizetési napok megfelelően legyenek beállítva. Kezdjük az ünnepeket a listából:
Ez a kód egy nappal előre továbbítja a fizetési dátumot, ha az adott intervallumban ünnep van. Például, ha az ünnep pénteken esik, a dátumot szombatra elhalasztják. Most meg kell adnunk a megfelelő dátumot hétfőtől hétfőig:
Ismételje meg ezt a három frissítést mindaddig, amíg nem kapja meg a rendszeres üzenetet, amely jelzi, hogy a nulla bejegyzés megváltozott. Ez fontos; például elképzelni egy olyan helyzetet, ahol a hétvége, az ünnepek mellett folyamatosan négy napig folytatódik. Ebben az esetben a pénteket hétfőn, hétfőtől keddig szombat, szombat és vasárnap elhalasztják. Mivel a hétfő egy ünnep, minden kinevezését is el kell halasztani keddre. Tehát, hogy az összes dátumot a megfelelő helyeken rendezzék, a frissítés második fordulóját kell elvégeznie.
Ha kicsit csökkenteni szeretné az asztalt, kérjük, vegye figyelembe, hogy minden ügyleti dátumnak munkanapokon kell lennie, nem hétvégén. Ez lehetővé teszi a kész asztal csökkentését kb. 100 sorral (52 kimenet x 2 nap):
Annak megállapításához, hogy miért mozgott a dátum, olvassa el a hétvégék nevét és dátumát a lekérdezésben:
A kapott táblázat körülbelül 250 sort és csak két oszlopot tartalmaz. Nagyon kicsi és könnyen illeszkedik a szinte minden számítógép lemezéhez. A fizetés napjának keresése egy kérelem segítségével történik; Ha csak ünnepi asztalod van, akkor eljárási kódot kell írni.
4.4. Idősor
Az egyik legfontosabb probléma az, hogy szükség van egy azonos időszakban vagy bizonyos sorrendben előforduló eseménysorozatok feldolgozására. Ez a kód meglehetősen bonyolult, nehéz megérteni; az alapötlet az, hogy van egy asztal az események kezdési és befejezési idejével, és csoportról információkat szeretne kapni róluk.
4.4.1. Idősorok intervallumai
Az idősor szakaszokra osztható, majd az ilyen intervallumok kimeneti csoportjait készítheti a jelentések készítésére. Például a bérbeadás egyik fő kérdése az a kérés, hogy megmagyarázza munkáskönyveiben a hiányzó okokat a kérelmező számára. Általában az ilyen mulasztások azt jelentik, hogy a személy nem működött (néha az úgynevezett "tanácsadó volt" - a munkanélküliek szinonimája).
Hozzunk létre egy SQL lekérdezést, amely a jelöltek foglalkoztatási időtartamának (munkanélküliségének) hosszát jeleníti meg. Feltételezzük, hogy az SQL-implementáció olyan DATE függvényeket tartalmaz, amelyek egyszerű matematikai műveleteket hajthatnak végre a naptárral.
Ne feledje, hogy az aktuális munka befejezési dátumát bizonyos távoli időre be kell állítani, vagy el kell távolítania a NOT NULL korlátot és NULL értékeket kell használnia. Az SQL szabvány nem támogatja az olyan értékeket, mint az "örökkévalóság" vagy az idő vége (örökkévalóság vagy az idő vége). Az SQL-ben szereplő legnagyobb dátum "9999-12-31 23:59 '59 .999999", használja.
Nyilvánvaló, hogy egy ilyen kérelemnek tartalmaznia kell a táblázatban a vegyület, de szükséges használni még néhány számtani dátum, hogy tükrözze azt a tényt, hogy az első napon minden időszak tétlen az utolsó nap az előző forgalmas időszakban plusz egy, az utolsó nap a tétlen időszak első napján az alábbi assignments minus 1. A kezdő és záró nap probléma magyarázza a predikátum OVERLAPS SQL-ben való jelenlétét.
Az SQL támogatási dátumok legtöbb verziója és az SQL-92 szabvány meghatározza a dátumminták és számtani műveletek meghatározását a velük való együttműködéshez. Sajnos minden implementációban ezek a típusok különböző módon vannak képviselve, és nincs semmi, mint egyetlen ANSI szabvány. Ebben a példában egy jól olvasható, de nem szabványos jelölést fogunk használni, amelyet lefordíthatunk a termék nyelvére.
A szükséges lekérdezés első kísérlete általában az alábbi listához hasonló eredményhez vezet. Ez a lekérdezés eredményeket ad, de néhány további szükségtelen sor is. Feltételezzük, hogy ha hozzáadásra kerül egy bizonyos számú nap a dátumhoz vagy kivonja őket, új dátumot kap.
Ez a kérés a következő ok miatt nem alkalmas. Tegyük fel, hogy az asztalom tartalmazza Bill Jones jelölt nevét és munkájának történetét:
Az eredményt kapjuk:
A John Smith nevű vonal elég tisztességesnek tűnik, és talán úgy gondolja, hogy a táblázat többi része rendben van. Két helyen dolgozott, és így egy munkanélküli időszakra lenne szüksége. Azonban a Bill Jones vonal többé már nem helytálló - a három foglalkoztatási időszakban két munkanélküli időszakot kell elkülöníteni. A lekérdezés azonban három ilyen rést mutat.
A lekérdezés kapcsolatot létesít az eredeti táblázat kezdő és befejező dátumainak összes lehetséges kombinációjával. Vannak hamis adatok, mivel egy foglalkoztatási időszak vége ("Scut Worker") és egy másik (Grand Poobah) kezdete miatt munkanélküliségi időszaknak tekinti. Csak az utolsó befejezett munkát kell figyelembe venni. Ezt a MAX () függvény és egy másik korrelált kérés segítségével lehet elvégezni. A végeredmény a következő:
4.4.2. Szilárd időtartamok
Ha a pályázó benyújtotta a munkálatok listáját, amelynek kezdetét és végét különböző időpontokban végezték el, meg kell vizsgálni, hogy valóban megszakítás nélkül tényleg dolgozott-e. Hozzunk létre egy munkavállalói ütemtervet az alkalmazott számára:
Szükséges egy keresési feltétel létrehozása a folyamatos időtartam minden jellemzőjére vonatkozóan.
Azonban létrehozhat egy lekérdezést, amely szintén megjeleníti a folyamatos időtartamot korlátozó foglalkoztatási időszakokat:
Ha a Sybase SQL Anywhere programmal dolgozik, módosíthatja ezt a lekérdezést a LIST () karakterlánc-aggregáló függvény bevitelével (részletekért lásd: 21.5. Szakasz).
A LIST () függvény az automatikus rendezést hajtja végre. Ne feledje, hogy az eredmény utolsó sorában valójában az utolsó előtti vonal részhalmaza. Ha meg szeretné menteni a választ egy táblázatban, akkor az összes ilyen alcsoport törölhető a POSITION () karakterlánc függvény segítségével.
Az eljárás alkalmazása dátumokra megengedett, bár meglehetősen nehéz.
4.4.3. Dátum lokalizációja
A probléma megoldásának első megközelítése az, hogy a dátum összetevőinek töredékeit keresési feltételek között használják. Például az összes munkatárs születésnapját megtalálhatja ugyanazon a hónapban:
A megközelítés kiterjesztésének kísérlete azonban nem fog működni, hiszen a 45 napos időszak három hónapig tarthat, és valószínűleg a következő évre is eljuthat, ami szökőév lehet. Hamarosan jelentősen megnő a hívások száma, és a logika sokkal bonyolultabbá válik.
A második megközelítés egy egyszerű keresési feltétel írása ezekkel a feladatokkal, és az aktuális év születésnapjaival a Munkavállaló táblázat születésnapjaitól (dob):
A közönséges nyelvre történő fordítás esetén a kifejezés azt jelenti: ha a munkavállaló 45 napnál régebbi évévé válik, születésnapja ebben az intervallumban van.
4.4.4. A hónap első és utolsó napjai
A dátumok számos módon szerepelhetnek az adatbázisban. Egy termék tervezői inkább egy olyan formátumot preferálhatnak, mint a Cobol nyelv, ahol egy évre, hónapra és napra minden nap külön terület van. Egy másik termék a UNIX stílust is használhatja, ahol a dátumot bizonyos kiindulási ponttól való eltérésként definiálják, néhány kis időegységben kifejezve, és szükség esetén kiszámolják a dátumkijelző formátumot. Az SQL-92 szabványban nem írják le az adattárolási formátumot, de a "Cobol megközelítés" könnyebben megjeleníthető a képernyőn, és az "előfeszített megközelítés" megkönnyíti a számítások elvégzését.
Ennek eredményeképpen kiderül, hogy nincs ideális módja egy adott hónap első vagy utolsó napjának kiszámításához. A Cobol módszerben a hónap első napját a dátum átalakításával kaphatja meg, így a nap mezőjében helyezheti el az 1-es számot.
A hónap utolsó napja némileg nehezebb. A Cobol módszerben rendszerint van olyan funkció, amely dátumot hoz létre az év, a hónap és a nap számszerű mezőiből. Úgy gondolja, hogy a következő hónap első napjának megépítésével és egy nap levonásával belőle megkapja, amire szüksége van:
Gyorsabban működik olyan termékekkel, amelyek az offset-módszert alkalmazzák a dátumokra. Az általános részkifejezést, valamint az INTERVAL kifejezést csak egyszer kell értékelni. A többi az egész számok aritmetikája. Az első nap nagyon egyszerű:
Ne felejtsük el, hogy a megosztás egész szám, mivel a benne lévő változók az egész típusból származnak. Nézze meg az algoritmus másik változatát, amely ugyanazt a számítást végzi:
Ez a program azon a feltételezésen alapul, hogy a CAST (x AS INTEGER) függvény nem csonkítja be x-et; ellenőrizze az SQL processzor dokumentációját.
A következő van írva Pascal eljárás átalakítására Gergely naptár Julian, úgy ez a könyv Numerikus receptek Pascal (Press, Flannery és Vetterling, 1989).
A juliánus nap Gregorian napra való bekapcsolásához tegye a következőket:
Két probléma van ezekkel az algoritmusokkal. Először, a csillagászok délben kezdődnek a júliusi napot. Ha úgy gondolja, van értelme, mert főleg éjszaka dolgozik. A második probléma a számításba bevont teljes számok nagy méretéhez kapcsolódik; nem cserélheti le őket lebegőpontos számokkal, mert a kerekítési hibák jelentősen megnövekedtek. Ezen túlmenően, szükséged lesz sokféle hosszú hosszúságú egész számra (hosszú egész számokra), mivel ezek tartománya 2,5 millió értéket fed le.
4.6. Dátum és idő kitermelési funkciók
Az adattípusokkal való munkához rendelkezésre álló funkciók
Nyilvánvaló, hogy ez a módszer kiterjeszthető más karakterláncfunkciókra is, amelyek lehetővé teszik a dátum vagy az idő, a dátumtartomány stb.
Javasoljuk, hogy olvassa el az SQL termék használatának kézikönyvét, és keresse meg a könyvtári funkcióinak jellemzőit.
4.7. A dátumokkal és időkapcsolatokkal kapcsolatos egyéb funkciók
Nagyon gyakori olyan funkciócsoport, amely nem szerepel az SQL szabványban. Ez a hetekkel való munkavégzés. Például a Sybase SQL Anywhere (korábban WATCOM SQL) tartalmazza a DOW (
A DB2 és XDB SQL termékek tartalmazzák az AGE (
A 10.1. Szakaszban található táblázat az SQL-92 összes érvényes aritmetikai operátorának összefoglalását tartalmazza, beleértve az adattípusokat is
Adattípusú műveletek
Típus műveletek
Az OVERLAPS predikátum határozza meg, hogy két időrendi szegmens keresztezi-e időben (részletesen lásd a 13.2 szakaszt). Ez utóbbit egy pár típusú érték határozza meg
Funkció EXTRACT (
A második probléma sokkoló lehet. Az iskolában tanították, 365,25 nap alatt, és a napszakos részek felhalmozódása négyéves szökőévhez vezet. Tanárok hazudtak - az év során, sőt, 365.2422 nap; 400 évenként felhalmozódik, ez a különbség egy újabb napot ad. Mivel a legtöbben még nem 400 évesek, még nem kellett aggódnunk. Az SQL / PSM-ben a szökőév helyes ellenőrzése így néz ki:
Egy kompaktabb megoldást javasolt a Phil Alexander; A beágyazott kódba a keresési kifejezések közé tartozhat:
4.8.4 Végzetes dátumok az örökölt adatokban