Hogar › Teléfono › Conversión de fecha en mysql usando DATE_FORMAT(). Tipos de datos de calendario en MySQL: características de uso

Conversión de fecha en mysql usando DATE_FORMAT(). Tipos de datos de calendario en MySQL: características de uso

Valores en estos formatos:

Como una cadena en el formato "AAAA-MM-DD" o "AA-MM-DD". Se permite una sintaxis relajada: cualquier carácter de puntuación se puede utilizar como separador entre las partes de la fecha. Por ejemplo, "2012-12-31", "2012/12/31", "2012^12^31" y "2012@12@31" son equivalentes.

Como una cadena sin delimitadores en el formato "AAAAMMDD" o "AAMMDD" siempre que la cadena tenga sentido como fecha. Por ejemplo, "20070523" y "070523" se interpretan como "2007-05-23", pero "071332" es ilegal (tiene partes de mes y día sin sentido) y se convierte en "0000-00-00".

Como un número en el formato AAAAMMDD o AAAAMMDD, siempre que el número tenga sentido como fecha. Por ejemplo, 19830905 y 830905 se interpretan como "1983-09-05".

Por lo tanto, la cadena "25/08/2012" no es un literal de fecha MySQL válido. Tiene cuatro opciones (en un vago orden de preferencia, sin más información sobre sus requisitos):

Configure Datepicker para proporcionar fechas en un formato compatible usando altField junto con altFormat:

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

O, si está satisfecho de que los usuarios vean la fecha en formato AAAA-MM-DD, simplemente configure el parámetro dateFormat:

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

$dt = \DateTime::createFromFormat("m/d/Y", $_POST["fecha"]);
y luego:

Ingrese manualmente la cadena en un literal válido:

$partes = explotar("/", $_POST["fecha"]); $fecha = "$piezas-$piezas-$piezas";

Advertencia

Su código es vulnerable a la inyección SQL. Realmente deberías usar declaraciones preparadas, donde pasas tus variables como parámetros, que no se evalúan en SQL. Si no sabes de qué estoy hablando o cómo solucionarlo, lee la historia de Bobby Tables.

El tipo FECHA se utiliza para valores con una parte de fecha pero sin parte de hora. MySQL recupera y muestra los valores de FECHA en el formato "AAAA-MM-DD". El rango admitido es de "1000-01-01" a "9999-12-31".

El tipo DateTime se utiliza para valores que contienen tanto una fecha como una hora. MySQL recupera y muestra los valores DateTime en el formato "AAAA-MM-DD HH:MM:SS". El rango admitido es de "1000-01-01 00:00:00" a "9999-12-31 23:59:59".

La publicación de este artículo está permitida únicamente con un enlace al sitio web del autor del artículo.

Como sabe, todas las fechas se almacenan en MySQL en orden inverso año-mes-día (2008-10-18), a veces incluso sin separador (20081018).
Para mostrar la fecha, debe convertirla a un formato legible normal.

Hay dos métodos de conversión, efectivos y no muy efectivos.
Una forma ineficaz es cuando la salida de fecha de mysql se convierte usando php.
Yo personalmente hice esto durante mucho tiempo. Antes de mostrar, invertí cada fecha usando una función de PHP.
Si la cantidad de transformaciones no es grande, entonces puede revertir la fecha usando PHP, no hay nada de malo en eso, pero si necesita extraer decenas o cientos de miles de registros y convertir la fecha en cada uno, entonces, por supuesto, convertir Las fechas usando MySQL serán mucho más rápidas.

Mysql tiene una excelente función DATE_FORMAT(), es muy similar a la función php date().
Aquí tienes un ejemplo de uso.

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

Todo es muy sencillo y rápido, no es necesario cambiar fechas usando php.
Aquí hay una lista de definiciones para esta función.

Determinante	Descripción
%METRO	Nombre del mes (enero...diciembre)
%W	Nombre del día de la semana (domingo...sábado)
%D	Día del mes con sufijo en inglés (0.º, 1.º, 2.º, 3.º, etc.)
%Y	Año, fecha, 4 dígitos
%año	Año, fecha, 2 dígitos
%INCÓGNITA	Año para una semana donde el domingo se considera el primer día de la semana, número de 4 dígitos, usado con "%V"
%incógnita	Año para una semana donde el domingo se considera el primer día de la semana, número de 4 dígitos, usado con "%v"
%a	Nombre abreviado del día de la semana (domingo...sábado)
%d	Día del mes, número (00..31)
%mi	Día del mes, número (0..31)
%metro	Mes, día (00..12)
%do	Mes, fecha (0..12)
%b	Nombre abreviado del mes (enero...diciembre)
%j	Día del año (001..366)
%H	Hora (00..23)
% k	Hora (0..23)
%h	Hora (01..12)
%I	Hora (01..12)
%l	Hora (1..12)
%i	Minutos, número (00..59)
%r	Hora, formato de 12 horas (hh:mm:ss M)
%T	Hora, formato de 24 horas (hh:mm:ss)
%S	Segundos (00..59)
%s	Segundos (00..59)
%pag	mañana o tarde
%w	Día de la semana (0=domingo..6=sábado)
%U	Semana (00..53), donde el domingo se considera el primer día de la semana.
%u	Semana (00..53), donde el lunes se considera el primer día de la semana
%V	Semana (01..53), donde el domingo se considera el primer día de la semana. Usado con `%X"
%v	Semana (01..53), donde el lunes se considera el primer día de la semana. Usado con `%x"
%%	Literalmente "%".

