În ziua de azi, la un singur forum MySQL, am citit o întrebare care ma interesat și a condus la scrierea acestui articol. Întrebarea a fost aceea de a stoca data de compensare și la momentul potrivit pentru a o rezuma cu o anumită dată. Să încercăm să argumentăm acest subiect.
Built-in capabilități MySQL.
Documentația pentru MySQL are multe funcții cu data și ora. Luați în considerare posibilitățile de transformare. Unele caracteristici există în MySQL cu mult timp în urmă, iar unele au apărut în versiunile 4.1.1 și 5.0.1.
Toate funcțiile de transformare date încorporate nu funcționează cu date incomplete, atunci când luna sau ziua este zero și returnează NULL. Datele incomplete arată astfel:
Funcția funcționează cu data - DATE_ADD () și DATE_SUB () - face posibilă adăugarea la data inițială și o scad intervalele de timp în unități diferite de timp de ani, trimestre, luni, saptamani, zile, ore, minute, secunde, și în unele combinații ale acestora. În funcție, puteți specifica doar un singur interval. Din motive naturale, nici unul dintre ele sunt combinații de an, trimestru, luna, zilele săptămânii și unități mai mici.
Ușor mai flexibil pentru a calcula offsetul de date este posibil dacă utilizați intervale aritmetice:
data + unitate expr INTERVAL
data - unitate expr INTERVAL
Acest formular vă permite să construiți expresii compuse. De exemplu:
ACUM # 40; # 41; - INTERVAL 1 LUNĂ + INTERVAL 3 ORE
Când lucrați cu intervale de luni și trimestre, rețineți că în lunile cu un număr diferit de zile și în calcul, este posibil să "rotunji" luna până în ultima zi a lunii rezultatului:
De asemenea, trebuie să ne amintim că în expresiile compuse ordinea operațiilor este importantă - regulile sumării aritmetice nu sunt aplicabile aici. De exemplu, următoarele comenzi duc la rezultate diferite:
O funcție foarte utilă care calculează ultima zi a lunii. Funcția a apărut în versiunea 4.1.1. Vă permite să obțineți nu numai ultima zi a lunii la data specificată, ci și să calculați prima zi:
LAST_DAY # 40; ACUM # 40; # 41; # 41; + INTERVAL 1 ZI - INTERVAL 1 LUNĂ
Înregistrarea cu o mulțime de INTERVAL pare greoaie. Dacă apare o dată în instrucțiunea SQL, nu este teribil, dar dacă trebuie să utilizați o expresie similară în instrucțiune de mai multe ori, este ușor să obțineți confuzie.
Ne fixăm și ne extindem.
Să încercăm să vă creați propriile funcții, permițându-vă să faceți lucruri care nu sunt convenabile sau greoaie pentru a scrie cu funcții încorporate.
Prima zi a lunii.
Metoda standard de programare este eliminarea expresiei greoaie și repetitive a funcției.
CREAȚI FUNCȚIA BEGIN_OF_MONTH # 40;
p_date pe data de DATE
# 41;
RETURNS pe data de DATE
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN LAST_DAY # 40; p_date # 41; + INTERVAL 1 ZI - INTERVAL 1 LUNĂ;
Se pare că a devenit mai ușor, mai compact și mai ușor de înțeles:
Sincer, nu înțeleg de ce a fost să scurteze LAST_DAY_OF_MONTH () la LAST_DAY () - nu este evident. În general, aș numi acest lucru END_OF_MONTH () - și în mod clar și nu prea mult.
CREAȚI FUNCȚIA END_OF_MONTH # 40;
p_date pe data de DATE
# 41;
RETURNS pe data de DATE
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN LAST_DAY # 40; p_date # 41; ;
Prima și ultima zi a anului, trimestru sau săptămână.
Nu există funcții încorporate care să permită calcularea acestora. Prin urmare, noi o facem noi înșine.
CREAȚI FUNCȚIA BEGIN_OF_YEAR # 40;
p_date pe data de DATE
# 41;
RETURNS pe data de DATE
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN MAKEDATE # 40; ANUL # 40; p_date # 41;. 1 # 41; ;
CREAȚI FUNCȚIA END_OF_YEAR # 40;
p_date pe data de DATE
# 41;
RETURNS pe data de DATE
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN MAKEDATE # 40; ANUL # 40; p_date # 41; + 1. 1 # 41; - INTERVAL 1 ZIUA;
CREAȚI FUNCȚIA BEGIN_OF_WEEK # 40;
p_date pe data de DATE
# 41;
RETURNS pe data de DATE
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN p_date - INTERVAL WEEKDAY # 40; p_date # 41; ZIUA;
Funcțiile END_OF_WEEK (). BEGIN_OF_QUARTER () și END_OF_QUARTER () nu pictează - nu este greu să ghici cum arată. Codul funcției poate fi găsit în anexa la articol.
Rezumând date incomplete.
Deși funcțiile încorporate nu permit funcționarea pe date incomplete, MySQL le permite să fie stocate în baza de date. La urma urmei, o dată incompletă, într-un sens, un interval. Să scriem funcția de sumare a datei complete cu incompletă.
CREATE FUNCTION DATE_ADD_OFFSET # 40;
p_date DATETIME.
p_offset DATETIME
# 41;
RETURNS DATETIME
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN p_date
+ ANUL INTERVAL # 40; p_offset # 41; ANUL
+ INTERVAL LUNĂ # 40; p_offset # 41; LUNA
+ ZI DE INTERVAL # 40; p_offset # 41; ZIUA
+ INTERVAL MAKETIME # 40;
ORĂ # 40; p_offset # 41;.
MINUTE # 40; p_offset # 41;.
A DOUA # 40; p_offset # 41;
# 41; HOUR_SECOND;
În mod similar, funcția inversă arată ca DATE_SUB_OFFSET ().
Conversia DATE incompletă în DATETIME trece fără erori.
CREATE FUNCTION pe DATE_TO_DATETIME # 40;
p_date pe data de DATE
# 41;
RETURNS DATETIME
DETERMINISTIC
CONȚINE SQL
INVOKER DE SECURITATE SQL
RETURN p_date;
Nu puteți adăuga DATE la TIME sau la un număr.
Se pare ceva obscen: ambii operanzi devin numere reale și sumar prost:
Aflați data zilei săptămânii.
Găsirea zilei din săptămână după dată este ușoară - utilizați funcția încorporată WEEKDAY (). Este o altă chestiune de a ști care este data de vineri a celei de-a doua săptămâni sau a ultimei zile de sâmbătă a lunii.
Codul funcției FIND_WEEK_DAY () este în aplicație. Primul parametru este data (indică luna), al doilea este numărul săptămânii, al treilea este ziua săptămânii (în aceeași numerotare a zilelor cu ZIUA (). Să verificăm.
Valoarea pozitivă a săptămânii este utilizată pentru căutarea de la începutul lunii specificate și nu se limitează la luna în sine - puteți găsi chiar și cea de-a treisprezecea înviere duminică.
Valoarea negativă a săptămânii este utilizată pentru căutarea inversă de la sfârșitul lunii specificate și nu se limitează la o lună.
Valoarea zero a săptămânii este folosită pentru a găsi următoarea zi a săptămânii dorită de la data specificată.
A existat și ideea de a face o căutare inversă de la data specificată, dar mâinile mele nu au ajuns.
Transformări multiple într-o funcție.
Cum să faci mai multe conversii de dată și de timp, nu o grămadă de calcule?
Aparent, trebuie să scriem această mulțime de calcule și să scriem o funcție care să înțeleagă ceea ce vrem de la ea și să realizăm această mulțime de calcule.
Din motive de flexibilitate, lizibilitate și performanță, totuși, să separăm părțile funcționale individuale din funcțiile DATE_RANGE () și DATE_ADD_INTERVAL (). și vom pune procesarea secvenței regulilor în DATE_TRANSFORM (). Cod, din cauza greoi, nu citez aici - a se vedea apendicele.
Pentru DATE_TRANSFORM (), formatul regulii de conversie:
Regulile sunt scrise fără delimitatori și sunt executate secvențial.
Pentru o înțelegere mai bună, voi da o serie de exemple.
Prima zi a lunii următoare. Calcul ca: următoarea lună, prima zi a lunii:
Nouă dimineața zilei a treia a celei de-a doua luni a trimestrului precedent. Imaginați-vă cum ar arăta această formulă folosind funcțiile INTERVAL și built-in! La noi toți sunt mult mai ușor:
Scripturi SQL utilizate în articol.
- Un set de funcții pentru a obține începutul și sfârșitul intervalului.
- Începutul și sfârșitul intervalului pentru tipul DATETIME este funcția DATE_RANGE ().
- Găsiți ziua din săptămână - funcția FIND_WEEK_DAY ().
- Metoda redusă a aritmeticii intervalului este funcția DATE_ADD_INTERVAL ().
- Conversii complexe de tipul DATETIME - funcția DATE_TRANSFORM ().
Întrebări despre articol și despre bazele de date pe care vă rog să le întrebați pe forumul nostru - "Merry Wu".
Unele interogări utile de depanare.
Pentru comoditatea depanării, vă recomandăm câteva linii de cod.
Afișează toate funcțiile din schema "test":
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
ȘI ROUTINE_TYPE = 'FUNCȚIE';
Generă codul pentru a elimina toate funcțiile din schema "test":
SELECT GROUP_CONCAT # 40; CONCAT # 40; "FUNCȚIA DROP". ROUTINE_NAME. ';' # 41; SEPARATOR " # 41;
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'test'
ȘI ROUTINE_TYPE = 'FUNCȚIE';