MySQL-datumnotatie en tijdstempelfuncties met voorbeelden

mysql date format timestamp functions with examples

Deze uitgebreide tutorial biedt syntaxis en voorbeelden van MySQL-datumnotatie en tijdstempelfuncties zoals huidige datum, datum toevoegen, tijd toevoegen, enz.:

In deze tutorial zullen we diepgaand leren over de MySQL Date and Time-gegevenstypen en de verschillende functies die MySQL biedt voor het manipuleren van datum- en tijdwaarden.

MySQL biedt een groot aantal functies / hulpprogramma's om verschillende bewerkingen uit te voeren, zoals het optellen en aftrekken van datums, het vinden van het verschil tussen 2 datums en het extraheren van specifieke delen van een gegeven datum-tijdwaarde.

Wat je leert:

• MySQL datumnotatie en tijdfuncties
  • Vereisten
  • DATUM EN TIJD Gegevenstypen
  • MySQL datum- en tijdfuncties
    • # 1) Huidige datum en tijd ophalen
    • # 2) Datums optellen en aftrekken
    • # 3) Datums converteren
    • # 4) Specifieke delen van DateTime-kolommen ophalen
    • # 5) Verschil vinden tussen 2 datums
    • # 6) Datums opmaken
  • Veel Gestelde Vragen
• Gevolgtrekking
  • Aanbevolen literatuur

MySQL datumnotatie en tijdfuncties

Deze functies zijn erg belangrijk wanneer er bewerkingen op datum-tijdkolomwaarden vereist zijn in MySQL-tabellen.

Vereisten

Voor het uitvoeren van de voorbeelden die in de zelfstudie worden gebruikt, heeft u een lokale installatie van MySQL-installatie nodig.

= >> Raadpleeg onze tutorial voor het installeren van MySQL hier.

DATUM EN TIJD Gegevenstypen

Gegevenstypen zijn onder meer YEAR, DATE, TIME, DATETIME en TIMESTAMP.

MySQL slaat datum- en tijdwaarden op met behulp van 5 verschillende gegevenstypen, d.w.z. - YEAR, DATE, TIME, DATETIME en TIMESTAMP. Laten we proberen deze kort te begrijpen in de onderstaande tabel, voordat we dieper ingaan op DATUMFORMATEN en DATETIME FUNCTIES geleverd door MySQL.

Nu zullen we de geheugenvereisten voor fractionele opslag begrijpen. Voor de gegevenstypen TIME, DATETIME en TIMESTAMP zou, afhankelijk van de gespecificeerde fractionele component, het gebruikte geheugen verschillen.

Gebruik de bovenstaande tabel, bijvoorbeeld, als we een kolomtype hebben als - TIME (5).

Dan zou het totale gebruikte geheugen zijn => geheugen gebruikt door TIME-gegevenstype + geheugen gebruikt door 5 fractionele componenten => 3 + 3 => 6 bytes

Laten we al deze gegevenstypen illustreren als onderdeel van een enkele tabel en de waarden bekijken die aan elk van de typen zijn toegewezen.

Voeg een enkele rij in de tabel in met behulp van de ‘now ()’ -functie, die het huidige tijdstempel is. Merk op hoe het gebruik van deze functie zou resulteren in het bijsnijden / invoegen van alleen het vereiste deel van de huidige datum en tijd, afhankelijk van het gegevenstype van de kolom.

Laten we nu eens kijken naar de inhoud van de tabel om de gegevens in verschillende kolommen te analyseren.

Dus vatten we de bovenstaande gegevens samen als,

• Alle kolommen kregen toegewezen waarden volgens de gegevenstypen, bijvoorbeeld, voor de eerste kolom - YEAR - hoewel we hebben ingevoegd met de functie ‘now ()’ die de huidige tijdstempel is, is de waarde die is ingevoegd alleen het jaargedeelte van de huidige datum en tijd - d.w.z. 2020.
• now () functie retourneert de huidige datetime in het formaat - dd: mm: jjjj uu: mm: ss
• Merk op dat het fractionele gedeelte in de kolomnamen ‘_fractional’ heeft. Afhankelijk van het opgegeven aantal breukcijfers, worden de breukwaarden vastgelegd en weergegeven.