Comentarios

27.11.2008 ----
Hola shaitán!!!
Yo mismo he estado trabajando en php y mysql durante cinco años, y todo el tiempo estaba cambiando la fecha en php...
Ni siquiera se me ocurrió que sería más fácil usar la función incorporada de MySQL.

28/11/2008 Zheka
¡Asimismo! Siempre he usado mi función php

03/12/2008 Serguéi
Bueno, en general, ¿alguien utiliza este enfoque?
¿O todos usan php para revertir la fecha?
Yo personalmente nunca he revertido una fecha en mysql, sigo haciendo todo en php

28/06/2009 Ilya
Desafortunadamente nada funcionó :(

08/07/2009 Vitaly
Genial, gracias por la función. Me pregunto qué otros garabatos hay.

14/07/2009 DSan
Gracias, ayudó mucho. Todo lo que queda es mostrar el nombre del mes en ruso)

28/07/2009 Vlad
MySQL=>PHP
seleccione unix_timestamp (fecha_inicio) como fecha_inicio_php
código php
fecha("d.m.Y",$fila["start_date_php"])

PHP=>MySQL
actualizar el conjunto de tablas start_date=DATE_FORMAT(STR_TO_DATE("19/12/2009 6:35:22 PM","%d.%m.%Y %H:%i"),"%Y.%m.%d % Hola")

18/08/2009 Invitado
2:DSanto
Existe una función tan maravillosa:
ELT(MES("2004-04-10"), "enero", "febrero", "marzo", "abril", "mayo", "junio", "julio", "agosto", septiembre","octubre","noviembre","diciembre")

Úselo. :-)

