Usar la hora actual UTC como predeterminada en PostgreSQL

1. Método estándar para obtener la hora UTC en PostgreSQL
2. Crear tablas con la columna TIMESTAMP en formato UTC
3. Utilice TIMEZONE() para obtener HORA en formato UTC para la columna de la tabla en PostgreSQL
4. Envuelva el formato de hora UTC en una función para una mejor eficiencia

Hoy, en PostgreSQL, aprenderemos cómo usar la hora actual UTC como la hora predeterminada en nuestras tablas en PostgreSQL. UTC significa HORA UNIVERSAL COORDINADA.

Es el método UNIVERSAL de regulación de relojes y tiempo en todo el mundo. Las zonas horarias locales tienen el tiempo especificado solo para una determinada ubicación y se utilizan principalmente para comprender mejor a los plebeyos.

Veremos las diferentes soluciones para configurar la hora actual en nuestro servidor PostgreSQL como UTC.

Método estándar para obtener la hora UTC en PostgreSQL

Podemos llamar a una consulta simple para obtener la hora en formato UTC para nuestra sesión actual de PostgreSQL.

select now() at time zone ('utc');

Esto nos devolverá una tabla de la siguiente manera.

Producción :

timezone (timestamp without timezone)
"2022-04-27 16:38:29.354155"

Entonces, ¿cómo funciona esto? Primero, llamamos a NOW() para obtener la hora de inicio de la transacción actual.

Se obtiene a partir de la ZONA HORARIA definida en formato UTC. Entonces esta simple consulta nos ayuda a obtener la hora actual en la zona que requerimos.

Si tiende a llamar a esta consulta sin definir la cláusula TIMEZONE, notará que su sesión devolverá un valor de tiempo especificado GMT de la siguiente manera.

select now();

Producción :

timezone (timestamp with timezone)
2022-04-27 21:55:14.098115+05

Por lo tanto, es esencial especificar UTC al obtener el valor TIME. En muchos casos, al usar TIMESTAMP, los valores de TIME del sistema se almacenarán en UTC, de lo contrario, se especificarán de manera diferente.

Además, puede haber una conversión implícita ya sea de fechas locales a UTC o viceversa.

Crear tablas con la columna TIMESTAMP en formato UTC

Una forma popular de INSERTAR datos en una tabla donde la información del registro debe contener la hora válida es agregar un controlador de tiempo automático, una columna que constará de la FECHA HORA actual actualizada y la almacenará una vez que el registro esté INSERTADO. .

Podemos utilizar TIMESTAMP SIN ZONA HORARIA, un alias de UTC, para especificar una columna que contenga valores UTC en INSERCIÓN. Para hacer esto, escribamos una consulta de la siguiente manera.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default (now() at time zone('utc')), rec int);

Ejecutar la consulta anterior creará una tabla temporal que contiene una columna ESTAMP DE TIEMPO PREDETERMINADO. Cuando llamamos a INSERT en la tabla anterior, cada registro insertado contendrá un TIMESTAMP automático generado en UTC y adjunto.

Ejecutando una consulta de la siguiente manera.

insert into DELFTSTACK_TUT (rec) VALUES(1), (2), (3);

Nos devolverá una tabla como sigue en la salida.

timestamp without time zone    rec
"2022-04-27 17:13:31.159356"	1
"2022-04-27 17:13:31.159356"	2
"2022-04-27 17:13:31.159356"	3

Por lo tanto, podemos ver que usar la expresión NOW() con UTC en la cláusula DEFAULT funciona perfectamente para hacer tablas donde se necesita llevar un registro.

Asegúrese de no perder los corchetes alrededor de la cláusula DEFAULT, ya que tiene que tomar la expresión completa como un solo elemento y luego usarla para completar los valores en los registros respectivos.

Puede encontrarse con diferentes problemas en diferentes versiones de PostgreSQL. En ocasiones, puede haber un error al definir la ZONA HORARIA.