Automatische initialisatie en update van DATETIME- en TIMESTAMP-velden

In de praktijkscenario's - meestal zijn de DATETIME-initialisatie en updates geconfigureerd om automatisch te zijn.

Dit betekent dat tabellen over het algemeen 2 kolommen hebben, zoals - created_time en updated_time ​

De kolom created_time is geconfigureerd om de waarde van de huidige systeemtijd op te nemen wanneer de rij wordt ingevoegd, en op dezelfde manier wordt updated_time geconfigureerd om de huidige systeemtijd te nemen wanneer er updates zijn uitgevoerd aan een bepaalde rij van de tabel.

Houd er rekening mee dat, tijdens het invoegen van de rij zouden zowel created_time als updated_time dezelfde waarde hebben. Alle volgende updates van de rij zouden resulteren in het bijwerken van het veld updated_time met de huidige systeemtijd.

Laten we eens proberen een dergelijke tabel te maken die zowel automatische initialisatie als updates heeft.

We zullen een tabel maken met de naam aandelenprijs die de velden aandelensymbool, prijs en datum / tijd heeft - created_on en updated_on.

Voeg enkele gegevens in de tabel in:

Laten we eens kijken naar het resultaat (merk op dat we zojuist waarden hebben ingevoerd voor symbool en prijs - de datetime-kolommen, created_on en updated_on zouden automatisch moeten worden geïnitialiseerd)

pl sql interviewvragen met antwoorden

Probeer de aandelenkoers voor de 'AMZN'-aandelen bij te werken zoals hieronder weergegeven:

En haal nu de gegevens opnieuw voor de tabel op. We zouden de waarde van de updated_time moeten zien veranderen in het tijdstip waarop de update naar de stock_price plaatsvond.

U kunt nu aan het bovenstaande voorbeeld zien dat de waarde van de kolom updated_on is bijgewerkt naar het tijdstempel van de update.

MySQL datum- en tijdfuncties

MySQL biedt talrijke functies voor kolommen met datum- en tijdgerelateerde gegevenstypen.

Deze functies worden over het algemeen gebruikt om,

• Converteer een opgeslagen datum naar een vereist formaat terwijl u de gegevens opvraagt.
• Het manipuleren van bestaande datum-tijdvelden, zoals het toevoegen van een interval aan de huidige waarde van de kolom, uren aftrekken van de huidige ingestelde waarden, etc.
• Specifieke informatie extraheren uit de opgeslagen waarden, bijvoorbeeld, het extraheren van alleen het 'datum' -gedeelte uit de opgeslagen waarde en het uitvoeren van verdere filtering / bewerkingen zoals GROEP OP met de datum, etc.

In deze sectie zullen we voorbeelden zien van de meest gebruikte datum- en tijdfuncties.

>> Verwijs hier voor een volledige lijst van dergelijke functies.

# 1) Huidige datum en tijd ophalen

MySQL biedt veel handige functies / constante waarden om de huidige waarden van datum en tijd op te halen en worden over het algemeen gebruikt bij het opslaan van waarden in de datetime-kolommen of het ophalen van een bepaald bereik uit de huidige datum- en tijdwaarden.

a) curdate ()

Deze functie haalt de huidige datum op. Andere synoniemen van curdate () functie zijn - CURRENT_DATE, CURRENT_DATE (), CURRENT_DATE ( fractionele cijfers ​

Een voorbeeld van deze functie wordt hieronder getoond:

b) curtime ()

Deze functie haalt de huidige tijd op. Om een ​​fractioneel deel (tot 6 cijfers) te verkrijgen, specificeert u de breuk tussen haakjes als curtime (3). Dit zou de huidige waarde van de systeemtijd ophalen tot 3 decimalen.

Andere synoniemen van de functie curtime () zijn- CURRENT_TIME, CURRENT_TIME (), CURRENT_TIME ( fractionele cijfers ​

c) nu ()

