month - Fecha de PostgreSQL() con zona horaria
postgresql date format (3)
Tengo un problema al seleccionar las fechas correctamente en Postgres: se almacenan en UTC, pero no se convierten correctamente con la función Date ().
La conversión de la marca de tiempo a una fecha me da la fecha incorrecta si es más allá de las 4 p.m. PST.
2012-06-21 debería ser 2012-06-20 en este caso.
El tipo de datos de columna starts_at es timestamp without time zone . Aquí están mis consultas:
Sin convertir a la zona horaria PST:
Select starts_at from schedules where id = 40;
starts_at
---------------------
2012-06-21 01:00:00
La conversión da esto:
Select (starts_at at time zone ''pst'') from schedules where id = 40;
timezone
------------------------
2012-06-21 02:00:00-07
Pero tampoco convertir a la fecha correcta en la zona horaria.
Básicamente lo que quieres es:
$ select starts_at AT TIME ZONE ''UTC'' AT TIME ZONE ''US/Pacific'' from schedules where id = 40
Obtuve la solución de este artículo está abajo, que es ORO directo !!! Explica este tema no trivial con mucha claridad, préstele una lectura si desea comprender mejor la gestión de Pstgrsql TZ.
Expresando marcas de tiempo de PostgreSQL sin zonas en hora local
Aquí es donde está pasando. En primer lugar, debe saber que ''la zona horaria PST está 8 horas atrás de la zona horaria UTC, por ejemplo, el 1 de enero de 2014, 4:30 PM PST (miércoles, 01 ene 2014 16:00:30 -0800) es equivalente al 2 de enero de 2014, 00:30 AM UTC (Jue, 02 Ene 2014 00:00:30 +0000). En cualquier momento después de las 4:00 p.m. en PST se pasa al día siguiente, interpretado como UTC.
Además, como Erwin Brandstetter mencionado anteriormente, postresql tiene dos tipos de tipos de datos de fecha y hora, uno con una zona horaria y otro sin ella. Si sus marcas de tiempo incluyen una zona horaria, entonces un simple:
$ select starts_at AT TIME ZONE ''US/Pacific'' from schedules where id = 40
trabajará. Sin embargo, si su marca de tiempo es timezoneless, ejecutar el comando anterior no funcionará, y PRIMERO debe convertir su marca de tiempo timezoneless a una marca de tiempo con una zona horaria, es decir, una zona horaria UTC, y SÓLO ENTONCES convertirla a su ''PST'' o ''US /'' deseado Pacific ''(que son lo mismo hasta algunos problemas de horario de verano. Creo que debería estar bien con cualquiera de los dos).
Permítanme demostrar con un ejemplo donde creo una marca de tiempo timezoneless. Supongamos por conveniencia que nuestra zona horaria local es de hecho "PST" (si no fuera así, se vuelve un poco más complicado, lo cual es innecesario a los fines de esta explicación).
Digamos que tengo:
$ select timestamp ''2014-01-2 00:30:00'' AS a, timestamp ''2014-01-2 00:30:00'' AT TIME ZONE ''UTC'' AS b, timestamp ''2014-01-2 00:30:00'' AT TIME ZONE ''UTC'' AT TIME ZONE ''PST'' AS c, timestamp ''2014-01-2 00:30:00'' AT TIME ZONE ''PST'' AS d
Esto producirá:
"a"=>"2014-01-02 00:30:00" (This is the timezoneless timestamp)
"b"=>"2014-01-02 00:30:00+00" (This is the UTC TZ timestamp, note that up to a timezone, it is equivalent to the timezoneless one)
"c"=>"2014-01-01 16:30:00" (This is the correct ''PST'' TZ conversion of the UTC timezone, if you read the documentation postgresql will not print the actual TZ for this conversion)
"d"=>"2014-01-02 08:30:00+00"
La última marca de tiempo es la razón de toda la confusión relacionada con la conversión de la marca de tiempo timezoneless de UTC a ''PST'' en postgresql. Cuando escribimos:
timestamp ''2014-01-2 00:30:00'' AT TIME ZONE ''PST'' AS d
Estamos tomando una marca de tiempo timezoneless e intentamos convertirla a ''PST TZ (suponemos indirectamente que postgresql entenderá que queremos que convierta la marca de tiempo de una UTC TZ, ¡pero postresql tiene sus propios planes!). En la práctica, lo que postgresql hace es que toma la marca de tiempo timezoneless (''2014-01-2 00:30:00) y la trata como si YA FUERA una marca de tiempo'' PST ''TZ (es decir: 2014-01-2 00:30 : 00 -0800) y lo convierte a la zona horaria UTC !!! ¡Así que en realidad lo empuja 8 horas antes en lugar de volver! Así obtenemos (2014-01-02 08: 30: 00 + 00).
De todos modos, este último comportamiento (no intuitivo) es la causa de toda confusión. Lea el artículo si desea una explicación más completa, en realidad obtuve resultados que son un poco diferentes a los de esta última parte, pero la idea general es la misma.
No veo el tipo exacto de starts_at en su pregunta. Deberías incluir esta información, es la clave de la solución. Tendré que adivinar.
Básicamente, PostgreSQL siempre almacena el valor de tiempo UTC para el tipo de timestamp with time zone internamente. Solo la pantalla varía con la configuración actual de la timezone . El efecto de la construcción AT TIME ZONE también cambia con el tipo de datos subyacente. Más detalles:
Si extrae una date de tipo date y timestamp [without time zone] , obtendrá la fecha de la zona horaria actual. El día en la salida será el mismo que en la visualización del valor de la timestamp de timestamp .
Si extrae una date del tipo date y timestamp with time zone ( timestamptz para abreviar), primero se "aplicará" el desfase de zona horaria. Aún obtiene la fecha para la zona horaria actual, que coincide con la visualización de la marca de tiempo. El mismo momento se traduce al día siguiente en partes de Europa, cuando ya pasaron las 4 pm en California, por ejemplo. Para obtener la fecha de una determinada zona horaria, primero aplique AT TIME ZONE .
Por lo tanto, lo que describes en la parte superior de la pregunta contradice tu ejemplo.
Dado que starts_at es una timestamp [without time zone] y el tiempo en su servidor se establece en la hora local. Prueba con:
SELECT now();
¿Se muestra al mismo tiempo que un reloj en tu pared? Si es así (y el servidor db se está ejecutando con la hora correcta), la configuración de la timezone de su sesión actual concuerda con su zona horaria local. Si no, puede visitar la configuración de la timezone en su postgresql.conf o su cliente para la sesión. Detalles en el manual.
Tenga en cuenta que el desplazamiento de la timezone utilizó el signo opuesto de lo que se muestra en los literales de la marca de tiempo. Ver:
Para obtener su fecha local desde starts_at solo
SELECT starts_at::date
Tantamount para:
SELECT date(starts_at)
Por cierto, su hora local es UTC-7 en este momento, no UTC-8, porque el horario de verano está vigente (no entre las ideas más brillantes de la raza humana).
La Hora estándar del Pacífico (PST) normalmente es 8 horas "anterior" (mayor valor de timestamp ) que UTC (Zona horaria universal), pero durante los períodos de horario de verano (como ahora) puede ser de 7 horas. Es por eso que timestamptz se muestra como 2012-06-21 02:00:00 -07 en su ejemplo. La construcción AT TIME ZONE ''PST'' tiene en cuenta el horario de verano. Estas dos expresiones arrojan resultados diferentes (una en invierno, una en verano) y pueden dar lugar a fechas diferentes cuando se emiten:
SELECT ''2012-06-21 01:00:00''::timestamp AT TIME ZONE ''PST''
,''2012-12-21 01:00:00''::timestamp AT TIME ZONE ''PST''
Sé que esta es una versión antigua, pero es posible que desee considerar el uso de AT TIME ZONE "EE. UU./Pacífico" cuando realice el envío para evitar problemas de PST / PDT. Asi que
SELECCIONAR starts_at :: TIMESTAMPTZ EN LA ZONA HORARIA "EE. UU./Pacífico" DESDE los horarios WHERE ID = ''40'';