java - unrecognized - mysql jdbc server timezone

Recuperando el campo UTC DATETIME de MySQL en Java cuando la zona horaria del servidor no es UTC

Question

Intento escribir código para interoperar con una base de datos desarrollada por un tercero usando Java y MySQL. Esta base de datos tiene un campo que almacena una marca de tiempo en un campo DATETIME como una fecha UTC. La zona horaria del servidor en el que se ejecutan tanto la base de datos como el cliente está configurada en una zona no UTC ( Europe/London ), por lo que de manera predeterminada, la marca de tiempo se lee incorrectamente como si fuera una hora local. Intento escribir un código para volver a leerlo como UTC.

He leído varias preguntas similares aquí, pero ninguna de ellas tiene una respuesta que me funcione:

• MySQL: ¿cómo almacenar el tiempo con la zona horaria correcta? (de Java)
• ¿Cómo almacenar un java.util.Date en un campo de marca de tiempo MySQL en la zona horaria UTC / GMT?
• Fecha en UTC en mysql
• ¿Cómo configuro la zona horaria de MySQL?

Desafortunadamente, no puedo cambiar la configuración del servidor, así que he intentado usar la variable "time_zone" de la conexión para configurar el servidor de base de datos para usar UTC y el parámetro Calendar opcional para ResultSet.getTimestamp para recuperar la fecha, pero esto no tiene efecto en el resultado . Aquí está mi código:

private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset.UTC));
public Date getDate ()
{
    try (Connection c = dataSource.getConnection ();
         PreparedStatement s = c
             .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))
    {
        fixTimeZone (c);
        s.setLong (1, getPhysId ());
        try (ResultSet rs = s.executeQuery ())
        {
            if (!rs.next ()) return null;
            return new Date (rs.getTimestamp(1,UTCCALENDAR).getTime ());    // do not use SQL timestamp object, as it fucks up comparisons!
        }
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Error accessing dbmail database", e);
    }
}

private void fixTimeZone (Connection c)
{
    try (Statement s = c.createStatement ())
    {
        s.executeUpdate ("set time_zone=''+00:00''");
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Unable to set SQL connection time zone to UTC", e);
    }
}

El campo de la base de datos que estoy tratando de leer tiene un valor almacenado en él de la siguiente manera:

mysql> select * from dbmail_datefield where physmessage_id=494539;
+----------------+--------+---------------------+
| physmessage_id | id     | datefield           |
+----------------+--------+---------------------+
|         494539 | 494520 | 2015-04-16 10:30:30 |
+----------------+--------+---------------------+

Pero desafortunadamente, el resultado aparece como BST no UTC:

java.lang.AssertionError: expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>

Answer 1

El código getDate() su cliente parece correcto hasta donde llega. Creo que también necesita obtener el controlador MySQL Connector / J JDBC para tratar las fechas almacenadas en la tabla como fechas UTC, para evitar una conversión de zona horaria espuria. Esto significa configurar el huso horario efectivo del servidor, además de la zona horaria de la sesión del cliente y el calendario utilizado para las llamadas JDBC getTimestamp mientras lo hace.

Eche un vistazo a los valores que obtuvo en su afirmación fallida y en qué dirección se encuentra el error:

expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>

Lo que obtuviste fue a las 10:30 BST, que es a las 9:30 GMT. Esto es coherente con la base de datos tratando ese 10:30 en la tabla como un valor BST y convirtiéndolo espurosamente en GMT para usted, antes de analizarlo como una fecha GMT. Esa es la dirección opuesta a un valor GMT que se convierte falsamente en BST.

Esto puede ser un problema específico de JDBC, porque JDBC requiere que los tiempos de tiempo se conviertan a la zona local. (Donde la API C de MySQL no lo hace, probablemente porque los tipos de tiempo clásicos de C no son conscientes de la zona como lo son los de Java). Y también necesita saber de qué zona se está convirtiendo. El tipo MySQL TIMESTAMP siempre se almacena como UTC. Pero eso no está establecido para el tipo DATETIME . Creo que eso implica que MySQL interpretará los valores de la columna DATETIME como si estuvieran en la zona horaria del servidor. Lo que mencionaste como configurado en BST, y eso es consistente con la dirección del cambio que se muestra en tu mensaje de error de aserción.

La time_zone sesión time_zone que estableces le dice al servidor MySQL cuál es la zona horaria de tu máquina cliente, pero no afecta lo que el servidor cree que es su propia zona horaria. Eso puede ser anulado con la propiedad de conexión serverTimezone JDBC . En su conexión, configure serverTimezone en UTC y asegúrese de que useLegacyDatetimeCode esté desactivado. (Y revise las otras propiedades relacionadas con la zona si eso no funciona). Vea si eso hace que sus fechas aparezcan como UTC con los mismos valores de campo de calendario que en la base de datos.

Tenga en cuenta que esto va a cambiar la interpretación de otros valores DATETIME en su base de datos: ahora todos se verán como fechas UTC (en el contexto de su conexión JDBC). Si eso es correcto va a depender de cómo se poblaron inicialmente. Si bien su código de cliente tendrá el comportamiento que desea, no sé si se puede hacer que este sistema en su conjunto se comporte de manera totalmente consistente sin establecer la zona horaria del servidor en UTC en el nivel del servidor. Básicamente, si no tiene su zona configurada en UTC, no está completamente configurada para el comportamiento que desea, y está haciendo kludging alrededor de ella.

Answer 2

La mejor opción, en mi opinión, es decirle a MySQL que use GMT y que maneje todos los problemas de tiempo local en el código de su aplicación, no en su base de datos. Los valores en la base de datos siempre serían GMT, punto final, que no es ambiguo. Como dices, con los ajustes del horario de verano (horario de verano), puedes terminar con el mismo valor en tu base de datos para lo que para nosotros, los humanos, es dos veces diferentes.

Esto también hace que la base de datos sea portátil. Si se muda a América del Norte y comienza a utilizar MySQL configurado para (por ejemplo) el horario central, de repente los valores en su base de datos parecen haberse movido varias horas. Tuve ese problema con una base de datos que heredé que usaba la hora local del servidor, cuando la moví de la costa este de los EE. UU. A la costa oeste, sin haber pensado verificar si MySQL estaba conectada a la zona de la máquina ...

long t = 1351382400000; // the timestamp in UTC
String insert = "INSERT INTO my_table (timestamp) VALUES (?)";
PreparedStatement stmt = db.prepareStatement(insert);
java.sql.Timestamp date = new Timestamp(t);
stmt.setTimestamp(1, date);
stmt.executeUpdate();

.....

TimeZone timezone = TimeZone.getTimeZone("MyTimeZoneId");
Calendar cal = java.util.Calendar.getInstance(timezone);
String select = "SELECT timestamp FROM my_table";
// some code omitted....
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
   java.sql.Timestamp ts = rs.getTimestamp(1);
   cal.setTimeInMillis(ts.getTime());
   System.out.println("date in db: " + cal.getTime());
}

Answer 3

No piense en convertir o adaptar el huso horario. No piense en el TZ que usa el mysql para almacenar sus marcas de tiempo o cualquier cosa así. Esas cosas ya están manejadas. Hay tres cosas que debe manejar: INPUT, OUTPUT y errores.

ENTRADA

Cuando un usuario ingresa una fecha (en un formulario) sin una zona horaria explícita, debe saber qué TZ quiso utilizar. Puede usar un objeto SimpleDateFormat con la zona horaria configurada para resolver esto. No tiene que convertir la fecha de entrada, debe ''interpretarla'' correctamente. Una vez que tiene una Fecha o indicación de tiempo correctamente interpretada, ha terminado con la entrada.

La entrada no es solo la entrada del usuario, también incluye archivos de configuración.

SALIDA

