Mysql-functies voor het werken met datum en tijd. Kalendergegevenstypen in MySQL: gebruiksfuncties

publicatie van dit artikel is alleen toegestaan met een link naar de website van de auteur van het artikel

Zoals u weet worden alle datums in MySQL opgeslagen in de omgekeerde volgorde van jaar, maand en dag (18-10-2008), soms zelfs zonder scheidingsteken (20081018).
Om de datum weer te geven, moet u deze naar een normaal leesbare vorm converteren.

Er zijn twee conversiemethoden: effectief en niet erg effectief.
Een ineffectieve manier is wanneer de datumuitvoer van mysql wordt geconverteerd van met behulp van php.
Persoonlijk heb ik dit zelf gedaan voor een lange tijd. Voordat ik de datum weergaf, heb ik elke datum omgekeerd met behulp van een php-functie.
Als het aantal transformaties niet groot is, dan kun je de datum omkeren met behulp van PHP, daar is niets mis mee, maar als je tien- of honderdduizenden records eruit moet halen en de datum in elk record moet converteren, dan moet je natuurlijk converteren data met mysql zullen veel sneller zijn.

Er is een geweldige functie in mysql genaamd DATE_FORMAT(), deze lijkt erg op php-functie datum().
Hier is een voorbeeld van gebruik

SELECT DATE_FORMAT("2008-11-19","%d.%m.%Y");
resultaat

Alles is heel eenvoudig en snel, het is niet nodig om datums te wijzigen met behulp van php.
Hier vindt u een lijst met definities voor deze functie