Deze functie haalt de huidige tijdstempel op. Specificeer het breukgedeelte tussen de haakjes, bijvoorbeeld, now (4) zou het huidige tijdstempel ophalen tot 4 decimalen.

Andere synoniemen van de functie now () zijn onder meer CURRENT_TIMESTAMP, CURRENT_TIMESTAMP (), CURRENT_TIMESTAMP ( fractionele cijfers ​

# 2) Datums optellen en aftrekken

MySQL biedt functies om datum- en tijdwaarden toe te voegen aan en af ​​te trekken van een bestaande waarde die is opgeslagen in de kolom of die kan worden gebruikt tijdens het opvragen van gegevens. We zullen de functies begrijpen met behulp van voorbeelden.

We zullen de onderstaande tabel gebruiken voor het optellen en aftrekken van datum en tijd.

Voeg enkele vermeldingen in de bovenstaande tabel in met behulp van de INSERT-instructies zoals hieronder weergegeven:

Laten we de bovenstaande tabel doorzoeken en de opgeslagen gegevens bekijken:

Naast het bijwerken van kolommen in een tabel, kunnen we direct een datetime-manipulatie uitvoeren met behulp van de SELECT-instructie door deze toe te passen op een specifieke datum / tijd of datetime-waarde.

a) ADDDATE ()

Zoals de naam suggereert, wordt deze functie gebruikt om een ​​datum toe te voegen aan een bestaande datumwaarde die van het type DATE, DATETIME of TIMESTAMP kan zijn

Syntaxis:

ADDDATE kan ook worden aangeroepen met een intervalwaarde - dit kan DAGEN, WEEK, UUR, MINUUT, TWEEDE enz. Zijn.

De syntaxis zou in dat geval veranderen in,

Laten we een paar voorbeelden bekijken om dit beter te begrijpen.

Als een integerwaarde wordt opgegeven als het tweede argument, wordt deze behandeld als het aantal dagen dat bij de opgegeven datum moet worden opgeteld.

Nog enkele voorbeelden met Interval als - MAAND, UUR en WEEK zijn:

Andere voorbeelden van intervallen kunnen zijn: MINUUT, TWEEDE, KWARTAAL, etc.

Voor het bijwerken van een kolom van de bestaande tabel, bijvoorbeeld, voor de tabel hebben we ‘datetime_calc’ gemaakt als we de ‘datecol’ -waarde willen bijwerken en 10 dagen willen toevoegen aan alle datums in de kolom, we zouden de vraag kunnen gebruiken zoals hieronder:

Gebruik nu SELECT * om alle gegevens in de tabel datetime_calc weer te geven.

We kunnen in de bovenstaande uitvoer zien dat de waarden van de datecol-kolom zijn bijgewerkt naar 24-09-2020 vanaf 25-08-2020 na toevoeging van 30 dagen aan de bestaande waarden.

b) ADDTIME ()

Net als bij ADD_DATE kan de functie ADD_TIME worden gebruikt om een ​​tijdcomponent toe te voegen aan een bestaande - DATETIME, TIME of TIMESTAMP kolom / veld of een bepaalde invoer.

Syntaxis:

Voorbeeld:

Laten we eens kijken naar een voorbeeld waarin we een datetime-kolom in een tabel updaten door 5 uur toe te voegen aan alle waarden in die kolom. Hier is een voorbeeldzoekopdracht:

Tabeluitvoer voor:

Tabeluitvoer na:

c) ONDERDATUM ()

SUBDATE lijkt precies op ADDDATE, met als enige verschil: SUBDATE trekt het opgegeven interval / nr af. dagen vanaf de kolom- / veldwaarde.
Syntaxis:

Laten we een paar voorbeelden bekijken om de SUBDATE () -functie te illustreren.

Het onderstaande voorbeeld toont het aftrekken van een interval vermeld in uren / dagen week, enz.

d) SUBTIME ()