Lo mismo aquí. Olvídese de lo que TZ tiene sus objetos Date y timestamps no tiene ninguno, solo son milisegundos desde época. Tienes que formatear tus fechas en la TZ que el usuario espera para que él las entienda.

Loco

¡Puede que haya errores en su código relacionados con TZ, pero las bibliotecas también pueden tenerlos!

Noté que el controlador de mysql java no pudo comunicar la zona horaria del cliente al servidor. Este comando s.executeUpdate ("set time_zone=''+xx:yy''"); es la solución, pero la está utilizando mal. Debe indicarle al servidor el TZ que está usando el cliente, antes de insertarlo y consultarlo. La variable se almacena en la sesión. Tal vez puedas automatizarlo en la configuración de tu grupo de conexiones. Esto es necesario para que el servidor sepa qué TZ necesita usar el cliente para leer o escribir. Esto no depende del servidor TZ . No significa "almacenar esta fecha en UTC", significa "la fecha que te doy es UTC" y "envíame conjuntos de resultados en UTC". No importa si está usando la clase Date con su TZ interno, el controlador lo estropea, necesitará establecer esa variable de sesión.

De forma predeterminada, asume que el cliente TZ es el mismo que el servidor TZ, por lo que no debería preocuparse porque dijo que son lo mismo.

Answer 4

Si desea utilizar la zona horaria, puede leer la columna como UTC.

ZonedDateTime zdt = ZonedDateTime.of(rs.getTimestamp(1).toLocalDateTime(), ZoneOffset.UTC);

Luego puedes cambiar a la zona horaria que quieras usando:

zdt = zdt.withZoneSameInstant(ZoneId.of(
            TARGET_ZONE));

Si solo desea leer la fecha y no le interesan las zonas en absoluto, utilice solo:

LocalDateTime ldt = rs.getTimestamp(1).toLocalDateTime()

Obtendrá LocalDateTime sin zona horaria.

Si tiene que devolver el uso de java.util.Date:

Date.from(ldt.atZone(ZoneOffset.UTC).toInstant());

Answer 5

Tal vez puedas usar JodaTime de la siguiente manera;

private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset    .UTC));
public Date getDate ()
{
    try (Connection c = dataSource.getConnection ();
         PreparedStatement s = c
             .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))
    {
        s.setLong (1, getPhysId ());
        try (ResultSet rs = s.executeQuery ())
        {
            if (!rs.next ()) return null;
            DateTime dt = new LocalDateTime(rs.getTimestamp(1,UTCCALENDAR).getTime ()).toDateTime(DateTimeZone.forID("Europe/London"));  

            return dt.toDate();               }
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Error accessing dbmail database", e);
    }
}

EDITAR:

java.util.Date no es TimeZone agnostic. El método toDateTime se ocupa de TimeZone y DST para que no te importe

El siguiente código:

public static void main(String[] args) {
    // 29/March/2015 1:05 UTC
    DateTime now = new DateTime(2015, 3,29,1,5,DateTimeZone.UTC);
    // Pre DST 29/March/2015 0:30 UTC
    DateTime preDst = new DateTime(2015, 3,29,0,30,DateTimeZone.UTC);
    System.out.println("1:05 UTC:"+now);
    System.out.println("0:30 UTC:"+preDst);
    DateTimeZone europeDTZ = DateTimeZone.forID("Europe/London");
    DateTime europeLondon = now.toDateTime(europeDTZ);
    System.out.println("1:05 UTC as Europe/London:"+europeLondon);
    DateTime europeLondonPreDst = preDst.toDateTime(europeDTZ);
    System.out.println("0:30 UTC as Europe/London:"+europeLondonPreDst);
}

Se imprimirá:

1:05 UTC:2015-03-29T01:05:00.000Z
0:30 UTC:2015-03-29T00:30:00.000Z
1:05 UTC as Europe/London:2015-03-29T02:05:00.000+01:00
0:30 UTC as Europe/London:2015-03-29T00:30:00.000Z

Si puede ver que JodaTime se ocupa de DST.