29/10/2009 Vladímir
Gracias, sobre ELT(MES("2004-04-10"), "enero", "febrero", "marzo", "abril", "mayo", "junio", "julio", "agosto". ","septiembre","octubre","noviembre","diciembre").
No lo he oído.

07/10/2010 Evgeniy
¿Qué funciona más rápido? ¿Conversión en la solicitud o como resultado de la función PHP?

07/10/2010 defensor
Como mínimo, hay menos memoria para procesar, menos llamadas a funciones, menos asignaciones de memoria... Constantemente uso algo como esto, pero no en mysql, sino en postgresql.

08/10/2010 Administrador
Evgeniy, el defensor, dijo correctamente que esta transformación debería funcionar mejor a través de la base de datos, pero claro, si hablamos de extraer una gran cantidad de datos.
Si extrae entre 10 y 20 registros, entonces no hay diferencia en cómo convertir la fecha en comparación con otras cargas, esto es una nimiedad;

27/01/2011 pcemma
uhh gracias al afftor (: ya no es necesario usar mi función mega cool para la conversión (:

13/04/2011 Xes
CÓMO USARLO EN PHP
mientras ($sqlr=mysql_fetch_array($sql))
{
echo ($sqlr["comadd"]." ".$sqlr["comentario"]."

");

$sqlr["comadd"] - ¿Necesita presentarlo en formato normal?
}

14/04/2011 Vitaly
Tengo una fecha en la base de datos en el formato 19/11/2008, tipo de tabla VARCHAR, ¿cómo puedo reescribirla en la base de datos en el formato 2008-11-19?
Manos solo por mucho tiempo...
Gracias.

15/04/2011 administración
Xes es una función de MySQL, debe usarla en una consulta SQL que, a juzgar por su código, se encuentra en algún lugar arriba. No se puede utilizar en esta sección de código.

15/04/2011 administración
Vitaly, simplemente cambia el tipo de celda a FECHA, mysql convertirá automáticamente todos los datos en esta celda al formato 2008-11-19.
Pero por si acaso, antes de cambiar el tipo de celda, haga un volcado de esta tabla, porque de repente la base de datos hará algo mal y la tabla se romperá por completo.

Si es importante para usted dejar el tipo de campo como varchar, luego de configurar el tipo de FECHA, configúrelo nuevamente en varchar...

Esta es la opción más sencilla, pero no del todo correcta, pero comprobé que funciona.

14/05/2011 DDD
fecha("d-m-Y",strtotime("$mifila"));

24/05/2011 Konstantin
y siempre tomo SELECT *,UNIX_TIMESTAMP(creado) COMO creado DESDE...
Pero con un motor puedo hacerlo en cualquier formato. Incluso solo un día, incluso solo un tiempo...
y comparar cual es mayor que 14/05/2011 o 14/05/2010...
Y lo escribo así:
...date=".gmdate("Y-m-d H:i:s",$creado)...
y en general, no veo ninguna razón para cambiar de hábitos.

24/05/2011 Serguéi
Konstantin, yo mismo uso php date() para la salida, pero aquí estamos considerando la opción de convertir la fecha no a través de php, sino a través de mysql.

Yo diría que esto es sólo una descripción general de la función mysql y ciertamente tiene derecho a existir...

A continuación se muestra un ejemplo que utiliza funciones de fecha. La siguiente consulta selecciona todos los registros con el valor date_col dentro de los últimos 30 días:

Mysql> SELECCIONE algo DE tbl_name DONDE A_DÍAS (AHORA()) - A_DÍAS (fecha_col)<= 30;

Devuelve el índice del día de la semana para la fecha (0 = lunes, 1 = martes, ... 6 = domingo):

Mysql> SELECCIONAR DÍA DE LA SEMANA ("1998-02-03 22:23:00");

-> 1 mysql> SELECCIONAR DÍA DE LA SEMANA("1997-11-05");

-> 2

DÍA DEL MES (fecha)

Devuelve el número ordinal del día del mes para el argumento de fecha, en el rango de 1 a 31:

Mysql> SELECCIONAR DÍA DEL MES("1998-02-03");

-> 3

Devuelve el número ordinal del día del año para el argumento de fecha, en el rango de 1 a 366:

Mysql> SELECCIONAR DÍA DEL AÑO("1998-02-03");

-> 34

Devuelve el número de serie del mes del año para el argumento de fecha, entre 1 y 12:

Mysql> SELECCIONAR MES("1998-02-03");

Devuelve el número del trimestre del año para el argumento de fecha, con un rango de 1 a 4:

Mysql> SELECCIONAR CUARTO("98-04-01");

-> 2

SEMANA(fecha), SEMANA(fecha,primero)

Dado un argumento, devuelve el número ordinal de la semana del año para la fecha, que va de 0 a 53 (sí, posiblemente el comienzo de la semana 53) para las regiones donde el domingo se considera el primer día de la semana. El formulario SEMANA() con dos argumentos le permite especificar si la semana comienza en domingo o lunes. El resultado estará en el rango 0-53 o 1-52.

Así es como funciona el segundo argumento:

Mysql> SELECCIONAR SEMANA("1998-02-20");

-> 7 mysql> SELECCIONAR SEMANA("1998-02-20",0);

-> 7 mysql> SELECCIONAR SEMANA("1998-02-20",1);

-> 8 mysql> SELECCIONAR SEMANA("1998-12-31",1);

-> 53

Nota: En la versión 4.0, la función SEMANA(#,0) se cambió para que coincida con el calendario de EE. UU.

Tenga en cuenta que si la semana es la última semana del año anterior, MySQL devolverá 0 a menos que especifique 2 o 3 como argumento opcional:

Mysql> SELECCIONAR AÑO("2000-01-01"), SEMANA("2000-01-01",0);

-> 2000, 0 mysql> SELECCIONAR SEMANA("2000-01-01",2);

-> 52

Podemos suponer que MySQL debería devolver 52 porque esta fecha es la semana 52 del año 1999. Decidimos devolver 0 porque queremos que la función proporcione "el número de la semana en el año especificado". Esto hace que la función WEEK() sea más confiable cuando se usa junto con otras funciones que calculan partes de fechas.

Si todavía te importa cuál es la semana correcta del año, entonces puedes usar 2 o 3 como argumento opcional, o usar YEARWEEK()

Mysql> SELECCIONAR AÑOSEMANA("2000-01-01");

-> 199952 mysql> SELECCIONAR MEDIO(AÑOSEMANA("2000-01-01"),5,2);

-> 52

Devuelve el año de la fecha en el rango de 1000 a 9999:

Devuelve el número de segundos para el argumento de tiempo, entre 0 y 59:

Mysql> SELECCIONAR SEGUNDO("10:05:03");

-> 3

Agrega N meses al período P (en formato AAAA o AAAAMM). Devuelve un valor en formato AAAAMM. Tenga en cuenta que el argumento del período P no es un valor de fecha:

Mysql> SELECCIONAR PERIOD_ADD(9801,2);

-> 199803

PERIODO_DIFF(P1,P2)

Devuelve el número de meses entre los períodos P1 y P2. P1 y P2 deben estar en formato AAAA o AAAAMM. Tenga en cuenta que los argumentos de período P1 y P2 no son valores de fecha:

Mysql> SELECCIONAR PERIOD_DIFF(9802,199703);

-> 11

DATE_ADD(fecha, tipo de expresión INTERVALO), DATE_SUB(fecha, tipo de expresión INTERVALO), ADDDATE(fecha, tipo de expresión INTERVALO), SUBDATE(fecha, tipo de expresión INTERVALO) Estas funciones realizan operaciones aritméticas en fechas. Ambos son nuevos en MySQL 3.22. Las funciones ADDDATE() y SUBDATE() son sinónimos de DATE_ADD() y DATE_SUB() . En MySQL 3.23, puede usar los operadores + y - en lugar de las funciones DATE_ADD() y DATE_SUB() si la expresión de la derecha es una columna de tipo DATE o DATETIME (vea el ejemplo a continuación). El argumento de fecha es un valor DATETIME o DATE que especifica la fecha de inicio.	La expresión expr especifica la cantidad de intervalo que se suma o resta de la fecha de inicio. La expresión expr es una cadena que puede comenzar con - para valores de intervalo negativos. La palabra clave type indica cómo se debe interpretar una expresión determinada. La función auxiliar EXTRACT(tipo DE fecha) devuelve un intervalo del tipo especificado (tipo) a partir de un valor de fecha. La siguiente tabla muestra la relación entre los argumentos tipo y expr: Significado
Tipo	Formato esperado
exprés	SEGUNDO
ARTÍCULOS DE SEGUNDA CLASE	MINUTO
MINUTOS	HORA
HORAS	DÍA
DÍAS	MES
MESES	AÑO
AÑOS	MINUTO_SEGUNDO
"MINUTOS:SEGUNDOS"	HORA_MINUTO
"HORAS:MINUTOS"	DÍA_HORA
"DIAS HORAS"	AÑO_MES
"AÑOS-MESES"	HORA_SEGUNDA
"HORAS:MINUTOS:SEGUNDOS"	DÍA_MINUTO

"DÍAS HORAS:MINUTOS"

Mysql > SELECCIONE "1997-12-31 23:59:59" + INTERVALO 1 SEGUNDO;

-> 1998-01-01 00:00:00 mysql> SELECCIONAR INTERVALO 1 DÍA + "1997-12-31";

-> 1998-01-01 mysql> SELECCIONE "1998-01-01" - INTERVALO 1 SEGUNDO;

-> 1997-12-31 23:59:59 mysql> SELECCIONAR FECHA_ADD("1997-12-31 23:59:59", INTERVALO 1 SEGUNDO);

-> 1998-01-01 00:00:00 mysql> SELECCIONAR FECHA_ADD("1997-12-31 23:59:59", INTERVALO 1 DÍA);

-> 1998-01-01 23:59:59 mysql> SELECCIONAR FECHA_ADD("1997-12-31 23:59:59", INTERVALO "1:1" MINUTO_SEGUNDO);

-> 1998-01-01 00:01:00 mysql> SELECCIONAR FECHA_SUB("1998-01-01 00:00:00", INTERVALO "1 1:1:1" DÍA_SEGUNDO);

La función EXTRACT() utiliza los mismos tipos de intervalo que las funciones DATE_ADD() o DATE_SUB(), pero EXTRACT() extrae una parte de un valor de fecha en lugar de realizar aritmética.

Mysql> SELECCIONAR EXTRACTO (AÑO DE "1999-07-02");

-> 1999 mysql> SELECCIONAR EXTRACTO (AÑO_MES DE "1999-07-02 01:02:03");

-> 199907 mysql> SELECCIONAR EXTRACTO (DÍA_MINUTO DE "1999-07-02 01:02:03");

-> 20102

la función devuelve el número de día para la fecha especificada en el argumento de fecha (el número de días que han pasado desde el año 0):

Mysql> SELECCIONAR TO_DAYS(950501);

-> 728779 mysql> SELECCIONAR A_DÍAS("1997-10-07");

-> 729669

La función TO_DAYS() no está diseñada para usarse con valores anteriores a la introducción del calendario gregoriano (1582) porque no tiene en cuenta los días perdidos cuando se cambió el calendario.

Determinante	Descripción
Devuelve el valor de FECHA para el día número N dado:	Nombre del mes (enero...diciembre)
Mysql> SELECCIONAR FROM_DAYS(729669);	Nombre del día de la semana (domingo...sábado)
-> "1997-10-07"	Día del mes con sufijo en inglés (0.º, 1.º, 2.º, 3.º, etc.)
La función FROM_DAYS() no está diseñada para usarse con valores anteriores a la introducción del calendario gregoriano (1582) porque no tiene en cuenta los días perdidos cuando se cambió el calendario.	Año, fecha, 4 dígitos
DATE_FORMAT(fecha,formato)	Año, fecha, 2 dígitos
Formatea el valor de fecha según la cadena de formato. Se pueden utilizar los siguientes calificadores en la línea de formato:	Año para una semana donde el domingo se considera el primer día de la semana, número de 4 dígitos, usado con "%V"
%METRO	Año para una semana donde el domingo se considera el primer día de la semana, número de 4 dígitos, usado con "%v"
%W	Nombre abreviado del día de la semana (domingo...sábado)
%D	Día del mes, número (00..31)
%Y	Día del mes, número (0..31)
%año	Mes, día (00..12)
%INCÓGNITA	Mes, fecha (0..12)
%incógnita	Nombre abreviado del mes (enero...diciembre)
%a	Día del año (001..366)
%d	Hora (00..23)
%mi	Hora (0..23)
%metro	Hora (01..12)
%do	Hora (01..12)
%b	Hora (1..12)
%j	Minutos, número (00..59)
%H	Hora, formato de 12 horas (hh:mm:ss M)
% k	Hora, formato de 24 horas (hh:mm:ss)
%h	Segundos (00..59)
%I	Segundos (00..59)
%l	mañana o tarde
%i	Día de la semana (0=domingo..6=sábado)
%r	Semana (00..53), donde el domingo se considera el primer día de la semana.
%T	Semana (00..53), donde el lunes se considera el primer día de la semana
%S	%s
%pag	%w
%%	%U

Semana (01..53), donde el domingo se considera el primer día de la semana. Usado con "%X"

Semana (01..53), donde el lunes se considera el primer día de la semana. Usado con "%x"

Esta función se usa de manera similar a la función DATE_FORMAT() descrita anteriormente, pero la cadena de formato solo puede contener especificadores de formato relacionados con horas, minutos y segundos. Al especificar otros calificadores, se devolverá el valor NULL o 0.

FECHA ACTUAL() , FECHA_ACTUAL

Devuelve la fecha de hoy como un valor en el formato AAAA-MM-DD o AAAAMMDD, dependiendo de si la función se usa en un contexto de cadena o numérico:

Mysql> SELECCIONAR FECHA ACTUAL();

-> "1997-12-15" mysql> SELECCIONAR FECHA ACTUAL() + 0;

-> 19971215

CURTIME() , CURRENT_TIME

Devuelve la hora actual como un valor en el formato HH:MM:SS o HHMMS, dependiendo de si la función se usa en un contexto de cadena o numérico:

Mysql> SELECCIONE CURTIME();

-> "23:50:26" mysql> SELECCIONE CURTIME() + 0;

-> 235026

AHORA(), SYSDATE(), CURRENT_TIMESTAMP

Devuelve la fecha y hora actuales como un valor en el formato AAAA-MM-DD HH:MM:SS o AAAAMMDDHHMMSS , dependiendo de si la función se usa en un contexto de cadena o numérico:

Mysql> SELECCIONAR AHORA();

Cuando se utiliza la función UNIX_TIMESTAMP en una columna TIMESTAMP, esta función devolverá el valor de la marca de tiempo interna directamente, sin la conversión implícita de la cadena a una marca de tiempo ("string-to-unix-timestamp"). Si la fecha proporcionada está fuera del rango válido, entonces la función UNIX_TIMESTAMP() devolverá 0, pero tenga en cuenta que solo se realiza una verificación básica (año 1970-2037, mes 01-12, día 01-31). Si necesita realizar una resta de columnas UNIX_TIMESTAMP(), el resultado se puede convertir a enteros con signo.

Consulte la Sección 6.3.5, “Funciones de conversión de tipos”.

FROM_UNIXTIME (marca de tiempo_unix)

Devuelve una representación del argumento unix_timestamp como un valor en el formato AAAA-MM-DD HH:MM:SS o AAAAMMDDHHMMSS , dependiendo de si la función se usa en un contexto de cadena o numérico:

Mysql> SELECCIONAR FROM_UNIXTIME(875996580);

-> "1997-10-04 22:23:00" mysql> SELECCIONAR FROM_UNIXTIME(875996580) + 0;

-> 19971004222300

FROM_UNIXTIME(unix_timestamp,formato)

Devuelve una representación de cadena del argumento unix_timestamp, formateada según la cadena de formato. La cadena de formato puede contener los mismos calificadores que se enumeran en la descripción de la función DATE_FORMAT():

Mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), "%Y %D %M %h:%i:%s %x");

-> "1997 23 de diciembre 03:43:30 1997"

SEC_TO_TIME(segundos)

Devuelve el argumento de segundos, convertido a horas, minutos y segundos, como un valor en el formato HH:MM:SS o HHMMSS , dependiendo de si la función se usa en un contexto numérico o de cadena:

Mysql> SELECCIONAR SEC_TO_TIME(2378);

-> "00:39:38" mysql> SELECCIONAR SEC_TO_TIME(2378) + 0;

TIMESTAMP: tipo de datos para almacenar fecha y hora. Los datos se almacenan como el número de segundos que han pasado desde el inicio de la "era Unix". Rango de valores: 1970-01-01 00:00:00 - 2038-12-31 00:00:00. Ocupa 4 bytes.
AÑO: tipo de datos para almacenar el año. Rango de valores: 1901 - 2155. Ocupa 1 byte.
DATE es un tipo de datos para almacenar fechas. Rango de valores: 1000-01-01 - 9999-12-31. Ocupa 3 bytes.
TIME es un tipo de datos para almacenar el tiempo. Rango de valores: −828:59:59 - 828:59:59. Ocupa 3 bytes.
DATETIME es un tipo de datos para almacenar fecha y hora. Rango de valores: 1000-01-01 00:00:00 - 9999-12-31 00:00:00. Ocupa 8 bytes.

Nota para la anfitriona. Lo interesante es que la mayoría de los programadores creen que el concepto de "marca de tiempo" es la hora de Unix. De hecho, una marca de tiempo es una marca que es una secuencia de caracteres que indican la fecha y/u hora en que ocurrió un determinado evento. Y “hora Unix” (hora Unix) o hora POSIX es la cantidad de segundos que han pasado desde la medianoche del 1 de enero de 1970 UTC. El concepto de marca de tiempo es más amplio que el de tiempo de Unix.

Después de analizar la descripción de los tipos presentados anteriormente, se pueden sacar casi todas las conclusiones sobre las ventajas y desventajas de ciertos tipos. Todo es bastante simple y obvio.

Pero antes de hablar sobre el uso de estos tipos, quiero señalar que en la práctica a menudo se usa otro tipo para almacenar la fecha y la hora: un valor entero (para almacenar la fecha - INT (4 bytes), fecha y hora - BIGINT (8 bytes )). La única diferencia entre el uso de tipos de números enteros y DATE y DATETIME es que los datos no se formatean durante la salida y, en los cálculos con fechas y horas, los números enteros se deben convertir al tipo de calendario apropiado. Además, no se comprueba la validez del valor presentado antes de guardarlo. Se conservan las capacidades de clasificación. Por lo tanto, tiene sentido utilizar INT y BIGINT en los mismos casos que DATE y DATETIME, para maximizar la portabilidad y la independencia del DBMS. No veo otras ventajas; si las hay, te sugiero que las indiques en los comentarios.

Usando tipos de datos de calendario en MySQL

Comencemos con lo más simple: escriba AÑO. Su única ventaja es su pequeño tamaño: sólo 1 byte. Pero debido a esto, existe una limitación estricta en el rango de valores válidos (el tipo solo puede almacenar 255 valores diferentes). Me cuesta imaginar una situación práctica en la que uno quiera almacenar años estrictamente en el rango de 1901 a 2155. Además, el tipo SMALLINT (2 bytes) proporciona un rango que es suficiente en la mayoría de situaciones para almacenar un año. Y guardar 1 byte por fila en una tabla de base de datos no tiene sentido en nuestro tiempo.

Tipos FECHA Y FECHA HORA se pueden combinar en un grupo. Almacenan una fecha o fecha y hora con un rango bastante amplio de valores válidos, independientemente de la zona horaria configurada en el servidor. Su uso definitivamente tiene sentido práctico. Pero si desea almacenar fechas de eventos históricos que se remontan a más allá de la Era Común, tendrá que elegir otros tipos de datos. Estos tipos son ideales para almacenar fechas de ciertos eventos que potencialmente quedan fuera del rango del tipo TIMESTAMP (cumpleaños, fechas de lanzamiento de productos, elecciones presidenciales, lanzamientos de cohetes espaciales, etc.). Al utilizar estos tipos, es necesario tener en cuenta un matiz importante, del que hablaremos más adelante.

Tipo TIEMPO se puede utilizar para almacenar un período de tiempo cuando no se necesita una precisión inferior a 1 segundo y períodos de tiempo de menos de 829 horas. No hay nada más que agregar aquí.

El tipo más interesante sigue siendo: MARCA DE TIEMPO. Debe considerarse en comparación con DATE y DATETIME: TIMESTAMP también está diseñado para almacenar la fecha y/u hora de ocurrencia de ciertos eventos. Una diferencia importante entre ellos está en los rangos de valores: obviamente, TIMESTAMP no es adecuado para almacenar eventos históricos (incluso cumpleaños), pero es excelente para almacenar los actuales (registro, fechas de publicación de artículos, adición de productos, realización de pedidos). y los próximos eventos en el futuro previsible (lanzamientos de nuevas versiones, calendarios y programadores, etc.).

La principal conveniencia de usar el tipo TIMESTAMP es que para las columnas de este tipo en las tablas puede establecer un valor predeterminado en forma de sustitución de la hora actual, así como configurar la hora actual al actualizar un registro. Si necesita estas funciones, existe un 99% de posibilidades de que TIMESTAMP sea exactamente lo que necesita. (Consulte el manual para saber cómo hacer esto).

No tenga miedo de que su software deje de funcionar a medida que nos acercamos al año 2038. En primer lugar, antes de este momento, lo más probable es que su software simplemente deje de utilizarse (especialmente las versiones que se están escribiendo ahora). En segundo lugar, a medida que se acerca esta fecha, los desarrolladores de MySQL definitivamente encontrarán algo para preservar la funcionalidad de su software. Todo se solucionará, así como el problema del año 2000.

Entonces, usamos el tipo TIMESTAMP para almacenar las fechas y horas de eventos de nuestro tiempo, y DATETIME y DATE para almacenar las fechas y horas de eventos históricos o eventos del futuro profundo.

Los rangos de valores son una diferencia importante entre los tipos TIMESTAMP, DATETIME y DATE, pero no son la diferencia principal. Principal que TIMESTAMP almacena el valor en UTC. Al almacenar un valor, se traduce de la zona horaria actual a UTC y, al leerlo, se traduce de la zona horaria actual a UTC. DATETIME y DATE siempre almacenan y muestran la misma hora, independientemente de las zonas horarias.

Las zonas horarias se configuran globalmente en MySQL DBMS o para conexión actual Este último se puede utilizar para garantizar el trabajo de diferentes usuarios en diferentes zonas horarias a nivel de DBMS. Todos los valores de tiempo se almacenarán físicamente en UTC, se recibirán del cliente y se entregarán al cliente, en los valores de su zona horaria. Pero sólo cuando se utiliza el tipo de datos TIMESTAMP. DATE y DATETIME siempre reciben, almacenan y devuelven el mismo valor.

La función NOW() y sus sinónimos devuelven el valor de la hora en la zona horaria actual del usuario.

Dadas todas estas circunstancias, debes tener mucho cuidado al cambiar la zona horaria dentro de una conexión al servidor y utilizar los tipos DATE y DATETIME. Si necesita guardar una fecha (por ejemplo, fecha de nacimiento), no habrá problemas. La fecha de nacimiento es la misma en cualquier zona. Aquellos. Si nació el 1 de enero a las 0:00 UTC/GMT+0, entonces esto No Significa que en Estados Unidos celebrarán tu cumpleaños el 31 de diciembre. Pero si decides almacenar tiempo eventos en la columna DATETIME, entonces simplemente no será posible trabajar con zonas horarias de usuario en el nivel DBMS. Déjame explicarte con un ejemplo:

El usuario X trabaja en la zona UTC/GMT+2, Y - en la zona UTC/GMT+3. Para las conexiones de usuario a MySQL, se establece una zona horaria correspondiente (cada una tiene su propia). El usuario publica un mensaje en el foro, nos interesa la fecha en que se escribió el mensaje.

Opción 1: FECHA HORA. El usuario X escribe un mensaje a las 14:00 UTC/GMT+2. El valor en el campo "fecha" del mensaje se sustituye como resultado de ejecutar la función AHORA() - 14:00. El usuario Y lee la hora en que se escribió el mensaje y ve las mismas 14:00. Pero su configuración está configurada en UTC/GMT+3, y cree que el mensaje no fue escrito justo ahora, sino hace una hora.

Opción 2: MARCA DE TIEMPO. El usuario X escribe un mensaje a las 14:00 UTC/GMT+2. El campo "fecha" contiene el resultado de ejecutar la función NOW(), en este caso, 12:00 UTC/GMT+0. El usuarioY lee la hora en que se escribió el mensaje y recibe (UTC/GMT+3)(12:00 UTC/GMT+0) = 15:00 UTC/GMT+3. Todo sale exactamente como queremos. Y lo más importante, es extremadamente cómodo de usar: para admitir zonas horarias personalizadas, no es necesario escribir ningún código de conversión de hora.

Las posibilidades de sustituir la hora actual y trabajar con zonas horarias en el tipo TIMESTAMP son tan poderosas que si necesitas almacenar una fecha sin hora en un determinado registro, aún debes usar TIMESTAMP en lugar de DATE, sin guardar 1 byte de diferencia. entre ellos. En este caso, simplemente ignore "00:00:00".

Si no puede usar TIMESTAMP debido al rango relativamente pequeño de sus valores (generalmente 1-2 casos versus 10-15 en la base de datos del sitio), tendrá que usar DATETIME y ajustar cuidadosamente sus valores en los lugares correctos ( es decir, al escribir en este campo convertir la fecha a UTC, y al leer, a la hora de la zona del usuario lector). Si solo almacena la fecha, lo más probable es que no importe la zona horaria que tenga: todos celebran el Año Nuevo el 1 de enero, hora local, por lo que no necesita traducir nada aquí.

Para empezar, quiero abordar el tema de qué formato es mejor para almacenar fechas en la base de datos: MARCA DE TIEMPO o FECHA HORA. Esta pregunta se ha planteado y se plantea muchas veces en foros, blogs, etc. Pero para no enviarte inmediatamente a los motores de búsqueda, intentaré mostrar la diferencia con palabras sencillas y con un ejemplo. Tipo FECHA HORA- almacena el valor de la fecha en el formato "AAAA-MM-DD HH:MM:SS" y no depende de la zona horaria. MARCA DE TIEMPO- almacena una marca de tiempo, es decir el número de segundos que han pasado desde el 1 de enero de 1970. MySQL convierte estos valores teniendo en cuenta la zona horaria actual tanto al escribir en la base de datos como al generar desde ella. Qué quiere decir esto...
Por ejemplo, acaba de agregar un artículo a la base de datos, en su calendario es el primero de enero de 2014 y en su reloj es la 01:00. Si el campo de fecha es del tipo DATETIME, todos los que visiten el sitio verán exactamente esta fecha y hora, independientemente de su lugar de residencia. Todo parece estar bien, pero el usuario ( llamémoslo "Bill G"), que vive en algún lugar de Nueva York, aún no ha llegado el primero de enero; para él es el 31 de diciembre de 2013 y su reloj marca las 19:00. Está un poco perplejo, porque... Aún no ha empezado a celebrar el Año Nuevo, pero ya está imaginando “un artículo del futuro” ;) Esto no sucederá con el tipo TIMESTAMP, porque Al generar, se tendrá en cuenta su zona horaria.
“¡Todo está claro!”, dices y rápidamente cambias todos los campos de fecha al tipo TIMESTAMP, y Bill G dará un suspiro de alivio, pero no por mucho tiempo. Al registrarse en su sitio, Bill indicó la fecha y hora de su nacimiento. Viajando por el mundo, siempre mira su sitio web y descubre con horror que la hora, y a veces la fecha de su nacimiento, es siempre diferente, porque... se muestran teniendo en cuenta la zona horaria en la que se encuentra actualmente. Sí, en este caso, el tipo TIMESTAMP jugó una broma cruel.
Concluimos que para ciertas tareas, es necesario seleccionar el tipo de campo apropiado o controlar la grabación/salida dependiendo del resultado deseado.

Pasemos a problemas populares y opciones para resolverlos. Seleccione registros dentro del rango de fechas especificado, es decir. durante un cierto período de tiempo.

SELECCIONE * DESDE `table_name` DONDE `date_field` ENTRE "2014-07-05" Y "2014-07-15" ORDEN POR `date_field`;

Se seleccionarán todos los registros donde las fechas en el campo "date_field" estén en el rango del 5 de julio de 2014 al 15 de julio de 2014, incluidas las fechas especificadas. No debemos olvidar que por defecto las fechas en MySQL se almacenan en el formato "AAAA-MM-DD HH:MM:SS" y, en consecuencia, la máscara de formato es "%Y-%m-%d %H:%i: %s" (estándar ISO). ¿Cómo solucionar el problema si la fecha no viene en este formato? Descartemos las opciones de PHP y veamos cómo se puede hacer esto en la solicitud misma. Y para tales fines, necesitaremos la función. STR_TO_DATE(). Sintaxis: STR_TO_DATE(cadena, formato), Dónde " cadena" - cadena de fecha y " formato" es el formato correspondiente. Probemos:

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

El resultado de la ejecución es una fecha en el formato utilizado por defecto en MySQL. Es decir, debemos especificar no el formato en el que queremos recibir la fecha de salida, sino el formato en el que proporcionamos la fecha para su procesamiento. Usando este método, nuestra entrada anterior podría verse así:

SELECCIONE * DESDE `table_name` DONDE `date_field` ENTRE STR_TO_DATE("05/07/2014", "%d.%m.%Y") Y STR_TO_DATE("15 de julio de 2014", "%M %d,%Y ") ORDEN POR `campo_fecha`;

Ya que hemos tocado el tema del formato de fecha, veamos cómo obtener una fecha al muestrear en el formato que necesitamos, porque Mucha gente está mucho más acostumbrada a ver “31/12/2014” o “31 de diciembre de 2014” que “31-12-2014”. Para tales fines, utilice la función. FECHA_FORMATO(). Sintaxis: DATE_FORMAT(fecha, formato), Dónde " fecha" - cadena de fecha y " formato" - el formato al que se va a convertir " fecha". A diferencia de la función STR_TO_DATE(), nosotros mismos indicamos el formato de salida deseado, pero la fecha debe especificarse en formato ISO, es decir, "AAAA-MM-DD HH:MM:SS". Comprobamos:

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

Si nos estuviéramos comunicando con usted en tiempo real, lo más probable es que en este punto la pregunta siguiera inmediatamente: " Pero, ¿cómo mostrar el mes en otro idioma: ucraniano, ruso o chino?"Es muy simple: establezca la configuración regional requerida. Y esto se puede hacer en el archivo de configuración de MySQL (my.cnf) o simplemente con una solicitud desde PHP, después de conectarse a la base de datos y antes de las consultas principales:

SET lc_time_names = ru_RU; SELECT DATE_FORMAT("2014-12-31", "%d %M %Y"); // resultado: 31 de diciembre de 2014 // si lo deseas, también puedes agregar “g”. o "año" SELECT DATE_FORMAT("2014-12-31", "%d %M %Y año"); // resultado: 31 de diciembre de 2014

¡Belleza! ;) Y algunos ejemplos más de solicitudes que también suelen ser necesarias, pero que causan confusión entre los principiantes.

// Seleccionar registros para el día actual SELECT * FROM `table_name` WHERE `date_field` >= CURDATE(); // Todos los registros de ayer SELECT * FROM `table_name` WHERE `date_field` >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND `date_field` NOW() - INTERVAL 30 DAY; // Selecciona todo para un mes específico del año actual (por ejemplo, el mes de mayo) SELECT * FROM `table_name` WHERE YEAR(`date_field`) = YEAR(NOW()) AND MONTH(`date_field`) = 5 ; // o para el mes de mayo, pero en 2009 SELECT * FROM `table_name` WHERE YEAR(`date_field`) = 2009 AND MONTH(`date_field`) = 5;

No veo el sentido de describir en detalle las funciones de MySQL utilizadas en los ejemplos, porque... son intuitivos y para una persona con al menos un poco de conocimiento de inglés no será difícil entender que, por ejemplo, la función MES() devuelve el mes de la fecha, AÑO()- su año, y DÍA() (o sinónimo DAYOFMONTH()) - día. Palabra clave INTERVALO- sirve para operaciones aritméticas sobre fechas y sus cambios.

SELECCIONE "2014-07-07 23:59:59" + INTERVALO 1 SEGUNDO; // resultado: 2014-07-08 00:00:00 SELECCIONAR "2014-07-07 23:59:59" + INTERVALO 1 DÍA; // resultado: 2014-07-08 23:59:59 // lo mismo. pero usando la función DATE_ADD() SELECT DATE_ADD("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-08 23:59:59 // Si necesita restar en lugar de sumar SELECT DATE_SUB("2014-07-07 23:59:59", INTERVAL 1 DAY); // 2014-07-06 23:59:59 // o simplemente SELECCIONE "2014-07-07 23:59:59" - INTERVALO 1 DÍA; // 2014-07-06 23:59:59

Estas no son todas funciones para trabajar con fechas, y le aconsejaría que las revise con fines informativos en el sitio web oficial para conocer su existencia si surge una situación no estándar. Pero espero que incluso una descripción tan breve de las funciones de MySQL para trabajar con fechas en este artículo le ayude a navegar la situación y tomar la decisión correcta. Si aún surgen dificultades, haga preguntas en este tema o en la sección "Su pregunta". Lo resolveremos juntos ;)

Popular en la categoría: