11.3. Tipos de fecha y hora

MySQL 5.0

previous page next page

11.3. Tipos de fecha y hora

Los tipos de fecha y hora para representar valores temporales son , , , , y . Cada tipo temporal tiene un rango de valores legales, así como un valor “zero” que se usa cuando se especifica un valor ilegal que MySQL no puede representar. El tipo tiene un comportamiento automático especial, descrito posteriormente.

Desde MySQL 5.0.2, MySQL da advertencias/errores si trata de insertar una fecha ilegal. Puede hacer que MySQL acepte ciertas fechas, tales como , usando el modo SQL . (Antes de 5.0.2, este modo era el comportamiento por defecto de MySQL). Esto es útil cuando quiere almacenar el valor “posiblemente erróneo” que el usuario especifica (por ejemplo, en un formulario web) en la base de datos para un posterior procesamiento. En este modo, MySQL sólo verifica que el mes esté en el rango de 0 a 12 y que el día esté en el rango de 0 a 31. Estos rangos incluyen cero ya que MySQL permite almacenar fechas cuando el día o el mes son cero en columnas o . Esto es muy útil para aplicaciones que necesiten almacenar una fecha de nacimiento para la que no sepa la fecha exacta. En este caso, símplemente almacena la fecha como o . Si almacena valores similares a estos, no debe esperar obtener resultados correctos para funciones tales como or que necesitan fechas completas. (Si no quiere permitir ceros en las fechas, puede usar el modo SQL ).

MySQL permite almacenar como “fecha de pruebas” (si no está usando el modo SQL ). Esto es mejor que usar (y usa menos espacio de datos e índice) que usar valores .

Modificando la variable de sistema al modo apropiado, puede especificar exactamente qué tipos de datos quiere soportar con MySQL. Consulte Sección 5.3.2, “El modo SQL del servidor”.

Aquí hay algunas consideraciones generales a tener en cuenta cuando se trabaja con tipos de fecha y hora:

  • MySQL muestra los valores para una fecha o hora en un formato de salida estándard, pero trata de intepretar una variedad de formatos para los valores de entrada que se proporcionan (por ejemplo, cuando especifica un valor para asignar o comparar con un tipo fecha o hora). Sólo los formatos descritos en las siguientes secciones son soportados. Se espera la entrada de valores legales. Si se usan otros formatos pueden ocurrir resultados imprevisibles.

  • Las fechas con años de dos dígitos son ambituas, ya que no se sabe el siglo. MySQL interpreta los años de dos dígitos usando las siguientes reglas:

    • Los años del rango se convierten en .

    • Los años del rango se convierten en .

  • Aunque MySQL trata de interpretar los valores con varios formatos, las fechas siempre deben darse en el orden año-mes-día (por ejemplo, ), en lugar del formato mes-día-año o día-mes-año usados comunmente (por ejemplo, , ).

  • MySQL convierte automáticamente una fecha o hora a un número si el valor se usa en un contexto numérico y viceversa.

  • Cuando MySQL encuentra un valor para fecha o hora que está fuera de rango o es ilegal para el tipo (como se describe al inicio de la sección), lo convierte al valor “cero” para ese tipo. La excepción es que los valores fuera de rango del tipo se reemplazan por el valor límite de rango apropiado para el tipo .

    La siguiente tabla muestra el formato del valor “cero” para cada tipo. Tenga en cuenta que el uso de estos valores produce mensajes de advertencia si el modo SQL está activado.

    Tipo de Columna “Cero” Valor

  • Los valores “cero” son especiales, pero puede almacenarlos o referirse a ellos explícitamente usando los valores mostrados en la tabla. También puede hacerlo usando los valores o , que son más sencillos de escribir.

  • Los valores de fecha o hora “cero” usados a través de MyODBC se convierten automáticamente a en MyODBC 2.50.12 y posterior, ya que ODBC no puede tratar estos valores.

11.3.1. Los tipos de datos DATETIME, DATE y TIMESTAMP

Los tipos , , and están relacionados. Esta sección describe sus características, en que se parecen y en que difieren.

El tipo se usa cuando necesita valores que contienen información de fecha y hora. MySQL recibe y muestra los valores en formato . El rango soportado es de a . (“Soportado” significa que aunque valores anteriores pueden funcionar, no hay garantías)

El tipo se usa cuando necesita sólo un valor de fecha, sin una parte de hora. MySQL recibe y muestra los valores en formato . El rango soportado es de a .

El tipo tiene varias propiedades, en función de la versión de MySQSL y el modo SQL que esté ejecutando el servidor. Estas propiedades se describen posteriormente en esta sección.

Puede especificar valores , , y usando cualquier de los siguientes formatos:

  • Como cadena de carácteres en formato o . Una sintaxis “relajada” se permite: Cualquier carácter de puntuación puede usarse como delimitador entre partes de fecha o de hora. Por ejemplo, , , , y son equivalentes.

  • Como cadena de carácteres en formato or . Se permite una sintaxis “relajada” . Por ejemplo, , , , y son equivalentes.

  • Como cadena de carácteres sin delimitadores en formato o , mientras que la cadena de carácteres tenga sentido como fecha. Por ejemmplo, y se interpretan como , pero es ilegal (tiene una parte de minutos sin sentido) y se convierte en .

  • Como cadena de carácteres sin delimitadores en formato o , mientras que el cadena de carácteres tenga sentido como fecha. Por ejemplo, y se interpretan como , pero es ilegal (tiene una parte de mes y día sin sentido) y se convierte en .

  • Como número en formato o , mientras que el número tenga sentido como fecha. Por ejemplo, y se interpretan como .

  • Como número en formato o , mientras que el número tenga sentido como fecha. Por ejemplo, y se interpretan como .

  • Como resultado de una función que retorne un valor acceptable en un contexto , , o , como o .

Los valores ilegales de , , o se convierten al valor “cero” del tipo apropiado (, , o ).

Para valores especificados como cadenas de carácteres que incluyan partes de fecha delimitadas, no es necesario especificar dos dígitos para valores de mes o día menores que . es lo mismo que . Similarmente, para valores especificados como cadenas de carácteres que incluyan delimitadores para la parte de hora, no es necesario especificar dos dígitos para horas, minutos o segundos menores que . es lo mismo que .

Los valores especificados como números deben tener una longitud de 6, 8, 12, o 14 dígitos. Si un número tiene una longitud de 8 o 14 dígitos, se asume que está en formato o y que el año lo dan los primeros 4 dígitos. Si el número tiene 6 o 12 dígitos de longitud, se asume que está en formato o y que el año lo dan los primeros 2 dígitos. Los números que no tengan estas longitudes se interpretan como si tuvieran ceros a la izquierda y fueran de la longitud permitida más cercana.

Los valores especificados como cadenas de carácteres no delimitadas se interpretan usando su longitud. Si la cadena de carácteres tiene longitud 8 o 14, el año se asume como dado por los primeros 4 carácteres. En el resto de caso, se supone que el año lo dan los primeros 2 carácteres. La cadena de carácteres se interpreta de izquierda a derecha para encontrar el año, mes, día, hora, minuto y segundo, para tantas partes como representa la cadena de carácteres. Esto significa que no debe usar cadenas de carácteres con menos de 6 carácteres. Por ejemplo, si especifica , pensando que representa Marzo, 1999, MySQL inserta un valor “cero” en la tabla. Esto es porque el valor de año y mes son y , pero la parte de día no se encuentra, así que el valor no es una fecha legal. Sin embargo, puede especificar explícitamente un valor de cero para representar partes de día y mes. Por ejemplo, puede usar para insertar el valor .

Puede asignar valores de un tipo a un objeto de un tipo diferente hasta un límite. Sin embargo, hay algunas alteraciones del valor o pérdidas de información:

  • Si asigna un valor a un objeto o , la parte de hora del valor resultante se cambia a ya que el valor no contiene información temporal.

  • Si asigna un valor o a un objeto , la parte temporal del valor resultante se borra porque el tipo no tiene información temporal.

  • Tenga en cuenta que aunque , , y pueden especificarse usando el mismo conjunto de formatos, los tipos no tienen el mismo rango de valores. Por ejemplo, no pueden ser anteriores a o posteriores a . Esto significa que una fecha como , que sería legal como o no es un valor válido y se convierte a si se asigna a un objeto de este tipo.

Tenga en cuenta ciertas cosas al especificar valores temporales:

  • El formato relajado para valores especificados como cadenas de carácteres puede ser problemático. Por ejemplo, un valor como puede parecer una hora por el delimitador '' , pero si se usa en un contexto de fecha se interpreta como . El valor se convierte a ya que no es un mes legal.

  • El servidor MySQL realiza sólo chequeo básico de la validez de las fechas: Los rangos para año, mes y día son de 1000 a 9999, 00 a 12, y 00 a 31, respectivamente. Cualquier fecha que contenga partes fuera de estos rangos está sujeta a conversión a . Tenga en cuenta que esto permite almacenar fechas inválidas como . Para asegurar que una fecha es válida, haga una comprobación en su aplicación.

  • Fechas con valores de año de dos dígitos son ambíguas porque no se conoce el siglo. MySQL interpreta los años de dos dígitos usando las siguientes reglas:

    • Los valores de años en el rango se convierten a .

    • Los valores de años en el rango se convierten a .

11.3.1.1. Propiedades de desde MySQL 4.1

Nota: En antiguas versiones de MySQL (antes de la 4.1), las propiedades de las columnas difieren significativamente en muchas cosas de lo que se describe en esta sección. Si necesita convertir datos antiguos para que funcionen con MySQL 5.0, asegúrese de consultar Manual de referencia de MySQL 4.1 para más detalles.

En MySQL 5.0, se muestran en el mismo formato que las columnas . En otras palabras, el ancho de muestra se limita a 19 carácteres, y el formato es .

El servidor MySQL puede ejecutarse en modo . Cuando el servidor corre en este modo, es idéntico a . Esto es, si el servidor está ejecutándose en modo cuando se crea una tabla, las columnas se crean como columnas . Como resultado, tales columnas usan el formato de salida de , tienen el mismo rango de valores, y no hay inicialización automática o actualización de la fecha y hora actual.

Para activar el modo , cambie el modo SQL del servido a cuando arranque usando la opción o cambiando en tiempo de ejecución la variable global : 

mysql> SET GLOBAL sql_mode=MAXDB;

Un cliente puede hacer que el servidor se ejecute en modo para sus propias conexiones como se muestra: 

mysql> SET SESSION sql_mode=MAXDB;

Desde MySQL 5.0.2, MySQL no acepta valores timestamp que incluyan cero en la columna de día o hora o valores que no sean fechas válidas. La única excepción es el valor especial .

En MYSQL 5.0, tiene considerable flexibilidad para determinar cuando se actualiza e inicializa automáticamente y qué columna debe tener ese comportamiento:

  • Puede asignar la fecha y hora actual como el valor por defecto y el valor de actualización automático, como se hacía anteriormente. Pero es posible tener sólo uno u otro comportamiento automático, o ninguno de ellos. (No es posible tener un comportamiento para una columna y el otro para la otra columna.)

  • Puede especificar qué columna inicializar o actualizar con la fecha y hora actuales. Ya no hace falta que sea la primera columna .

Tenga en cuenta que la información en la siguiente discusión se aplica a columnas sólo para tablas no creadas con el modo activado. (Como se menciona anteriormente, el modo hace que las columnas se creen como columnas .) Las reglas que gobiernan la inicialización y actualización de columnas en MySQL 5.0 son las siguientes:

  • Si un valor se especifica para la primera columna en una tabla, no se ignora. El valor por defecto puede ser o una fecha y hora constante.

  • es lo mismo que para la primera columna . Para cualquier otra columna , se trata como .

  • Cualquier columna individual en una tabla puede actualizarse e inicializarse con la fecha y hora actual automáticamente.

  • En un comando , la primera columna puede declararse de cualquiera de las siguientes formas:

    • Con las cláusulas y , la columna tiene la fecha y hora actual como su valor por defecto, y se actualiza automáticamente.

    • Sin las cláusulas ni , es lo mismo que .

    • Con la cláusula y sin , la columna tiene la fecha y hora actual como valor por defecto pero no se actualiza automáticamente.

    • Sin cláusula y con cláusula , la columna tiene por defecto 0 y se actualiza automáticamente.

    • Con un valor constante , la columna tiene el valor dado por defecto. Si la columna tiene una cláusula se actualiza automáticamente, de otro modo no lo hace.

    En otras palabras, puede usar la fecha y hora actuales para el valor inicial y el valor de actualización automática, o uno de ellos o ninguno. (Por ejemplo, puede especificar para activar actualización automática sin tener la columna inicializada .)

  • Cualquiera de , , o puede usarse en las cláusulas y . Todas tienen el mismo efecto.

    El orden de los dos atributos no importa. Si se especifican y para una columna , cualquiera puede preceder al otro.

    Ejemplo. Estos comandos son equivalentes: 

    CREATE TABLE t (ts TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
DEFAULT CURRENT_TIMESTAMP);

  • Para especificar el valor por defecto o actualización automática para una columna distinta a la primera, debe suprimir la actualización e inicialización automática de la primera columna asignándole explícitamente una valor constante (por ejemplo, o ). Luego, para la otra columna , las reglas son las mismas que para la misma columna , excepto que no puede omitir ambas cláusulas y . Si lo hace, no habrá inicialización ni actualización automática.

    Ejemplo, estos comandos son equivalentes: 

    CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

En MySQL 5.0, puede asignar la zona horaria actual para cada conexión, como se describe en Sección 5.9.8, “Soporte de zonas horarias en el servidor MySQL”. Los valores se almacenan en UTC, convirtiéndose desde la zona horaria actual para almacenamiento, y volviéndose a convertir a la zona horaria actual al mostrarse. Mientras la zona horaria permanezca constante, puede obtener el mismo valor que hay almacenado. Si almacena un valor , cambia la zona horaria y luego rescata el valor, es diferente que el valor almacenado. Esto ocurre porque no se usa la misma zona horaria para la conversión en ambas direcciones. La zona horaria actual está disponible en la variable de sistema .

Puede incluir el atributo en la definición de una columna para permitir que la columna contenga valores . Por ejemplo: 

CREATE TABLE t
(
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT 0,
  ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);

Si el atributo no se especifica, asignar el valor a la columna resulta en que se almacena la hora y fecha actuales. Tenga en cuenta que una columna que permita valores no no almacenará la fecha y hora actual a no ser que su valor por defecto se defina como , o o se inserte en la columna. En otras palabras, una columna definida como se actualizará automáticamente sólo si se crea usando una definición como las siguientes: 

CREATE TABLE t (ts NULL DEFAULT CURRENT_TIMESTAMP);

De otro modo - esto es, si la columna se define usando pero no usando , como se muestra aquí... 

CREATE TABLE t1 (ts NULL DEFAULT NULL);
CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00');

...entonces debe insertar el valor explícitamente correspondiente a la fecha y hora actuales. Por ejemplo: 

INSERT INTO t1 VALUES (NOW());
INSERT INTO t2 VALUES (CURRENT_TIMESTAMP);

11.3.2. El tipo TIME

MySQL devuelve y muestra los valores en formato (o formato para valores de hora grandes). tiene rango de a . La razón por la que la parte de hora puede ser tan grande es que el tipo puede usarse no sólo para representar una hora del día (que debe ser menor a 24 horas), pero también el tiempo transcurrido o un intervalo de tiempo entre dos eventos (que puede ser mucho mayor a 24 horas, o incluso negativo).

Puede especificar valores en una variedad de formatos:

  • Como cadena de carácteres en formato . También puede usar una de las siguientes sintaxis “relajadas” : , , , , , , o . Aquí representa días y puede tener un valor de 0 a 34. Tenga en cuenta que MySQL no almacena la fracción (todavía).

  • Como cadena de carácteres sin delimitadores en formato , mientras que tenga sentido como hora. Por ejemplo, se entiende como , pero es ilegal (no tiene una parte de minutos correcta) y pasa a ser .

  • Como número en formato , mientras tenga sentido como hora. Por ejemplo, se entiende como . Los siguientes formatos alternativos también se entienden: , , , . Tenga en cuenta que MySQL no almacena la fracción (todavía).

  • Como resultado de una función que retorna un valor que es aceptable en un contexto , tal como .

Para valores especificados como cadenas de carácteres que incluyan un delimitador de las partes de hora, no es necesario especificar dos dígitos para horas, minutos o segundos que tengan un valor inferior a . es lo mismo que .

Tenga cuidado con asignar valores abreviados a una columna . Sin comas, MySQL interpreta los valores asumiendo que los dos dígitos más a la derecha representan segundos. (MySQL interpreta valores como tiempo transcurrido en lugar de horas del día.) Por ejemplo puede pensar que y significan (12 minutos tras las 11 en punto), pero MySQL los interpreta como (11 minutos, 12 segundos). Similarmente, y se interpretan como . Los valores con comas, por contrario, se tratan siempre como hora del día. Esto es, significa , no .

Los valores fuera del rango de pero que son legales se cambian por el valor límite de rango más cercano. Por ejemplo y se convierten en y .

Valores ilegales se convierten a . Tenga en cuenta que como es un valor legal, no hay forma de decir si un valor almacenado en una tabla, se insertó como o como valor ilegal.

11.3.3. El tipo de datos YEAR

El tipo es un tipo de un byte usado para representar años.

MySQL devuelve y muestra los valores en formato . El rango es de a .

Puede especificar los valores en una variedad de formatos:

  • Como cadena de carácteres de cuatro dígitos en el rango de a .

  • Como número de cuatro dígitos en el rango de a .

  • Como cadena de carácteres de dos dígitos en el rango de a . Los valores en los rangos de a y de a se convierten en valores en el rango de a y de a .

  • Como número de dos dígitos en el rango de a . Los valores en los rangos de a y de a se convierten en valores en los rangos de a y de a . Tenga en cuenta que el rango para números de dos dígitos es ligeramente distinto del rango para cadenas de carácteres de dos dígitos, ya que no especifica el cero directamente como número y tiene que ser interpretado como . Debe especificarlo como cadena de carácteres o o se interpreta como .

  • Como resultado de una función que retorne un valor que se acepte en un contexto , como .

Valores ilegales se convierten en .

11.3.4. Efecto 2000 (Y2K) y tipos de datos

MySQL no tiene problemas con el año 2000 (Y2K) (consulte Sección 1.4.5, “Conformidad con el efecto 2000”), pero los valores de entrada presentados a MySQL pueden tenerlos. Cualquier entrada con valores de años de dos dígitos es ambigua, ya que no se conoce el siglo. Tales valores deben interpretarse en forma de cuatro dígitos, ya que MySQL los almacena internamente usando cuatro dígitos.

Para tipos , , , y , MySQL interpreta las fechas con valores de año ambíguos usando las siguientes reglas:

  • Años en el rango se convierten a .

  • Años en el rango se convierten a .

Recuerde que estas reglas proporcionan sólo suposiciones razonables sobre lo que significan los valores. Si el heurístico usado por MySQL no produce los valores correctos, debe proporcionar entrada no ambígua con años de cuatro dígitos.

ordena valores o correctamente que tengan años de dos digitos.

Algunas funciones como y convierten un o a número. Esto significa que un valor con un año de dos dígitos no funciona correctamente con estas funciones. En este caso la solución es convertir o a formato de cuatro dígitos o usar algo como .

previous page start next page