SUBTIME trekt de tijdwaarde die is opgegeven in ‘uu: mm: ss’ af van een gegeven datetime- of tijdveldwaarde.

Syntaxis:

Voorbeelden van de functie SUBTIME ():

# 3) Datums converteren

MySQL biedt een verscheidenheid aan ingebouwde functies, van het converteren van datums van het ene formaat naar het andere. Laten we eens kijken naar de meest gebruikte functies voor het converteren van datums.

a) CONVERT_TZ ()

Deze functie wordt gebruikt om de datum van de ene tijdzone naar de andere te converteren. Deze zijn handig in situaties waarin u veronderstelt dat uw gegevens zijn opgeslagen met de UTC-tijdzone en terwijl u wordt weergegeven, wilt u deze converteren naar een tijdzone van uw keuze.

Bijvoorbeeld, converteer UTC naar MET (Midden-Europese tijd).

Tips / opmerkingen

# 1) Om de lijst met alle tijdzones in de MySQL-systeemdatabase te krijgen, kunt u de onderstaande zoekopdracht uitvoeren en een van de tijdzoneenamen gebruiken om van de ene tijdzone naar de andere te converteren.

#twee) De tijdzonegegevens worden ingevuld tijdens de installatie van de MySQL-serverinstantie. Als het resultaat van de bovenstaande query NULL is, moet u mogelijk eerst tijdzonewaarden invullen in de MySQL-systeemtabellen.

>> Volg deze koppeling voor meer hulp hierbij.

b) FROM_UNIXTIME ()

Deze functie wordt gebruikt om een ​​gegeven UNIX TIMESTAMP naar MySQL datetime-formaat te converteren.

c) UNIX_TIMESTAMP

Deze functie wordt gebruikt om een ​​bepaalde MySQL-datetime naar UNIX-tijdstempel te converteren.

UNIX-tijdstempel is de weergave van een bepaalde datum in de vorm van seconden die zijn verstreken sinds 1 januari 1970, UTC.

>> Verwijs hier voor meer informatie over UNIX-tijdstempel.

# 4) Specifieke delen van DateTime-kolommen ophalen

Soms is het gewenst om een ​​specifiek deel van de datumwaarde op te halen voor een datetime-veldwaarde in de tabel. Bijvoorbeeld , stel dat je het aantal wilt tellen. van bestellingen en groepeer ze op dag, week, maand, enz.

En over het algemeen worden deze waarden niet als afzonderlijke kolommen in de tabel opgeslagen - bijvoorbeeld, je zou slechts één kolom hebben zoals - created_on, wat een datetime-veld zou zijn en geen aparte kolommen zoals - datum, maand, jaar, etc.

MySQL biedt veel handige methoden waarmee u het gewenste onderdeel uit het DateTime-veld kunt extraheren.

Stel dat we een datetime-waarde hebben als - ‘2020-03-15 07: 10: 56.123’

Laten we eens kijken naar enkele van de veelgebruikte functies met voorbeelden van de bovenstaande datetime-waarde.

a) Extract DAG

MySQL biedt de volgende functies om DAG-informatie te extraheren:

DATUM() ​ Pak het DATE () -gedeelte van het DateTime-veld uit.

beste mp3-downloader voor Android-telefoon

DAG VAN DE MAAND() ​ Extraheer het DAG-gedeelte van datetime. Het is in wezen de dag van de maand. De afkorting voor het gebruik van deze functie is DAG ()

DAG VAN DE WEEK () - Extraheert de index die overeenkomt met de dag van de week - waarden tussen 1 en 7.

DAYOFYEAR () - Haalt de dag uit in termen van nr. dagen verstreken in een jaar. De waarden liggen tussen 1 en 365 (of 366 als het een schrikkeljaar is).

DAGNAAM () - Extraheert de naam van de dag met waarden die variëren van maandag tot en met zondag.

b) Extraheer TIJD

MySQL biedt de volgende functies om TIME-waarden te extraheren:

TIJD () - Extraheert het tijdgedeelte in de indeling uu: mm: ss (met de component fractionele seconden indien beschikbaar).

