sql - without - Obtenga "hora con zona horaria" de "hora sin zona horaria" y el nombre de la zona horaria

time zone postgresql

Question

En primer lugar, me doy cuenta de que el time with time zone no es recomendable. Voy a usarlo porque estoy comparando varias time with time zone valores de time with time zone actual de mi sistema, independientemente del día. Es decir, un usuario dice comenzar todos los días a las 08:00 y terminar a las 12:00 con su zona horaria, no la zona horaria del sistema. Entonces, tengo un time without time zone columna de time without time zone en una tabla, llamémoslo SCHEDULES.time y tengo una columna de nombre de zona horaria UNIX en otra tabla, llamémoslo USERS.tz .

La zona horaria de mi sistema es ''America/Regina'' , que no usa DST, por lo que el desplazamiento siempre es -06 .

Dado un tiempo de ''12: 00: 00 ''y un tz de'' América / Vancouver '', me gustaría seleccionar los datos en una columna de tipo time with time zone pero NO deseo convertir el tiempo en mi zona horaria porque el usuario efectivamente ha dicho comenzar a las 12:00 en Vancouver, no en Regina.

Por lo tanto, haciendo:

SELECT SCHEDULES.time AT TIME ZONE USERS.tz
FROM SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID;

resultados (en este momento) en:

''10:00:00-08''

pero realmente quiero

''12:00:00-08''

No puedo encontrar ninguna documentación relacionada con la aplicación de una zona horaria a una hora, a excepción de AT TIME ZONE . ¿Hay alguna manera de lograr esto sin la manipulación del personaje u otros hacks?

ACTUALIZACIÓN: Esto se puede lograr mediante el uso de concatenación de cadenas, fundición y la zona horaria de Postgres como tal:

select (''12:00:00''::text || utc_offset::text)::timetz
from pg_timezone_names
where name = ''America/Vancouver'';

Sin embargo, esto es bastante lento. Debe haber una mejor manera, ¿no?

ACTUALIZACIÓN 2: me disculpo por la confusión. La tabla SCHEDULES NO utiliza el time with time zone . Estoy intentando SELECCIONAR una time with time zone combinando valores de una time without time zone y un nombre de zona horaria de text .

ACTUALIZACIÓN 3: Gracias a todos los involucrados en su debate (acalorado). :) Me convencieron de abandonar mi plan para usar un time with time zone para mi salida y usar una timestamp with time zone ya que funciona bien, es más legible y resuelve otro problema con el que me iba a encontrar, el tiempo zonas que entran en nuevas fechas. ES DECIR. ''2011-11-21 23:59'' en ''América / Vancouver'' es ''2011-11-22'' en ''América / Regina''.

ACTUALIZACIÓN 4: Como dije en mi última actualización, he elegido la respuesta que @ MichaelKrelin-hacker propuso por primera vez y @JonSkeet finalizó. Es decir, una timestamp with time zone ya que mi resultado final es una mejor solución. Terminé usando una consulta como:

SELECT timezone(USERS.tz, now()::date + SCHEDULES.time)
FROM SCHEDULES
JOIN USERS ON USERS.ID = SCHEDULES.USERID;

El formato timezone() fue reescrito por Postgres después de que (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz en mi vista.

Answer 1

ADVERTENCIA: Novato de PostgreSQL (ver comentarios sobre la pregunta!). Sin embargo, sé un poco sobre las zonas horarias, así que sé lo que tiene sentido preguntar.

Me parece que esto es básicamente una situación no soportada (desafortunadamente) cuando se trata de AT TIME ZONE . Al observar la documentación de AT TIME ZONE , se proporciona una tabla donde los tipos de valores de "entrada" son solo:

• marca de tiempo sin zona horaria
• marca de tiempo con zona horaria
• hora con zona horaria

Nos falta el que desea: tiempo sin zona horaria. Lo que está preguntando es algo lógico, aunque depende de la fecha ... ya que las diferentes zonas horarias pueden tener diferentes compensaciones dependiendo de la fecha. Por ejemplo, 12:00:00 Europe / London puede significar 12:00:00 UTC, o puede significar 11:00:00 UTC, dependiendo de si es invierno o verano.

En mi sistema, habiendo configurado el huso horario del sistema en América / Regina, la consulta

SELECT (''2011-11-22T12:00:00''::TIMESTAMP WITHOUT TIME ZONE) 
                               AT TIME ZONE ''America/Vancouver''

me da 2011-11-22 14:00:00-06 como resultado. Eso no es ideal , pero al menos da el punto instantáneo en el tiempo (creo). Creo que si buscara eso en la biblioteca de un cliente, o lo comparara con otro TIMESTAMP WITH TIME ZONE , obtendría el resultado correcto. Es solo la conversión de texto que luego usa la zona horaria del sistema para la salida.

¿Sería eso lo suficientemente bueno para ti? ¿Puede cambiar su campo SCHEDULES.time para que sea un campo TIMESTAMP WITHOUT TIME ZONE , o (en tiempo de consulta) combinar el tiempo del campo con una fecha para crear una marca de tiempo sin zona horaria?

EDITAR: Si está satisfecho con la "fecha actual", parece que puede cambiar su consulta a:

SELECT (current_date + SCHEDULES.time) AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

Por supuesto, la fecha del sistema actual puede no ser la misma que la fecha actual en la zona horaria local . Creo que esto arreglará esa parte ...

SELECT ((current_timestamp AT TIME ZONE USERS.tz)::DATE + schedules.time)
       AT TIME ZONE USERS.tz
from SCHEDULES JOIN USERS on USERS.ID=SCHEDULES.USERID

En otras palabras:

• Toma el instante actual
• Calcula la fecha / hora local en la zona horaria del usuario
• Toma la fecha de eso
• Agregue la hora del horario a esa fecha para obtener un TIMESTAMP WITHOUT TIME ZONE
• Use AT TIME ZONE para aplicar la zona horaria a esa fecha / hora local

Estoy seguro de que hay una mejor manera, pero creo que tiene sentido.

Debe tener en cuenta que en algunos casos esto podría fallar:

• ¿Qué quiere que sea el resultado para un tiempo de 01:30 en un día en que el reloj salta de 01:00 a 02:00, por lo que 01:30 no ocurre en absoluto?
• ¿Qué quiere que sea el resultado para un tiempo de 01:30 en un día en que el reloj vuelve de las 02:00 a la 01:00, por lo que 01:30 ocurre dos veces?

Answer 2

Aquí hay una demostración de cómo calcular los tiempos sin conversión al texto:

CREATE TEMP TABLE schedule(t time, tz text);
INSERT INTO schedule values
 (''12:00:00'', ''America/Vancouver'')
,(''12:00:00'', ''US/Mountain'')
,(''12:00:00'', ''America/Regina'');

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        + EXTRACT (timezone from now()) * interval ''1s''
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

Básicamente, debe restar el desplazamiento UTC y agregar el desplazamiento de su zona horaria local para llegar a la zona horaria determinada.

Puede acelerar el cálculo codificando su compensación local. En su caso (América / Regina) debería ser:

SELECT s.t AT TIME ZONE s.tz
        - p.utc_offset
        - interval ''6h''
FROM   schedule s
JOIN   pg_timezone_names p ON s.tz = p.name;

Como pg_timezone_names es una vista y no una tabla del sistema, es bastante lenta, al igual que la variante demostrada con conversión de casting a texto y viceversa.

pg_timezone_names abreviaturas de zona horaria y tomaría el doble molde a través del text sin unirme en pg_timezone_names para un rendimiento óptimo.

Solución FAST

El culpable que le está ralentizando es pg_timezone_names . Después de algunas pruebas, descubrí que pg_timezone_abbrevs es muy superior. Por supuesto, debe guardar las abreviaturas de zona horaria correctas en lugar de los nombres de zona horaria para lograr esto. Los nombres de zona horaria tienen en cuenta el horario de verano automáticamente, las abreviaturas de zona horaria son básicamente códigos para una compensación de tiempo. La documentación:

Una abreviatura de zona horaria, por ejemplo PST . Dicha especificación simplemente define una compensación particular de UTC, a diferencia de los nombres de zona horaria completa que también pueden implicar un conjunto de reglas de fecha de transición de ahorro de luz solar.

Eche un vistazo a los resultados de esta prueba o inténtelo usted mismo:

SELECT * FROM  pg_timezone_names;

Tiempo de ejecución total: 541.007 ms

SELECT * FROM pg_timezone_abbrevs;

Tiempo de ejecución total: 0.523 ms

Factor 1000 Ya sea que vaya con su idea para timetz a text y de vuelta a timetz o con mi método para calcular el tiempo no es importante. Ambos métodos son muy rápidos. Simplemente no use pg_timezone_names .

En realidad, tan pronto como guarde las abreviaturas de zona horaria, puede tomar la ruta de lanzamiento sin ninguna unión adicional. Use la abreviatura en lugar de utc_offset. Los resultados son precisos según su definición.

CREATE TEMP TABLE schedule(t time, abbrev text);
INSERT INTO schedule values
 (''12:00:00'', ''PST'')  -- ''America/Vancouver''
,(''12:00:00'', ''MST'')  -- ''US/Mountain''
,(''12:00:00'', ''CST''); -- ''America/Regina''

-- calculating
SELECT s.t AT TIME ZONE s.abbrev
     - a.utc_offset
     + EXTRACT (timezone from now()) * interval ''1s''
FROM   schedule s
JOIN   pg_timezone_abbrevs a USING (abbrev);

-- casting (even faster!)
SELECT (t::text || abbrev)::timetz
FROM   schedule s;