insertar - ¿Cuál es la forma más recomendada de almacenar tiempo en PostgreSQL usando Java?

insertar fecha y hora actual en postgresql

Question

Estoy almacenando dos fechas en la base de datos PostgreSQL. En primer lugar, son los datos de visita de una página web, y la segunda fecha es la fecha de la última modificación de la página web (este es obtener como un largo).

Tengo algunas dudas sobre cuál es la mejor estrategia para almacenar estos valores.

Solo necesito día / mes / año y hora: segundos y esto solo será para proposiciones estadísticas.

Entonces, algunas dudas:

• ¿Es mejor almacenar tanto tiempo y convertir en recuperación de información o almacenar en el formato de datos anterior?
• es mejor establecer la fecha de visita en el software o en la inserción en la base de datos?
• en Java, ¿cómo son las mejores clases para manejar las fechas?

Answer 1

java.time

No es bonito, pero esto es lo que funcionó para mí con una instancia de ZonedDateTime utilizando el nuevo marco java.time en Java 8 y posterior ( Tutorial ):

ZonedDateTime receivedTimestamp = some_ts_value;
Timestamp ts = new Timestamp(receivedTimestamp.toInstant().toEpochMilli());
ps.setTimestamp(
   1, 
   ts, 
   Calendar.getInstance(TimeZone.getTimeZone(receivedTimestamp.getZone()))
); 

Answer 2

Cualquier estrategia para almacenar datos de fecha y hora en Postgresql debe basarse en estos dos puntos:

• Su solución nunca debe depender de la configuración de la zona horaria del servidor o del cliente.
• Actualmente, Postgresql (como la mayoría de las bases de datos) no tiene un tipo de datos para almacenar una fecha y hora completas con la zona horaria. LocalDateTime elegir (conceptualmente) entre un tipo de datos Instant o LocalDateTime .

Mi receta para cada escenario:

Si desea registrar el instante físico en el que ocurrió un evento en particular (por lo general, una creación / modificación / eliminación), (una verdadera " marca de tiempo "), utilice:

• Java: clase java.util.Date - o (mucho mejor) Java-8 (o Jodatime) Instant .
• JDBC: java.sql.Timestamp
• Postgresql: TIMESTAMP WITH TIMEZONE ( TIMESTAMPTZ )

(No se deje engañar con los nombres de tipos de datos de Postgresql: WITH TIMEZONE / WITH TIMEZONE WITHOUT TIMEZONE tienen significados muy peculiares, ninguno de ellos en realidad almacena una zona horaria)

Algunos códigos repetitivos: lo siguiente asume que ps es un estado PreparedStatement , rs un ResultSet y tzUTC es un objeto de Calendar estático correspondiente a la zona horaria UTC .

public static final Calendar tzUTC = Calendar.getInstance(TimeZone.getTimeZone("UTC"));  

Escribe al Instant en la base de datos TIMESTAMPTZ :

Instant instant = ...;
Timestamp ts = instant != null ? new Timestamp(instant.toEpochMilli()) : null;
ps.setTimestamp(col, ts, tzUTC);   // column is TIMESTAMPTZ!

Lea Instant desde la base de datos TIMESTAMPTZ :

Timestamp ts = rs.getTimestamp(col,tzUTC); // column is TIMESTAMPTZ
Instant inst = ts !=null ? Instant.ofEpochMilli(ts.getTime()) : null;

Esto funciona de forma segura si su tipo de PG es TIMESTAMPTZ (en ese caso, el calendarUTC no tiene ningún efecto en ese código, pero siempre es recomendable no depender de las zonas horarias predeterminadas). "Seguro" significa que el resultado no dependerá de la zona horaria del servidor o la base de datos , ni de la información de zonas horarias: la operación es totalmente reversible y pase lo que pase con la configuración de las zonas horarias, siempre obtendrá el mismo "instante" que originalmente Lado de Java

Si, en lugar de una marca de tiempo (un instante en la línea de tiempo física), tiene una fecha-hora local "civil" (es decir, el conjunto de campos {year-month-day hour:min:sec} ), debe usar

• Java (o Jodatime) LocalDateTime
• JDBC: java.sql.Timestamp
• Postgresql: TIMESTAMP WITHOUT TIMEZONE ( TIMESTAMP )

Lea LocalDateTime desde la base de datos TIMESTAMP :

Timestamp ts = rs.getTimestamp(col, tzUTC); //
LocalDateTime localDt = null;
if( ts != null ) 
    localDt =  LocalDateTime.ofInstant(Instant.ofEpochMilli(ts.getTime()), ZoneOffset.UTC);

Escriba LocalDateTime en la base de datos TIMESTAMP :

  Timestamp ts = null;
  if( localDt != null)    
      ts = new Timestamp(localDt.toInstant(ZoneOffset.UTC).toEpochMilli()), tzUTC);
  ps.setTimestamp(colNum,ts, tzUTC); 

De nuevo, esta estrategia es segura y puede dormir tranquilo: si almacenó 2011-10-30 23:59:30 , recuperará esos campos precisos (hora = 23, minuto = 59 ... etc.) siempre, sin importar que - incluso si mañana cambia la zona horaria de su servidor Postgresql (o cliente), o su JVM o su zona horaria de OS, o si su país modifica sus reglas de horario de verano, etc.

Agregado: Si desea (parece un requisito natural) almacenar la especificación completa de fecha y hora (un ZonedDatetime : la marca de tiempo junto con la zona horaria, que implícitamente también incluye la información civil completa de fecha y hora, más la zona horaria), no tiene suerte : Postgresql no tiene un tipo de datos para esto (ni otras bases de datos, que yo sepa). Debe diseñar su propio almacenamiento, tal vez en un par de campos: podrían ser los dos tipos anteriores (altamente redundantes, aunque eficientes para recuperación y cálculo), o uno de ellos más el tiempo compensado (pierde la información de la zona horaria, algunos cálculos se vuelven difícil, y algo imposible), o uno de ellos más la zona horaria (como cadena, algunos cálculos pueden ser extremadamente costosos).

Answer 3

Use java.util.Date en su aplicación Java y timestamp with time zone en su base de datos.