Actualizar campo de fechas

Error común: La columna  es de timestamp sin zona horaria pero el sistema toma la expresión como de número entero

Cuando actualizas la columna recibes este mensaje de error:

SQL error: column "my_date" is of type timestamp without time zone but expression is of type integer

Esto se puede reproducir fácilmente con un conjunto de datos que contiene un campo de fecha y una consulta similar a esta:

insert into dataset_83830 ( test_name, my_date ) values ( 'test 1', 2012-1-23);

En la consulta anterior, estamos intentando insertar la fecha 23 de enero de 2012 en el campo my_date. Aquí podemos verlo reproducido en las Consultas Avanzadas:

Pero... ¿Por qué dice que la fecha es un número entero? Bueno, eso se debe a que Posgresql está resolviendo 2012-1-23 como una expresión numérica. Esto se puede ver con una simple declaración de selección en las consultas:

> select 2012-1-23 as mynumber;
> 1988

Viéndolo de esta forma en consultas avanzadas:

Para solucionar la consulta anterior, simplemente podemos agregar comillas simples alrededor de la fecha. Esto lo tratará como una cadena y activará los mecanismos internos de conversión de fecha de postgresql.

insert into dataset_83830 ( test_name, my_date ) values ( 'test 1', '2012-1-23');

Aquí vemos que podemos insertar el registro una vez que se aplica la corrección:

Toma en cuenta la Zona Horaria:

Actualmente estoy en la zona horaria del Pacífico, por lo que si inserto una fecha de '2012-1-23' en el campo de fecha y selecciono esa fecha, no recibo la fecha esperada. En su lugar, recibo la fecha '2012-1-22 16:00' como se ve aquí:

¿Por qué sucede esto?

Es porque el servidor está configurado en una zona horaria diferente a la zona horaria del Pacífico. Todos los servidores de AmigoCloud están configurados en la zona horaria UTC. Esto nos permite recopilar datos a nivel mundial y almacenarlos en una zona horaria común. Luego, cuando los datos se recuperan del servidor, se convierten a la zona horaria local. Esto quiere decir los datos en el campo son en realidad '2012-01-23 00:00:00', pero se han convertido a mi zona horaria local, la zona horaria del Pacífico, situada a 8 horas antes (UTC-8) que la zona horaria UTC. Así, para mí la fecha serán las 16:00 o las 4:00 p.m. el día antes. Normalmente, nuestro cliente web y los clientes móviles se encargarán de la conversión por usted, pero los puntos finales de SQL modifican directamente los datos y, por lo tanto, omiten la conversión de fecha.

¿Y cómo puedo resolver para indicar que estoy usando mi zona horaria?

La forma de solucionar este problema es usando siempre UTC  al emplear SQL para modificar datos de fecha. Hay un par de formas de hacer esto:

Solución N°1:  ajusta el tiempo a tu zona horaria:

Especifique una diferencia horaria que corresponda a tu zona horaria. Si actualmente nos ubicamos la zona horaria del Pacífico está 8 horas por detrás de la zona horaria UTC, simplemente puedo especificar 8:00 a.m. en lugar de dejarlo en blanco (ya que al dejar en blanco, el sistema lo asigna automáticamente al segundo 0 del día).

Esta operación me arrojará el resultado esperado:

Solución N°2 específica la zona horaria y conviértela en UTC

A veces, cuando inserta datos, no es deseable el ajuste debido a cambios en la zona horaria relacionados con el horario de verano y el horario estándar. A continuación, se muestra un ejemplo de cómo especificar una zona horaria y luego convertirla a UTC mientras se inserta:

insert into dataset_83830 ( test_name, my_date ) values ( 'test with time zone at time zone', '2012-1-23 00:00:00 PST8PDT' AT TIME ZONE 'UTC');

Esta segunda manera es mucho mas versátil, ya que la fecha se adaptará a los cambios en la zona horaria y los ajustes de la estación de año.

Aquí obtenemos la fecha esperada en nuestra zona horaria local (que es PST8PDT):

Algunos códigos de zonas horarios que pueden ser útiles: