Com treballar eficaçment amb dates i hores a MySQL

Com treballar eficaçment amb dates i hores a MySQL

Les dates i els horaris són importants, ajuden a mantenir les coses organitzades i són un aspecte integral de qualsevol operació de programari.





Treballar eficaçment amb ells a la base de dades de vegades pot semblar confús, tant si es treballa a través de les diferents zones horàries, com s’afegeixen / resten dates i altres operacions.



Apreneu les diverses funcions de MySQL disponibles per gestionar i gestionar fàcilment les dates / hores de la vostra base de dades.





Treballar amb zones horàries

Per ajudar a mantenir les coses estandarditzades, només heu de treballar sempre amb dates / hores a la zona horària UTC. Cada vegada que estableix una connexió a la base de dades MySQL, haureu de canviar la zona horària a UTC, que es pot fer amb la següent sentència SQL:

SET TIME_ZONE = '+0:00'

Com que totes les dates es desaran a UTC, sempre se sap amb què està treballant, cosa que fa que les coses siguin més senzilles i senzilles.



Android 7.0 mou aplicacions a la targeta SD

Quan sigui necessari, podeu fer-ho fàcilment convertir la zona horària de qualsevol valor de data / timestamp amb el manual CONVERT_TZ () Funció MySQL. Primer heu de conèixer el desplaçament, per exemple, PST a la costa oest d’Amèrica del Nord és UTC -08: 00, de manera que podeu utilitzar:

SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Això es tradueix en 2021.02.04 13:47:23 que és exactament correcte. Els tres arguments passats a CONVERT_TZ () són primer la marca de temps / hora amb què comenceu (utilitzeu ara () per a l’hora actual), la segona sempre serà '+ 0: 00' ja que totes les dates estan obligades a UTC a la base de dades, i l'últim és el desplaçament al qual volem convertir la data.



Afegeix / resta dates

Moltes vegades cal afegir o restar de dates, com ara si heu de recuperar registres de fa una setmana o programar alguna cosa al mes a partir d’ara.

Afortunadament MySQL té l’excel·lent DATE_ADD () i DATE_SUB () funcions que fan que aquesta tasca sigui extremadament fàcil. Per exemple, podeu restar dues setmanes de la data actual amb la sentència SQL:



SELECT DATE_SUB(now(), interval 2 week);

Si voleu afegir tres dies a una marca de temps existent, utilitzeu:

SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Ambdues funcions funcionen igual, el primer argument és la marca de temps que comenceu i el segon argument és l'interval per sumar o restar. El segon argument sempre té el mateix format, començant per la paraula interval seguit d'un valor numèric i de l'interval en si, que pot ser qualsevol dels següents: segon, minut, hora, dia, setmana, mes, trimestre, any.

Per a un altre exemple, si voleu recuperar tots els inicis de sessió que es van produir en els darrers 34 minuts, podeu utilitzar una sentència SQL com:

SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Com podeu veure, això recuperaria tots els registres del fitxer inicis de sessió taula amb una data d'inici de sessió superior al temps actual menys 45 minuts, o dit d'una altra manera, als darrers 45 minuts.

Obteniu la diferència entre les dates

De vegades, cal obtenir el temps transcorregut entre dues dates. Podeu obtenir fàcilment el nombre de dies entre dues dates diferents amb DAT DE DIFERÈNCIA , com ara la sentència SQL següent:

SELECT DATEDIFF(now(), '2020-12-15');

El DAT DE DIFERÈNCIA La funció adopta dos arguments, els dos són segells de data i hora i dóna el nombre de dies entre ells. L'exemple anterior mostrarà el nombre de dies transcorreguts des del 15 de desembre de 2020 fins avui.

Per obtenir el nombre de segons entre dues dates, el fitxer TO_SECONDS () la funció pot ser útil, per exemple:

SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Això donarà lloc al nombre de segons entre les dues dates proporcionades.

Extreu segments de dates

Hi ha diverses funcions de MySQL que us permeten extreure fàcilment segments específics de dates, com ara si només volíeu el mes, el dia de l'any o l'hora. A continuació, es detallen alguns exemples d’aquestes funcions:

SELECT MONTH('2021-02-11 15:27:52'); SELECT HOUR(now()); SELECT DAYOFYEAR('2021-07-15 12:00:00');

Les instruccions SQL anteriors resultarien 02 , l'hora actual i 196 ja que el 15 de setembre és el 196è dia de l'any. A continuació, es mostra una llista de totes les funcions d’extracció de dates disponibles, cadascuna amb només un argument, de la qual s’extreu la data:

- SECOND()
- MINUTE()
- HOUR()
- DAY()
- WEEK() - Number 0 - 52 defining the week within the year.
- MONTH()
- QUARTER() - Number 1 - 4 defining the quarter of the year.
- YEAR()
- DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
- LAST_DAY() - The last day in the given month.
- DATE() - The date in YYYY-MM-DD format without the time.
- TIME() The time in HH:II:SS format without the date.
- TO_DAYS() - The number of days since A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 0.
- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Per exemple, si potser només voleu recuperar el mes i l'any en què es van crear tots els usuaris, podeu utilitzar una sentència SQL com:

SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Això recuperaria tots els registres del fitxer usuaris taula i mostreu l’identificador #, mes i any en què es va crear cada usuari.

Agrupació de registres per període de dates

Un ús excel·lent de les funcions de data és la possibilitat d'agrupar registres per període de data utilitzant AGRUPAR PER dins de les vostres sentències SQL. Per exemple, potser voleu obtenir l'import total de totes les comandes el 2020 agrupades per mesos. Podeu utilitzar una sentència SQL com:

SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

D’aquesta manera, es recuperarien totes les comandes realitzades l’any 2020, s’agruparien pel mes en què es van crear i es retornarien 12 registres que mostren l’import total ordenat cada mes de l’any.

Tingueu en compte que, per obtenir un millor rendiment de l’índex, sempre és millor evitar utilitzar funcions de data com ara CURS() dins de la clàusula WHERE de les sentències SQL i, en canvi, utilitzeu el fitxer ENTRE com es mostra a l'exemple anterior.

No us torneu a confondre amb les dates

Utilitzant els coneixements anteriors, ara podeu treballar, traduir i realitzar operacions de dates i hores de manera eficient en una àmplia gamma de casos d’ús.

Recordeu que sempre heu d’utilitzar UTC quan treballeu amb dates per simplificar-los i utilitzeu els consells anteriors per gestionar de manera eficient les dates del vostre programari, ja sigui per completar càlculs senzills o per obtenir informes fàcilment agrupats per períodes de dates.

Si sou una mica nou a SQL, assegureu-vos de consultar-los ordres SQL essencials per ajudar a millorar el vostre ús de SQL.

Compartir Compartir Tweet Correu electrònic Com crear informes i documents professionals a Microsoft Word

Aquesta guia examina els elements d'un informe professional i revisa l'estructuració, l'estil i la finalització del vostre document a Microsoft Word.

Llegiu a continuació Temes relacionats Sobre l'autor Matt Dizak(18 articles publicats) Més de Matt Dizak

Subscriu-te al nostre butlletí

Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.

Feu clic aquí per subscriure-us