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

timestamp php mysql (6)

¿Pero cómo guardo 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''); Aún mejor, guarde las fechas como un campo documentation . Eso siempre está almacenado en UTC, y UTC no sabe sobre el horario de verano / invierno.

Puede convertir de UTC a CONVERT_TZ 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.

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 guardo algo a 01:30:00 -04: 00 ? La documentation 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.

Creo que el web.ivy.net/~carton/rant/MySQL-timezones.txt de micahwittman tiene la mejor solución práctica para estas limitaciones de MySQL: establece la zona horaria de la sesión en UTC cuando te conectes:

SET SESSION time_zone = ''+0:00''

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

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 bucle for, comenzando con min_date , terminando con max_date , por 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 esta:

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

Así que no estoy mirando el ciclo al principio del día, sino 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í.

Este hilo me hizo flipar ya que usamos columnas TIMESTAMP 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 comporta correctamente y puede diferenciar entre las dos fechas similares convirtiendo TIMESTAMP a timestamp 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

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 se comportan 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 guardarlo en el campo convertir de la zona horaria de su sistema a cualquier zona no DST en la que quiera guardarlo (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

Los tipos de fecha de MySQL están, francamente, rotos y no pueden almacenarse todos los tiempos 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 se debe a que no puede almacenar ningún momento durante la hora anterior a la finalización 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 aún almacenará y devolverá (¡solo en forma de cadena!) Veces dentro de la hora "perdida" cuando comience el horario de verano (p. Ej. 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

Lo que se lleva: si necesita almacenar y recuperar cada vez en el año, tiene algunas opciones no deseadas:

Establezca la zona horaria del sistema en GMT + alguna compensación constante. Ej. UTC
Almacenar fechas como INT (como descubrió Aaron, TIMESTAMP ni siquiera es confiable)
Supongamos que el tipo DATETIME tiene una zona horaria de compensación constante. Por ejemplo, si está en America/New_York , convierta su fecha a GMT-5 fuera de MySQL , luego almacénela como DATETIME (esto resulta ser esencial: vea 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. Tanto 2009-11-01 01:30 -0400 y 2009-11-01 01:30 -0500 se almacenarán como el ambiguo 2009-11-01 01:30 .