Escribir UTC en lugar de UTC puede causar problemas debido a diferentes CASE. Por lo tanto, es importante estar atento a las EXPRESIONES SENSIBLES A LAS MAYÚSCULAS y las RESTRICCIONES al escribir tales expresiones en PostgreSQL.

Utilice TIMEZONE() para obtener HORA en formato UTC para la columna de la tabla en PostgreSQL

Otra alternativa sencilla para obtener el valor de TIME DATE en formato UTC para nuestra columna es llamar a una consulta.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default ('utc', now()), rec int);

Pero esto devolverá un error de la siguiente manera en CREANDO.

Producción :

ERROR:  column "time_stamp" is of type timestamp without time zone but default expression is of type record
HINT:  You will need to rewrite or cast the expression.
SQL state: 42804

El error se debe a la falta de coincidencia de tipos. Puede CAST o reescribir la expresión anterior para eliminar este error.

Sin embargo, “ENVIAR” nuestra consulta de dos maneras diferentes para que lo entiendas mejor. El primero de abajo convierte este TIMESTAMP en otro TIMEZONE provisto del parámetro UTC.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone('utc', now()), rec int);

Esto nos proporcionará una tabla de la siguiente manera.

Producción :

timestamp without time zone    rec
"2022-04-27 17:45:34.257072"	1
"2022-04-27 17:45:34.257072"	2
"2022-04-27 17:45:34.257072"	3

Y nuestra segunda solución, CASTS, esta expresión en TEXT y luego tomar la salida, en caso de que haya variaciones entre las diferentes versiones de PostgreSQL.

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default now()::timestamp, rec int);

Ejecutar lo anterior y luego INSERTAR 3 registros en la tabla nos proporcionará el siguiente resultado.

timestamp without time zone    rec
"2022-04-27 22:49:51.654846"	1
"2022-04-27 22:49:51.654846"	2
"2022-04-27 22:49:51.654846"	3

Por lo tanto, puede notar que convertir nuestro NOW() en un TIMESTAMP y luego insertarlo en una columna especificada UTC nos da los resultados correctos. Sin embargo, esta última solución no considera las compensaciones UTC y solo mantiene una marca de tiempo ingenua como registro.

Utilice INTERVALO -8:00 en lugar de UTC

Puede escribir su consulta de la siguiente manera si no quiere quedarse atascado en los problemas de CASO de escribir UTC en su sesión.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone(INTERVAL '+00:00', now()), rec int);

Ejecutar esta consulta, hacer la tabla y llamar a INSERTAR devolverá los resultados correctos en formato UTC. INTERVALO con +00:00 se define en la documentación de PostgreSQL como perteneciente a la zona horaria UTC o ZULU (abreviatura militar) con un desplazamiento de +00.00.

Se generará un resultado de la siguiente manera.

 timestamp without time zone   rec
"2022-04-27 17:57:36.054746"	1
"2022-04-27 17:57:36.054746"	2
"2022-04-27 17:57:36.054746"	3

Envuelva el formato de hora UTC en una función para una mejor eficiencia

Podemos crear una función simple para obtener la hora actual en UTC y luego usarla con nuestro registro INSERCIONES.

create function get_UTC_time()
returns timestamp as
$$
  select now() at time zone 'utc';
$$
language sql;

Y luego llame a la consulta SELECCIONAR para obtener la hora actual de la siguiente manera.

select * from get_UTC_time();

Esto generará el siguiente resultado.

timestamp without time zone
"2022-04-27 18:01:35.42119"

Además, podemos usar esto dentro de nuestra tabla como:

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default get_UTC_time());

Por lo tanto, hemos analizado las diversas formas de obtener la hora en formato UTC para nuestras sesiones de PostgreSQL. Asegúrese de modificarlos de acuerdo con sus instalaciones o versiones de PostgreSQL.

Sin embargo, hacemos todo lo posible para cubrir cada consulta y sus condiciones y limitaciones con el mayor detalle posible.