Determinant	Beschrijving
%M	Naam van de maand (januari...december)
% W	Naam van de dag van de week (zondag...zaterdag)
%D	Dag van de maand met Engels achtervoegsel (0st, 1st, 2nd, 3rd, etc.)
%J	Jaar, datum, 4 cijfers
%y	Jaar, datum, 2 cijfers
%X	Jaartal voor een week waarin zondag wordt beschouwd als de eerste dag van de week, getal, 4 cijfers, gebruikt met "%V"
%X	Jaartal voor een week waarin zondag wordt beschouwd als de eerste dag van de week, getal, 4 cijfers, gebruikt met "%v"
%A	Verkorte naam van de dag van de week (zon...za)
%D	Dag van de maand, nummer (00..31)
% e	Dag van de maand, nummer (0..31)
%M	Maand, dag (00..12)
%C	Maand, datum (0..12)
%B	Verkorte naam van de maand (jan...dec)
%J	Dag van het jaar (001..366)
%H	Uur (00..23)
%k	Uur (0..23)
%H	Uur (01..12)
%I	Uur (01..12)
%l	Uur (1..12)
%i	Minuten, aantal (00..59)
%R	Tijd, 12-uursnotatie (uu:mm:ss M)
%T	Tijd, 24-uursnotatie (uu:mm:ss)
%S	Seconden (00..59)
%S	Seconden (00..59)
%P	AM of PM
%w	Dag van de week (0=zondag..6=zaterdag)
%U	Week (00..53), waarbij zondag wordt beschouwd als de eerste dag van de week
%u	Week (00..53), waarbij maandag wordt beschouwd als de eerste dag van de week
%V	Week (01..53), waarbij zondag wordt beschouwd als de eerste dag van de week. Gebruikt met `%X'
%v	Week (01..53), waarbij maandag wordt beschouwd als de eerste dag van de week. Gebruikt met `%x'
%%	Letterlijk `%'.

Opmerkingen

27.11.2008 ----
Hey shaitaan!!!
Zelf werk ik al vijf jaar aan php en mysql, en de hele tijd veranderde ik de datum in php...
Het kwam niet eens bij me op dat het eenvoudiger zou zijn om de ingebouwde mysql-functie te gebruiken

28-11-2008 Zheka
Insgelijks! Ik heb altijd mijn php-functie gebruikt

12/03/2008 Sergej
Welnu, gebruikt iemand deze aanpak in het algemeen?
Of gebruikt iedereen php om de datum om te draaien?
Persoonlijk heb ik nog nooit een datum teruggedraaid in mysql, ik doe nog steeds alles in php

28/06/2009 Ilja
Helaas werkte niets :(

07/08/2009 Vitaly
Cool, bedankt voor de functie. Ik vraag me af welke andere kronkels er zijn?

14/07/2009 DSt
Bedankt, het heeft veel geholpen. Het enige dat overblijft is om de naam van de maand in het Russisch weer te geven)

28-07-2009 Vlad
mysql=>PHP
selecteer unix_timestamp(start_date) als start_date_php
php-code
date("d.m.J",$row["start_datum_php"])

PHP=>MijnSQL
update tabelset start_date=DATE_FORMAT(STR_TO_DATE("19-12-2009 18:35:22 PM","%d.%m.%Y %H:%i"),"%Y.%m.%d % Hoi")

18-08-2009 Gast
2:D Sint
Er is zo'n prachtige functie:
ELT(MAAND("2004-04-10"), "jan.", "februari", "maart", "april", "mei", "juni", "juli", "aug.", sep. "," okt. "," nov. "," dec.")

Gebruik het. :-)

29-10-2009 Vladimir
Bedankt, over ELT(MAAND("2004-04-10"), "jan.", "februari", "maart", "apr.", "mei", "juni", "juli", "aug , "sep.", "okt.", "nov.", "dec.")
Ik heb het niet gehoord.

10/07/2010 Evgeniy
Wat werkt sneller? Conversie in de request, of als gevolg van de PHP-functie?

10/07/2010 verdediger
Er is op zijn minst minder geheugen om te verwerken, minder functieaanroepen, minder geheugentoewijzingen... Ik gebruik constant zoiets als dit, maar niet in mysql, maar in postgresql.

10/08/2010 Beheerder
Evgeny, verdediger heeft het terecht gezegd, deze transformatie zou mooier moeten werken via de database, maar natuurlijk als dat zo is waar we het over hebben over het extraheren van enorme hoeveelheden gegevens.
Als u 10-20 records ophaalt, maakt het niet uit hoe u de datum converteert in vergelijking met andere ladingen, dit is een kleinigheidje.

27-01-2011 pcemma
uhh dankzij de aftor (: het is niet langer nodig om mijn mega coole functie te gebruiken voor conversie (:

13-04-2011 Xen
HOE HET TE GEBRUIKEN IN PHP
terwijl ($sqlr=mysql_fetch_array($sql))
{
echo ($sqlr["comadd"]." ".$sqlr["commentaar"]."

");

$sqlr["comadd"] - Moet u het in normale vorm presenteren?
}

14/04/2011 Vitaly
Ik heb een datum in de database in het formaat 19-11-2008, tabeltype VARCHAR, hoe kan ik deze in de database herschrijven in het formaat 2008-11-19?
Handen gewoon voor een hele lange tijd...
Bedankt.

15/04/2011 beheerder
Xes, dit is een MySQL-functie, je moet deze gebruiken SQL-query, die zich, afgaande op uw code, ergens hierboven bevindt. Het kan niet worden gebruikt in dit codegedeelte.

15/04/2011 beheerder
Vitaly, verander gewoon het celtype in DATE, mysql converteert automatisch alle gegevens in deze cel naar het formaat 2008-11-19.
Maar voor het geval dat, voordat u het celtype wijzigt, moet u een dump van deze tabel maken, want plotseling zal de database iets verkeerd doen en zal de tabel helemaal kapot gaan.

Als het belangrijk voor u is om het veldtype varchar te laten, stel het DATE-type dan opnieuw in op varchar... nadat u het DATE-type hebt ingesteld.

Dit is de eenvoudigste optie, maar niet helemaal correct, maar ik heb gecontroleerd of het werkt.

14-05-2011 DDD
date("d-m-Y",strtotime("$mijnrij"));

24/05/2011 Konstantin
en ik neem altijd SELECT *,UNIX_TIMESTAMP(created) AS gemaakt VAN...
Maar met een motor kan ik het in elk formaat doen. Zelfs maar een dag, zelfs maar een tijd...
en vergelijk wat groter is dan 14-05-2011 of 14-05-2010...
En ik schrijf het zo:
...date=".gmdate("J-m-d H:i:s",$created)...
en over het algemeen zie ik geen reden om gewoonten te veranderen

24/05/2011 Sergej
Konstantin, ik gebruik het zelf voor uitvoer php-datum(), kijken we alleen naar de mogelijkheid om de datum niet via PHP, maar via mysql te converteren.

Ik zou zeggen dat dit slechts een overzicht is van de mysql-functie en dat deze zeker bestaansrecht heeft...

Deze functies zijn ook ontworpen om te werken met kalendergegevenstypen. Laten we ze eens nader bekijken.

DATE_FORMAT(datum, formaat) formatteert de datum volgens het geselecteerde formaat.

Deze functie wordt heel vaak gebruikt. In MySQL is het datumformaat bijvoorbeeld JJJJ-MM-DD (jaar-maand-dag), terwijl we meer bekend zijn met het formaat DD-MM-JJJJ (datum-maand-jaar). Om de datum zoals gewoonlijk weer te geven, moet deze daarom opnieuw worden geformatteerd.

Laten we eerst de vraag stellen en vervolgens uitzoeken hoe we het formaat kunnen instellen:

SELECT DATE_FORMAT(CURDATE(), "%d.%m.%Y");	Beschrijving
%A	Nu komt de datum ons bekend voor. Om het datumformaat te specificeren, worden speciale kwalificaties gebruikt. Voor het gemak vermelden we ze in de tabel. Defred Verkorte naam van de dag van de week (ma - maandag, di - dinsdag, wo - woensdag, do - donderdag, vr - vrijdag, za - zaterdag, zo - zondag). Voorbeeld:
%B	SELECTEER DATE_FORMAT(CURDATE(), "%a"); Defred Resultaat: Voorbeeld:
%C	Verkorte naam van de maanden (Jan - Januari, Feb - Februari, Mar - Maart, Apr - April, Mei - Mei, Jun - Juni, Jul - Juli, Aug - Augustus, Sep - September, Okt - Oktober, Nov - November, december - december). Defred SELECTEER DATE_FORMAT(CURDATE(), "%b"); Voorbeeld:
%D	Maand in numerieke vorm (1 - 12). Defred SELECTEER DATE_FORMAT(CURDATE(), "%s"); Voorbeeld:
%D	Dag van de maand in numerieke vorm met een nul (01 - 31). Defred SELECTEER DATE_FORMAT(CURDATE(), "%d"); Voorbeeld:
% e	Dag van de maand in numerieke vorm zonder nul (1 - 31). Defred SELECTEER DATE_FORMAT(CURDATE(), "%e"); Voorbeeld:
%H	Uren met voorloopnul van 00 tot 23. Defred SELECT DATE_FORMAT("2011-04-15 23:03:20", "%H"); Voorbeeld:
%H	Klok met voorloopnul van 00 tot 12. Defred SELECT DATE_FORMAT("2011-04-15 23:03:20", "%h"); Voorbeeld:
%i	Minuten van 00 tot 59. Defred SELECT DATE_FORMAT("2011-04-15 23:03:20", "%i"); Voorbeeld:
%J	Dag van het jaar van 001 tot 366. Defred SELECT DATE_FORMAT("2011-04-15 23:03:20", "%j"); Voorbeeld:
%k	Klok met voorloopnul van 0 tot 23. Defred SELECT DATE_FORMAT("31-12-2011 01:03:20", "%k"); Voorbeeld:
%l	Klok zonder voorloopnul van 1 tot 12. Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%l"); Voorbeeld:
%M	Defred Voorbeeld:
%M	Naam van de maand zonder afkorting. Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%M"); Voorbeeld:
%M	Maand in numerieke vorm met voorloopnul (01 - 12). Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%m"); Voorbeeld:
%P	AM of PM voor 12-uursformaat. Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%p"); Voorbeeld:
%R	Tijd in 12-uursformaat. Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%r"); Voorbeeld:
%S	Seconden van 00 tot 59. Defred SELECT DATE_FORMAT("2011-04-15 00:03:20", "%s"); Voorbeeld:
%T	Tijd in 24-uursnotatie. Defred SELECT DATE_FORMAT("2011-04-15 21:03:20", "%T"); Voorbeeld:
%u	Week (00 - 52), waarbij de eerste dag van de week maandag is. Defred SELECT DATE_FORMAT("2011-04-17 21:03:20", "%u"); Voorbeeld:
%U	Week (00 - 52), waarbij de eerste dag van de week zondag is. Defred SELECT DATE_FORMAT("17-04-2011 21:03:20", "%U"); Voorbeeld:
% W	Naam van de dag van de week zonder afkorting. Defred SELECT DATE_FORMAT("2011-04-17 21:03:20", "%W"); Voorbeeld:
%w	Nummer van de dag van de week (0 - zondag, 6 - zaterdag). Defred SELECT DATE_FORMAT("2011-04-17 21:03:20", "%w"); Voorbeeld:
%J	Jaar, 4 categorieën. Defred SELECT DATE_FORMAT("2011-04-17 21:03:20", "%Y"); Voorbeeld:
%y	Jaar, 2 categorieën. Defred SELECT DATE_FORMAT("2011-04-17 21:03:20", "%y"); Voorbeeld:

STR_TO_DATE(datum, formaat) is de omgekeerde functie van de vorige, er is een datum in formaat voor nodig en retourneert een datum in MySQL-formaat.
SELECTEER STR_TO_DATE("17-04-2011 23:50", "%d.%m.%Y %H:%i");

De functie TIME_FORMAT(tijd, formaat) is vergelijkbaar met de functie DATE_FORMAT(), maar wordt alleen voor tijd gebruikt:
SELECTEER TIJD_FORMAT("22:38:15", "%H-%i-%s");

De functie GET_FORMAT(datum, formaat) retourneert een notatietekenreeks die overeenkomt met een van de vijf tijdnotaties:
EUR - Europese standaard
VS - Amerikaanse standaard
JIS - Japanse industriële standaard
ISO- ISO-standaard (internationale organisatie normen)
INTERN - internationale standaard
Deze functie is goed te gebruiken in combinatie met de vorige - DATE_FORMAT(). Laten we eens kijken naar een voorbeeld:
SELECT GET_FORMAT(DATUM, "EUR"), DATE_FORMAT("17-04-2011", GET_FORMAT(DATUM, "EUR"));

Zoals u kunt zien, retourneert de functie GET_FORMAT() zelf het presentatieformaat, en samen met de functie DATE_FORMAT() produceert deze de datum in het vereiste formaat. Maak uw eigen zoekopdrachten met alle vijf de standaarden en zie het verschil.

Nou, nu weet je bijna alles over het werken met datums en tijden in MySQL. Dit zal erg handig voor u zijn bij het ontwikkelen van verschillende webapplicaties. Als een gebruiker bijvoorbeeld een datum invoert in een formulier op een website in een voor hem bekend formaat, zal het voor u niet moeilijk zijn om de noodzakelijke functie toe te passen zodat de datum in het vereiste formaat in de database verschijnt.

Waarden in deze formaten:

Als een tekenreeks in de notatie "JJJJ-MM-DD" of "JJ-MM-DD" . Een ontspannen syntaxis is toegestaan: elk leesteken kan worden gebruikt als scheidingsteken tussen datumdelen. '31-12-2012', '31-12-2012', '2012^12^31' en '2012@12@31' zijn gelijkwaardig.

Als een string zonder scheidingstekens in de notatie "JJJJMMDD" of "JJMMDD", op voorwaarde dat de string zinvol is als datum. "20070523" en "070523" worden bijvoorbeeld geïnterpreteerd als "23-05-2007", maar "071332" is illegaal (het heeft betekenisloze maand- en dagdelen) en wordt "0000-00-00".

Als een getal in de notatie JJJJMMDD of JJMMDD, zolang het getal maar betekenis heeft als datum. 19830905 en 830905 worden bijvoorbeeld geïnterpreteerd als "05-09-1983" .

Daarom is de tekenreeks "25/08/2012" geen geldige letterlijke MySQL-datum. Je hebt vier opties (in een vage volgorde van voorkeur, nr aanvullende informatie over uw vereisten):

Configureer Datepicker om datums in een ondersteund formaat aan te bieden met behulp van altField samen met altFormat:

$("selector").datepicker(( altField: "#actualDate" altFormat: "jjjj-mm-dd" ));

Of, als u tevreden bent dat gebruikers de datum in de notatie JJJJ-MM-DD zien, stelt u eenvoudigweg de parameter dateFormat in:

$("selector").datepicker(( dateFormat: "jjjj-mm-dd" ));

$dt = \DateTime::createFromFormat("m/d/Y", $_POST["datum"]);
en dan ofwel:

Voer de tekenreeks handmatig in een geldige letterlijke waarde in:

$parts = explode("/", $_POST["datum"]); $date = "$parts-$parts-$parts";

Waarschuwing

Uw code is kwetsbaar voor SQL-injectie. U zou echt voorbereide instructies moeten gebruiken, waarbij u uw variabelen als parameters doorgeeft, die niet worden geëvalueerd naar SQL. Als je niet weet waar ik het over heb, of niet weet hoe je het kunt oplossen, lees dan het Bobby Tables-verhaal.

Het type DATE wordt gebruikt voor waarden met een datumgedeelte maar geen tijdgedeelte. MySQL haalt DATE-waarden op en geeft deze weer in het formaat "JJJJ-MM-DD". Het ondersteunde bereik loopt van "1000-01-01" tot "9999-12-31".

Het DateTime type wordt gebruikt voor waarden die zowel een datum als een tijd bevatten. MySQL haalt DateTime-waarden op en geeft deze weer in het formaat "JJJJ-MM-DD HH:MM:SS" . Het ondersteunde bereik loopt van "1000-01-01 00:00:00" tot "9999-12-31 23:59:59" .

Daarom worden alle typen kalendergegevens in detail beschreven in paragraaf “10.3. Datum- en tijdtypen »MySQL-tutorials. A belangrijke informatie met betrekking tot DBMS-ondersteuning voor tijdzones wordt beschreven in paragraaf “9.7. Ondersteuning voor MySQL Server-tijdzone". Alles wat volgt is gebaseerd op het bestuderen van de handleiding. Tegelijkertijd worden hier alleen de nuances aangegeven van het kiezen voor het ene of het andere type, dus dit materiaal vervangt de handleiding op geen enkele manier, maar vult deze aan.

In het begin korte beschrijving elk type:

TIMESTAMP - gegevenstype voor het opslaan van datum en tijd. De gegevens worden opgeslagen als het aantal seconden dat is verstreken sinds het begin van het “Unix-tijdperk”. Waardebereik: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Neemt 4 bytes in beslag.
JAAR - gegevenstype voor het opslaan van het jaar. Waardebereik: 1901 - 2155. Neemt 1 byte in beslag.
DATE is een gegevenstype voor het opslaan van datums. Waardebereik: 1000-01-01 - 9999-12-31. Neemt 3 bytes in beslag.
TIME is een gegevenstype voor het opslaan van tijd. Waardebereik: −828:59:59 - 828:59:59. Neemt 3 bytes in beslag.
DATETIME is een gegevenstype voor het opslaan van datum en tijd. Waardebereik: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Neemt 8 bytes in beslag.

Opmerking voor de gastvrouw. Het is interessant dat de meeste programmeurs geloven dat het concept van “tijdstempel” Unix-tijd is. In feite is een tijdstempel een markering die bestaat uit een reeks tekens die de datum en/of tijd aangeven waarop een bepaalde gebeurtenis heeft plaatsgevonden. En “Unix-tijd” (Unix-tijd) of POSIX-tijd is het aantal seconden dat is verstreken sinds middernacht 1 januari 1970 UTC. Het concept van tijdstempel is breder dan Unix-tijd.

Na analyse van de beschrijving van de hierboven gepresenteerde typen, kunt u bijna alle conclusies trekken over de voor- en nadelen van bepaalde typen. Alles is vrij eenvoudig en duidelijk.

Maar voordat ik over het gebruik van deze typen praat, wil ik opmerken dat in de praktijk vaak een ander type wordt gebruikt om datum en tijd op te slaan: een geheel getal (voor het opslaan van datum - INT (4 bytes), datum en tijd - BIGINT (8 bytes). bytes)). Verschil in gebruik gehele typen alleen van DATE en DATETIME, omdat de gegevens tijdens de uitvoer niet worden opgemaakt, en bij berekeningen met datums en tijden moeten gehele getallen worden geconverteerd naar het juiste kalendertype. Bovendien is er geen controle op de geldigheid van de gepresenteerde waarde voordat deze wordt opgeslagen. Sorteermogelijkheden blijven behouden. Daarom is het zinvol om INT en BIGINT in dezelfde gevallen te gebruiken als DATE en DATETIME, om de draagbaarheid en onafhankelijkheid van het DBMS te maximaliseren. Ik zie geen andere voordelen; als die er zijn, raad ik u aan deze in de opmerkingen aan te geven.

Agendagegevenstypen gebruiken in MySQL

Laten we beginnen met het eenvoudigste type JAAR. Het enige voordeel is het kleine formaat - slechts 1 byte. Maar hierdoor is er een strikte bereikbeperking aanvaardbare waarden(type kan slechts 255 opslaan verschillende betekenissen). Ik kan me moeilijk een praktische situatie voorstellen waarin je jaren strikt in het bereik van 1901 tot 2155 wilt opslaan. Bovendien geeft het SMALLINT-type (2 bytes) een bereik dat in de meeste situaties voldoende is om een jaar op te slaan. En het opslaan van 1 byte per rij in een databasetabel heeft in onze tijd geen zin.

Soorten DATUM En DATUMTIJD kunnen worden samengevoegd tot één groep. Ze slaan de datum of datum en tijd op met heel breed bereik aanvaardbare waarden, onafhankelijk van de tijdzone die op de server is ingesteld. Het gebruik ervan is zeker praktisch zinvol. Maar als u datums wilt opslaan voor historische gebeurtenissen die teruggaan tot na de gewone tijdrekening, zult u andere gegevenstypen moeten kiezen. Deze typen zijn ideaal voor het opslaan van datums voor bepaalde gebeurtenissen die mogelijk buiten het bereik van het TIMESTAMP-type vallen (verjaardagen, releasedatums van producten, presidentsverkiezingen, lanceringen van ruimteraketten, enz.). Eén ding om in gedachten te houden bij het gebruik van deze typen is belangrijke nuance, maar daarover hieronder meer.

Type TIJD kan worden gebruikt om een tijdsperiode op te slaan waarin een nauwkeurigheid van minder dan 1 seconde niet nodig is, en tijdsperioden van minder dan 829 uur. Hier valt niets meer aan toe te voegen.

Het meest interessante type blijft: TIJDSTEMPEL. Het moet worden overwogen in vergelijking met DATE en DATETIME: TIMESTAMP is ook ontworpen om de datum en/of tijd van het optreden van bepaalde gebeurtenissen op te slaan. Een belangrijk verschil daartussen zit in het bereik van waarden: TIMESTAMP is uiteraard niet geschikt voor het opslaan van historische gebeurtenissen (zelfs verjaardagen), maar is uitstekend geschikt voor het opslaan van huidige gebeurtenissen (loggen, datums van het plaatsen van artikelen, het toevoegen van producten, het plaatsen van bestellingen) en komende evenementen in de nabije toekomst (nieuwe versiereleases, kalenders en planners, enz.).

Het belangrijkste gemak van het gebruik van het type TIMESTAMP is dat u voor kolommen van dit type in tabellen een standaardwaarde kunt instellen in de vorm van vervanging van de huidige tijd, en dat u ook de huidige tijd kunt instellen bij het bijwerken van een record. Als u deze functies nodig heeft, is er een kans van 99% dat TIMESTAMP precies is wat u nodig heeft. (Zie de handleiding hoe u dit doet.)

Wees niet bang dat uw software niet meer werkt als we 2038 naderen. Ten eerste zal het gebruik van uw software vóór die tijd hoogstwaarschijnlijk eenvoudigweg worden stopgezet (vooral de versies die nu worden geschreven). Ten tweede zullen MySQL-ontwikkelaars, nu deze datum nadert, zeker met iets komen om de functionaliteit van uw software te behouden. Alles zal worden opgelost, evenals het Y2K-probleem.

We gebruiken dus het type TIMESTAMP om de datums en tijden van gebeurtenissen uit onze tijd op te slaan, en DATETIME en DATE om de datums en tijden van historische gebeurtenissen of gebeurtenissen uit de verre toekomst op te slaan.

Waardebereiken zijn belangrijk verschil tussen de typen TIMESTAMP, DATETIME en DATE, maar niet het belangrijkste. Voornaamst dat TIMESTAMP de waarde in UTC opslaat. Bij het opslaan van een waarde wordt deze vertaald van de huidige tijdzone naar UTC, en bij het lezen wordt deze vertaald van de huidige tijdzone naar UTC. DATETIME en DATE slaan altijd dezelfde tijd op en geven deze weer, ongeacht de tijdzones.

Tijdzones zijn ingesteld MySQL-DBMS mondiaal of voor huidige verbinding Deze laatste kan worden gebruikt om de werking te garanderen verschillende gebruikers in verschillende tijdzones op DBMS-niveau. Alle tijdwaarden worden fysiek opgeslagen in UTC, ontvangen van de klant en aan de klant gegeven - in de waarden van zijn tijdzone. Maar alleen als u het gegevenstype TIMESTAMP gebruikt. DATE en DATETIME ontvangen, slaan op en retourneren altijd dezelfde waarde.

De functie NOW() en zijn synoniemen retourneren de tijdwaarde in de huidige tijdzone van de gebruiker.

Gezien al deze omstandigheden moet u uiterst voorzichtig zijn bij het wijzigen van de tijdzone binnen een verbinding met de server en bij het gebruik van de typen DATE en DATETIME. Als u een datum moet opslaan (bijvoorbeeld geboortedatum), zijn er geen problemen. De geboortedatum is in elke zone hetzelfde. Die. als je geboren bent op 1 januari om 0:00 UTC/GMT+0, dan dit Niet betekent dat ze in Amerika je verjaardag op 31 december vieren. Maar als u besluit op te slaan tijd gebeurtenissen in de DATETIME-kolom, dan is het eenvoudigweg niet mogelijk om met gebruikerstijdzones op DBMS-niveau te werken. Laat me het uitleggen met een voorbeeld:

Gebruiker X werkt in de UTC/GMT+2-zone, Y - in de UTC/GMT+3-zone. Voor gebruikersverbindingen met MySQL wordt een overeenkomstige (elke heeft zijn eigen) tijdzone ingesteld. De gebruiker plaatst een bericht op het forum, wij zijn geïnteresseerd in de datum waarop het bericht is geschreven.

Optie 1: DATUMTIJD. Gebruiker X schrijft een bericht om 14:00 UTC/GMT+2. De waarde in het veld “datum” van het bericht wordt vervangen als resultaat van het uitvoeren van de functie NU() - 14:00. Gebruiker Y leest het tijdstip waarop het bericht is geschreven en ziet hetzelfde 14:00 uur. Maar zijn instellingen zijn ingesteld op UTC/GMT+3, en hij denkt dat het bericht niet alleen nu is geschreven, maar een uur geleden.

Optie 2: TIJDSTEMPEL. Gebruiker X schrijft een bericht om 14:00 UTC/GMT+2. Het veld “datum” bevat het resultaat van het uitvoeren van de functie NU() - in in dit geval- 12:00 UTC/GMT+0. UserY leest het tijdstip waarop het bericht is geschreven en ontvangt (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Alles verloopt precies zoals wij willen. En het allerbelangrijkste: het is uiterst handig in gebruik: om aangepaste tijdzones te ondersteunen, hoeft u geen tijdconversiecode te schrijven.

De mogelijkheden om de huidige tijd te vervangen en te werken met tijdzones in het TIMESTAMP-type zijn zo krachtig dat als u een datum zonder tijd in een bepaald logboek moet opslaan, u nog steeds TIMESTAMP moet gebruiken in plaats van DATE, zonder 1 byte van het verschil op te slaan tussen hen. In dit geval negeert u eenvoudigweg “00:00:00”.

Als u TIMESTAMP niet kunt gebruiken vanwege het relatief kleine bereik van de waarden (meestal 1-2 gevallen versus 10-15 in de sitedatabase), moet u DATETIME gebruiken en de waarden zorgvuldig aanpassen op de juiste plaatsen(dat wil zeggen: wanneer u naar dit veld schrijft, converteert u de datum naar UTC, en tijdens het lezen - naar de tijd in de leeszone van de gebruiker). Als u alleen de datum opslaat, maakt het waarschijnlijk niet uit welke tijdzone u heeft: Nieuwjaar Iedereen viert 1 januari volgens de lokale tijd, het is niet nodig om hier iets te vertalen.

Om te beginnen wil ik ingaan op het onderwerp welk formaat het beste is om datums in de database op te slaan: TIJDSTEMPEL of DATUMTIJD. Deze vraag is en wordt vele malen gesteld op forums, blogs, enz. Maar om je niet meteen naar zoekmachines te sturen, zal ik het proberen in eenvoudige woorden en laat het verschil zien met een voorbeeld. Type DATUMTIJD- slaat de datumwaarde op in het formaat "JJJJ-MM-DD HH:MM:SS" en is niet afhankelijk van de tijdzone. TIJDSTEMPEL- slaat een tijdstempel op, d.w.z. het aantal seconden dat is verstreken sinds 1 januari 1970. MySQL converteert deze waarden, rekening houdend met de huidige tijdzone, zowel bij het schrijven naar de database als bij het uitvoeren ervan. Wat betekent dit...
U heeft bijvoorbeeld zojuist een artikel aan de database toegevoegd, in uw agenda is het 1 januari 2014 en op uw horloge is het 01:00 uur. Als het datumveld typ DATETIME, dan ziet iedereen die de site bezoekt precies deze datum en tijd, ongeacht de woonplaats. Alles lijkt in orde te zijn, maar de gebruiker ( laten we hem "Bill G" noemen), ergens in New York wonend, is 1 januari nog niet aangebroken - voor hem is het 31 december 2013 en de klok geeft 19.00 uur aan. Hij is een beetje perplex, want... Hij is nog niet begonnen met het vieren van het nieuwe jaar, maar hij stelt zich al een “artikel uit de toekomst” voor ;) Dit zal niet gebeuren met het type TIMESTAMP, omdat Bij het uitvoeren wordt rekening gehouden met de tijdzone.
“Alles is duidelijk!”, zeg je en verander snel alle datumvelden naar het type TIMESTAMP, en Bill G zal opgelucht ademhalen, maar niet voor lang. Bij registratie op uw site heeft Bill de geboortedatum en -tijd aangegeven. Terwijl hij de wereld rondreist, kijkt hij altijd naar uw website en ontdekt met afschuw dat de tijd, en soms de geboortedatum, altijd anders is, omdat... worden weergegeven, rekening houdend met de tijdzone waarin het zich bevindt op dit moment. Ja, in dit geval speelde het type TIMESTAMP een wrede grap.
We concluderen dat u voor bepaalde taken het juiste veldtype moet selecteren of de opname/uitvoer moet regelen, afhankelijk van het gewenste resultaat.

Laten we verder gaan met populaire problemen en opties om ze op te lossen. Selecteer records binnen het opgegeven datumbereik, d.w.z. voor bepaalde periode tijd.

SELECTEER * UIT `tabelnaam` WAAR `datumveld` TUSSEN "05-07-2014" EN "15-07-2014" BESTEL OP `datumveld`;

Alle records worden geselecteerd waarbij de datums in het veld "datumveld" binnen het bereik liggen van 5 juli 2014 tot 15 juli 2014, inclusief de opgegeven datums. We mogen niet vergeten dat datums in MySQL standaard worden opgeslagen in het formaat "JJJJ-MM-DD UU:MM:SS" en dienovereenkomstig is het formaatmasker "%Y-%m-%d %H:%i: %s" (standaard ISO). Hoe kan ik het probleem oplossen als de datum niet in dit formaat wordt weergegeven? Laten we de PHP-opties negeren en kijken hoe dit in het verzoek zelf kan worden gedaan. En voor dergelijke doeleinden hebben we de functie nodig STR_TO_DATE(). Syntaxis: STR_TO_DATE(str, notatie), Waar " str" - datumtekenreeks en " formaat" is het formaat dat ermee overeenkomt. Laten we testen:

SELECTEER STR_TO_DATE("31-12-2013", "%d.%m.%Y"); /* "31-12-2013" */ SELECTEER STR_TO_DATE("31/12/13 13:50", "%d/%m/%y %H:%i"); /* "31-12-2013 13:50:00" */

Het resultaat van de uitvoering is een datum in het formaat dat standaard in MySQL wordt gebruikt. Dat wil zeggen dat we niet het formaat moeten specificeren waarin we de uitvoerdatum willen ontvangen, maar het formaat waarin we de datum voor verwerking opgeven. Met behulp van deze methode zou ons bovenstaande bericht er zelfs zo uit kunnen zien:

SELECT * VAN `tabelnaam` WAAR `datumveld` TUSSEN STR_TO_DATE("07/05/2014", "%d.%m.%Y") EN STR_TO_DATE("15 juli 2014", "%M %d,%Y ") BESTEL OP `datumveld`;

Nu we de kwestie van de datumopmaak hebben aangestipt, gaan we kijken hoe we een datum kunnen krijgen bij het nemen van steekproeven in het formaat dat we nodig hebben, omdat velen zijn veel meer gewend aan het zien van “31/12/2014” of “31 december 2014” dan “31-12-2014”. Gebruik voor dergelijke doeleinden de functie DATE_FORMAT(). Syntaxis: DATE_FORMAT(datum, notatie), Waar " datum" - datumtekenreeks en " formaat" - het formaat waarnaar moet worden geconverteerd " datum". In tegenstelling tot de functie STR_TO_DATE() geven wij zelf het gewenste uitvoerformaat aan, maar moet de datum worden opgegeven in ISO-formaat, d.w.z. "JJJJ-MM-DD UU:MM:SS". Wij controleren:

SELECT DATE_FORMAT("31-12-2014", "%d.%m.%Y"); // 31-12-2014 SELECT DATE_FORMAT("31-12-2014", "%d %M %Y"); // 31 december 2014

Als we in realtime met u zouden communiceren, zou op dit punt hoogstwaarschijnlijk de vraag onmiddellijk volgen: " Maar hoe kun je de maand toch in een andere taal weergeven: Oekraïens, Russisch of Chinees?"Het is heel eenvoudig: installeer de vereiste landinstelling. En dit kan in beide talen worden gedaan configuratiebestand MySQL (my.cnf), of eenvoudigweg een verzoek van PHP, na verbinding te hebben gemaakt met de database en vóór de belangrijkste vragen:

SET lc_time_names = ru_RU; SELECT DATE_FORMAT("31-12-2014", "%d %M %Y"); // resultaat: 31 december 2014 // indien gewenst kunt u ook “g.” of "jaar" SELECT DATE_FORMAT("31-12-2014", "%d %M %Y jaar"); // resultaat: 31 december 2014

Schoonheid! ;) En nog een paar voorbeelden van verzoeken die ook vaak nodig zijn, maar voor verwarring zorgen bij beginners.

// Selecteer records voor de huidige dag SELECT * FROM `table_name` WHERE `date_field` >= CURDATE(); // Alle records voor gisteren SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` NOW() - INTERVAL 30 DAY; // Selecteer alles voor een specifieke maand van het huidige jaar (bijvoorbeeld de maand mei) SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5 ; // of voor de maand mei, maar in 2009 SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;

