Re: campos timestamp

Buenos dias:

2016-10-06 10:00 GMT+02:00 Magi Franquesa <magifranquesa(at)gmail(dot)com>:
> Tengo una bd con registros de incendios en los que se indica la fecha y hora
> de inicio en un campo de tipo "timestamp with time zone", pero el problema
> es que en lugar de un solo campo con ambos datos hay dos campos: uno que
> contiene la fecha correcta y el segundo la hora. Necesito crear un nuevo
> campo del mismo tipo con la fecha y hora como muestro en el ejemplo
> siguiente:
>
> Campo 1 (fecha inicio):"1968-09-08 00:00:00+01"
> Campo 2 (hora inicio): "1900-01-01 10:00:00+00"
> Resultado buscado: "1968-09-08 10:00:00+00"

Quieres extraer la ZONA HORARIA del registro 2? Eso depende mucho de
tu base de datos de zonas y de tu zona de cliente predeterminada, a mi
por ejemplo me pasa esto:

n=> with base as (select '1968-09-08 00:00:00+01'::timestamp with time
zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select * from base;
ts_fecha | ts_hora
------------------------+------------------------------
1968-09-08 00:00:00+01 | 1900-01-01 09:45:16-00:14:44

Porque si, en España parece ser que por aquella epoca llevabamos el
meridiano de Madrid, o algo asi, porque 14min:44 segs, ya que una hora
son 15 grados de latitud, y 15*(14/60+44/3600)=3.683333, .683333*60 ~=
41, 3-41W que es la puerta del Sol, calle arriba calle abajo.

Tienes varias formas de pegar eso, una es a lo burro en texto, jugando
con substrings. Otra puede ser extrayendo las fechas y horas mediante
casts:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select cast(ts_fecha as date) , cast(ts_hora as time with time zone) from base;
ts_fecha | ts_hora
------------+-------------------
1968-09-08 | 09:45:16-00:14:44
(1 row)

Que solo con sumarse te da un resultado, pero puede no ser lo que quieres:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select cast(ts_fecha as date) + cast(ts_hora as time with time zone) from base;
?column?
------------------------
1968-09-08 11:00:00+01
(1 row)

FIJATE que la rutina de salida de la fecha te lo da en la hora que
estaba en efecto en esa epoca, si quieres FORMATEARLO en UTC no tienes
mas que hacer:

newtron=> set timezone to 'UTC';

PERO entonces tienes el problema de que el cast intermedio a DATE te
lo pasa por UTC y no te da lo que buscas:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select cast(ts_fecha as date) + cast(ts_hora as time with time zone) from base;
?column?
------------------------
1968-09-07 10:00:00+00
(1 row)

Con lo que igual tienes que pasarlo a sin-timezone:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select (cast(ts_fecha as date) + cast(ts_hora as time with time zone))
at time zone 'UTC' from base;
timezone
---------------------
1968-09-08 10:00:00
(1 row)

En general el problema es complicado, dado que los ts WITH tz designan
un instante en el tiempo, convertirlos como tu quieres a FECHAS es
imposible sin saber en que zona horaria estaban originalmente.
Normalmente la forma correcta es pasar de ts WITH tz a WITHOUT tz, que
ya no tiene problemas, operar ahi y luego reconvertir de vuelta, p.e.:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select (cast(ts_fecha at time zone 'Europe/Madrid' as date) +
cast(ts_hora at time zone 'UTC' as time)) at time zone 'UTC' from
base; timezone
------------------------
1968-09-08 11:00:00+01
(1 row)

Observa que me sale en el horario mio ( Madrid ), si exploras las
partes veras que el proceso es, primero sacar una fecha y hora sin
zonas:

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select cast(ts_fecha at time zone 'Europe/Madrid' as date),
cast(ts_hora at time zone 'UTC' as time) from base;
timezone | timezone
------------+----------
1968-09-08 | 10:00:00
(1 row)

newtron=> with base as (select '1968-09-08 00:00:00+01'::timestamp
with time zone as ts_fecha,
'1900-01-01 10:00:00+00'::timestamp with time zone as ts_hora)
select cast(ts_fecha at time zone 'Europe/Madrid' as date) +
cast(ts_hora at time zone 'UTC' as time) from base;
?column?
---------------------
1968-09-08 10:00:00
(1 row)

Luego el cast lo interpreta en utc ( +00 ) y lo pasa a un instante en
el tiempo, y luego como yo estoy en Madrid el sistema me pinta que
hora era en Madrid en ese instante.

Espero que te ayude, de aqui coge lo que te valga, intenta algo,
pregunta si tienes mas dudas, recuerda que solo TU sabes exactamente
como llegaron a tu BD esas columnas y como desenredarlas.

Francisco Olarte.

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda