Pregunta Campos de fecha y hora de MySQL y horario de verano: ¿cómo hago referencia a la hora "extra"?


Estoy usando la zona horaria América / Nueva York. En el otoño, "retrocedemos" una hora, "ganando" efectivamente una hora a las 2 a. M. En el punto de transición sucede lo siguiente:

son las 01:59:00 -04: 00
luego 1 minuto después se convierte en:
01:00:00 -05: 00

Entonces, si simplemente dices "1:30 am", es ambiguo si te refieres o no a la primera vez que pasa la 1:30 o la segunda. Estoy tratando de guardar los datos de programación en una base de datos MySQL y no puedo determinar cómo guardar los tiempos correctamente.

Aquí está el problema:
"2009-11-01 00:30:00" se almacena internamente como 2009-11-01 00:30:00 -04: 00
"2009-11-01 01:30:00" se almacena internamente como 2009-11-01 01:30:00 -05: 00

Esto está bien y bastante esperado. Pero ¿cómo puedo guardar algo a 01:30:00 -04: 00? los documentación no muestra ningún soporte para especificar el desplazamiento y, en consecuencia, cuando he intentado especificar el desplazamiento, se ha ignorado debidamente.

Las únicas soluciones en las que he pensado consisten en configurar el servidor en una zona horaria que no utiliza el horario de verano y hacer las transformaciones necesarias en mis scripts (estoy usando PHP para esto). Pero eso no parece que sea necesario.

Muchas gracias por las sugerencias.


74
2017-10-29 19:55


origen


Respuestas:


Los tipos de fecha de MySQL están, francamente, rotos y no pueden almacenarse todas veces correctamente, a menos que su sistema esté configurado en una zona horaria de compensación constante, como UTC o GMT-5. (Estoy usando MySQL 5.0.45)

Esto es porque no puede almacenar ningún momento durante la hora anterior al final del horario de verano. No importa cómo ingrese las fechas, cada función de fecha tratará estos tiempos como si estuvieran durante la hora posterior al cambio.

La zona horaria de mi sistema es America/New_York. Intentemos almacenar 1257051600 (dom, 01 de noviembre de 2009 06:00:00 +0100).

Aquí está usando la sintaxis INTERVAL patentada:

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

Incluso FROM_UNIXTIME() no devolverá la hora exacta.

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

Por extraño que parezca, DATETIME será aún almacena y devuelve (solo en forma de cadena) veces dentro de la hora "perdida" cuando se inicia DST (p. 2009-03-08 02:59:59) Pero usar estas fechas en cualquier función de MySQL es arriesgado:

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

El punto de partida: si necesita almacenar y recuperar cada En el año, tienes algunas opciones indeseables:

  1. Establezca la zona horaria del sistema en GMT + alguna compensación constante. P.ej. UTC
  2. Almacenar fechas como INT (como descubrió Aaron, TIMESTAMP ni siquiera es confiable)

  3. Supongamos que el tipo DATETIME tiene una zona horaria de compensación constante. P.ej. Si estás en America/New_York, convierta su fecha a GMT-5 fuera de MySQL, luego almacenar como DATETIME (esto resulta ser esencial: ver la respuesta de Aaron). Luego debe tener mucho cuidado usando las funciones de fecha / hora de MySQL, porque algunos asumen que sus valores son de la zona horaria del sistema, otros (especialmente las funciones aritméticas de tiempo) son "agónicos de la zona horaria" (pueden comportarse como si los tiempos fueran UTC).

Aaron y yo sospechamos que las columnas TIMESTAMP de generación automática también están rotas. Ambos 2009-11-01 01:30 -0400 y 2009-11-01 01:30 -0500 se almacenará como el ambiguo 2009-11-01 01:30.


34
2017-10-30 15:13



Lo tengo resuelto para mis propósitos. Resumiré lo que aprendí (lo siento, estas notas son detalladas, son tanto para mi futura referencia como cualquier otra cosa).

Al contrario de lo que dije en uno de mis comentarios anteriores, los campos DATETIME y TIMESTAMP hacer comportarse de manera diferente. Los campos TIMESTAMP (como indican los documentos) toman todo lo que les envíe en formato "AAAA-MM-DD hh: mm: ss" y lo convierten de su zona horaria actual a hora UTC. El reverso ocurre de forma transparente cada vez que recupera los datos. Los campos DATETIME no hacen esta conversión. Toman lo que les envíes y lo almacenan directamente.

Ni los tipos de campo DATETIME ni TIMESTAMP pueden almacenar datos con precisión en una zona horaria que observa DST. Si almacena "2009-11-01 01:30:00", los campos no tienen forma de distinguir qué versión de la 1:30 a.m. que usted quería, la versión -04: 00 o -05: 00.

Ok, entonces debemos almacenar nuestros datos en una zona horaria no DST (como UTC). Los campos TIMESTAMP no pueden manejar estos datos con precisión por los motivos que explicaré: si su sistema está configurado en una zona horaria DST, entonces lo que ponga en TIMESTAMP puede no ser lo que obtiene de vuelta. Incluso si envía datos que ya ha convertido a UTC, seguirá asumiendo que los datos están en su zona horaria local y realizará otra conversión a UTC. Este viaje de ida y vuelta local-to-UTC-back-to-local realizado con TIMESTAMP es con pérdidas cuando su zona horaria local observa el horario de verano (ya que "2009-11-01 01:30:00" se asigna a 2 horarios posibles diferentes).

Con DATETIME puedes almacenar tus datos en cualquier zona horaria que desees y estar seguro de que recuperarás lo que envíes (no te verás obligado a realizar las conversiones de ida y vuelta con pérdidas que los campos TIMESTAMP te imponen). Entonces la solución es usar un campo DATETIME y antes de guardar en el campo Convierta desde la zona horaria de su sistema a la zona en la que no desea guardar el horario de verano (creo que UTC es probablemente la mejor opción). Esto le permite construir la lógica de conversión en su lenguaje de scripting para que pueda guardar explícitamente el equivalente UTC de "2009-11-01 01:30:00 -04: 00" o "" 2009-11-01 01:30: 00 -05: 00 ".

Otra cosa importante a tener en cuenta es que las funciones matemáticas de fecha / hora de MySQL no funcionan correctamente alrededor de los límites de DST si almacena sus fechas en un DST TZ. Entonces, hay más razones para guardar en UTC.

En resumen, ahora hago esto:

Al recuperar los datos de la base de datos:

Interprete explícitamente los datos de la base de datos como UTC fuera de MySQL para obtener una marca de tiempo de Unix precisa. Utilizo la función strtotime () de PHP o su clase DateTime para esto. No se puede realizar de manera confiable dentro de MySQL utilizando las funciones CONVERT_TZ () o UNIX_TIMESTAMP () de MySQL porque CONVERT_TZ solo generará un valor 'AAAA-MM-DD hh: mm: ss' que sufre problemas de ambigüedad, y UNIX_TIMESTAMP () asume su la entrada está en la zona horaria del sistema, no en la zona horaria en la que los datos se almacenaron REALMENTE (UTC).

Al almacenar los datos en la base de datos:

Convierta su fecha a la hora UTC precisa que desea fuera de MySQL. Por ejemplo: con la clase DateTime de PHP puede especificar "2009-11-01 1:30:00 EST" distintamente de "2009-11-01 1:30:00 EDT", luego conviértalo a UTC y guarde la hora UTC correcta a su campo DATETIME.

Uf. Muchas gracias por los aportes y la ayuda de todos. Con suerte, esto le ahorra a alguien más algunos dolores de cabeza en el futuro.

Por cierto, estoy viendo esto en MySQL 5.0.22 y 5.0.27


60
2017-10-30 16:30



Creo que micahwittman's enlazar tiene la mejor solución práctica para estas limitaciones de MySQL: establezca la zona horaria de la sesión en UTC cuando se conecte:

SET SESSION time_zone = '+0:00'

Luego, simplemente envíe las marcas de tiempo de Unix y todo estará bien.


10
2018-03-29 22:21



Pero ¿cómo puedo guardar algo a 01:30:00   -04: 00?

Puede convertir a UTC como:

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');


Mejor aún, guarde las fechas como un TIMESTAMP campo. Eso siempre está almacenado en UTC, y UTC no sabe sobre el horario de verano / invierno.

Puede convertir de UTC a localtime usando CONVERT_TZ:

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

Donde '+00: 00' es UTC, el de la zona horaria y 'SYSTEM' es la zona horaria local del sistema operativo donde se ejecuta MySQL.


3
2017-10-29 22:22



Este hilo me hizo flipar ya que usamos TIMESTAMP columnas con On UPDATE CURRENT_TIMESTAMP (es decir: recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) para rastrear registros cambiados y ETL a un datawarehouse.

En caso de que alguien se pregunte, en este caso, TIMESTAMP comportarse correctamente y puede diferenciar entre las dos fechas similares convirtiendo el TIMESTAMP a la marca de tiempo de Unix:

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810

2
2018-02-15 19:05



Estaba trabajando en los recuentos de registro de visitas de páginas y mostrando los recuentos en el gráfico (usando el plugin Flot jQuery). Llené la tabla con los datos de prueba y todo parecía estar bien, pero noté que al final del gráfico los puntos estaban un día libres según las etiquetas en el eje x. Después del examen, noté que el recuento de vistas para el día 2015-10-25 se recuperó dos veces de la base de datos y se pasó a Flot, por lo que todos los días después de esta fecha se movieron por un día a la derecha.
Después de buscar un error en mi código por un tiempo, me di cuenta de que esta fecha es cuando se realiza el DST. Luego llegué a esta página SO ...
... pero las soluciones sugeridas eran excesivas para lo que necesitaba o tenían otras desventajas. No estoy muy preocupado por no poder distinguir entre marcas de tiempo ambiguas. Solo necesito contar y mostrar registros por días.

Primero, recupero el rango de fechas:

SELECT 
    DATE(MIN(created_timestamp)) AS min_date, 
    DATE(MAX(created_timestamp)) AS max_date 
FROM page_display_log
WHERE item_id = :item_id

Luego, en un ciclo for, empezando por min_date, terminando con max_date, a paso de un día (60*60*24), Estoy recuperando los conteos:

for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
    $query = "
        SELECT COUNT(*) AS count_per_day
        FROM page_display_log
        WHERE 
            item_id = :item_id AND
            ( 
                created_timestamp BETWEEN 
                '" . date( "Y-m-d 00:00:00", $day ) . "' AND
                '" . date( "Y-m-d 23:59:59", $day ) . "'
            )
    ";
    //execute query and do stuff with the result
}

Mi solución final y rápida a mi problema fue esto:

$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....

Así que estoy sin mirar el ciclo al comienzo del día, pero dos horas después. El día sigue siendo el mismo y todavía estoy recuperando recuentos correctos, ya que solicito explícitamente a la base de datos los registros entre las 00:00:00 y las 23:59:59 del día, independientemente de la hora real de la marca de tiempo. Y cuando el tiempo salta por una hora, todavía estoy en el día correcto.

Nota: Sé que este es el hilo de 5 años, y sé que esta no es una respuesta a la pregunta de OP, pero podría ayudar a las personas como yo que encontraron esta página en busca de solución al problema que describí.


0
2017-07-08 21:58