UUR () - Extraheert het uurgedeelte van het veld / de waarde - waarden tussen 1-24.

MINUUT () - Extraheert het minuutgedeelte van de opgegeven datetime - waarden tussen 1-60.

TWEEDE() - Extraheert het secondengedeelte van de opgegeven datetime - waarden tussen 1-60.

MICROSECOND () - Extraheert het microsecondengedeelte van de opgegeven DateTime.

c) Extract MAAND

MAAND () - Extraheert de maandindex. Waarden variërend van 1 - 12.

MAANDNAAM () - Extraheert de naam van de maand. Waarden variërend van januari tot december.

d) Extract WEEK, JAAR en KWARTAAL

WEEK() - Extraheert de WEEK van het jaar voor de opgegeven DateTime-waarde.

JAAR () - Extraheert het JAAR-deel van de datum.

KWARTAAL () - Extraheert het kwartaal ten opzichte van het jaar. Waarden tussen 1 en 4.

Al deze bovenstaande methoden worden meestal gebruikt voor data-analyse en het doen van voorspellingen - bijvoorbeeld, welke dagen van de maand waren de nee. van de hoogste orders, etc.

# 5) Verschil vinden tussen 2 datums

Om het verschil te vinden tussen 2 gegeven datetime-veldwaarden, biedt MySQL DATEDIFF () & TIMEDIFF () -functies.

a) DATEDIFF ()

Retourneert het verschil tussen 2 DateTime (of datum) waarden in aantal dagen.

Syntaxis:

Laten we enkele voorbeelden bekijken voor de DATEDIFF () -functie:

DATEDIFF () gebruikt DateTime-invoer.

DATEDIFF () met behulp van datuminvoer.

DATEDIFF () met behulp van datuminvoer met negatieve uitvoer.

b) TIMEDIFF ()

Deze functie retourneert het verschil tussen 2 datetime (of tijd) waarden uitgedrukt in ‘hh: mm: ss’ met een optioneel fractioneel deel, afhankelijk van de invoer waarmee de functie TIMEDIFF () werd aangeroepen.

Syntaxis:

Volgens de bovenstaande syntaxis zou het resultaat van de functie TIMEDIFF () de tijd zijn die is gespecificeerd als ‘hh: mm: ss’ voor expression => Datum Tijd 1 ​ Datum Tijd 2

Houd er rekening mee dat,

• Zowel Expression 1 als Expression2 zijn van hetzelfde type - d.w.z. een datetime, een andere just time, zou een NULL-waarde retourneren.
• Expression1 en Expression2 kunnen niet zomaar datums zijn. Voorbeeld: DATEDIFF (2020-01-01, 2020-02-01) zou NULL retourneren.

Laten we enkele voorbeelden bekijken voor de functie TIMEDIFF ():

TIMEDIFF () met behulp van DateTime-invoer.

TIMEDIFF () met behulp van tijdinvoer.

TIMEDIFF () retourneert een negatieve uitvoer.

Houd er rekening mee dat zowel DATEDIFF () als TIMEDIFF () kunnen NEGATIEVE waarden retourneren wanneer het eerste argument kleiner is dan het tweede - dat wil zeggen dat het eerste argument een oudere dateTime-waarde is in vergelijking met het tweede.

# 6) Datums opmaken

DATE_FORMAT () functie converteert de datum in het gegeven formaat.

Bijvoorbeeld: Het omzetten van de vereiste datum naar mm / jjjj.

Laten we eens kijken naar enkele algemene conventies voor het gebruik van indelingsspecificaties:

Afhankelijk van de vereiste kunnen deze formaatspecificaties worden gebruikt en kan MySQL DateTime worden geconverteerd naar het vereiste formaat.

Voorbeeld: Omzetten naar dd / mm / jjjj-formaat kan worden gespecificeerd als:

>> Verwijs hier voor een volledige lijst met formaatspecificaties die worden ondersteund door MySQL.

Veel Gestelde Vragen