Ik zie het nut niet in van het in detail beschrijven van de MySQL-functies die in de voorbeelden worden gebruikt, omdat... ze zijn intuïtief en voor iemand met op zijn minst een beetje kennis van het Engels zal het niet moeilijk zijn om te begrijpen dat bijvoorbeeld de functie MAAND() geeft de maand van de datum terug, JAAR()- haar jaar, en DAG() (of synoniem DAYOFMONTH()) - dag. Trefwoord INTERVAL- dient voor rekenkundige bewerkingen op datums en hun wijzigingen.

SELECTEER "2014-07-07 23:59:59" + INTERVAL 1 SECONDE; // resultaat: 08-07-2014 00:00:00 SELECTEER "07-07-2014 23:59:59" + INTERVAL 1 DAG; // resultaat: 08-07-2014 23:59:59 // hetzelfde. maar met behulp van de DATE_ADD() functie SELECT DATE_ADD("2014-07-07 23:59:59", INTERVAL 1 DAG); // 08-07-2014 23:59:59 // Als u wilt aftrekken in plaats van optellen, SELECT DATE_SUB("07-07-2014 23:59:59", INTERVAL 1 DAG); // 06-07-2014 23:59:59 // of gewoon zomaar SELECT "07-07-2014 23:59:59" - INTERVAL 1 DAG; // 06-07-2014 23:59:59

Dit zijn niet alle functies voor het werken met datums, en ik raad u aan ze voor informatieve doeleinden op de officiële website door te nemen, zodat u weet of ze bestaan als u problemen ondervindt. ongebruikelijke situatie. Maar ik wil hopen dat zelfs dit korte recensie MySQL-functies voor het werken met datums in dit artikel helpt u bij het navigeren door de situatie en het nemen van de juiste beslissing. Als er zich nog steeds problemen voordoen, stel dan vragen in dit onderwerp of in het gedeelte 'Uw vraag'. Wij komen er samen wel uit;)