V # 1) Hoe kunnen we het jaargedeelte van de opgeslagen datum in MySQL wijzigen?

Antwoord: Om de jaarwaarde bij te werken naar een bepaald getal voor een bepaalde datum, kunnen we gebruik maken van de DATUM_ADD functie en voeg het verschil toe tussen het huidige jaar van de datum en het jaar dat we willen wijzigen.

Bijvoorbeeld: Stel dat we de datum ‘2020-03-15 07: 10: 56.123’ hebben en we willen alleen het jaargedeelte van de datum wijzigen in 2018. Om dat te doen,

• We kunnen eerst het verschil vinden tussen het jaar dat moet worden bijgewerkt en het werkelijke jaar van de datum. Dit kan worden bereikt met de functie JAAR.

• Nu kunnen we deze -2 als jaarinterval toevoegen met behulp van de DATE_ADD-functie.

U kunt hierboven zien dat we zojuist het jaartal van de datum hebben gewijzigd.

Dezelfde reeks stappen kan worden gebruikt wanneer het nodig is om een ​​specifiek deel van de datum te wijzigen. Voorbeeld: Maand, dag, etc.

Q # 2) Hoe verander ik datetime in het vereiste formaat in MySQL?

hoe swf-bestand op chrome te openen

Antwoord: MySQL biedt de functie DATE_FORMAT () om datetime om te zetten in de vereiste opgemaakte tekenreeks voor weergave in queryresultaten.

>> Verwijs hier voor een volledige lijst met formaatspecificaties.

Voorbeeld: Voor het converteren van datetime naar formaat - dd: mm: jjjj, kunt u de volgende zoekopdracht gebruiken:

V # 3) Wat is het datumnotatie in MySQL?

Antwoord: De standaard datumnotatie in MySQL is ‘dd-mm-jjjj uu: mm: ss. { fractionalPart ​

Voorbeeld datetime-waarde opgeslagen in MySQL - ‘2020-03-15 07: 10: 56.123’

V # 4) Hoe maak je een tabel aan met het datatype DATE in MySQL.

Antwoord: MySQL biedt verschillende gegevenstypen voor het opslaan van datum- en tijdwaarden naar behoefte. Alleen voor het opslaan van datums - DATE-gegevenstype kan worden gebruikt. Het slaat gegevens op in het formaat jjjj-mm-dd.
Laten we een voorbeeld bekijken van het specificeren van een kolom als DATE-type:

Gevolgtrekking

In deze tutorial hebben we geleerd over de verschillende gegevenstypen die door MySQL worden geleverd voor het opslaan van datum- en tijdwaarden en hoe verschillende gebruiksvriendelijke functies van MySQL kunnen worden gebruikt om berekeningen uit te voeren op datum-tijdwaarden.

We leerden ook over verschillende functies, zoals het extraheren van specifieke delen van de gegeven DateTime-waarde, het omzetten van een bepaalde datum in een andere vorm, het toevoegen van datum- / tijdwaarden aan bestaande waarden, enz.

DateTime is een van de belangrijkste gegevenstypen in MySQL en is van groot belang voor het ophalen van details rond wanneer een gebeurtenis plaatsvond.

Deze waarden worden op grote schaal gebruikt door data-analisten / machine learning / prijsmodellen enz. Waar veel data wordt gesneden en in blokjes gesneden om machine learning-modellen voor te bereiden en cruciale zakelijke beslissingen te nemen.

Aanbevolen literatuur

• C # DateTime-zelfstudie: werken met datum en tijd in C # met voorbeeld
• PL SQL Datetime-formaat: datum- en tijdfuncties in PL / SQL
• Python DateTime-zelfstudie met voorbeelden
• Datum- en tijdfuncties in C ++ met voorbeelden
• MySQL Create Bekijk zelfstudie met codevoorbeelden
• MySQL invoegen in tabel - Verklaring syntaxis en voorbeelden invoegen
• MySQL Create Table Tutorial met voorbeelden
• MySQL Delete-instructie - Verwijder opdrachtsyntaxis en voorbeelden