Change column from date to timestamp

Question

Change column from date to timestamp

Navigation

#1 by (0 votes)

0

I have a table with a column of type date but I need to see the GMT information, my idea is to convert it to timestamp . How can I change this even if the column already contains value?

create table PRO_TFESTIVO
(
  oid_festivo      NUMBER(10) not null,
  fecha_hora_envio DATE to TIMESTAMP
)

data oracle

asked by anonymous 25.07.2017 / 16:21

1 answer

session comparison with a database field in php Error saving MySQL PHP form information

score 0 · Answer 1

You can not change the type of a DATE / TIMESTAMP column without:

Rename the column (Ex: OLD_FECHA_HORA_ENVIO) with the existing values;

Create a new column with the original name (DATE_HORA_ENVIO);

Populate this new column with the values in the renamed table;

Remove the column (OLD_FECHA_HORA_ENVIO), because we will no longer use it;

Convert from DATE to TIMESTAMP WITH TIME ZONE:

ALTER TABLE pro_tfestivo RENAME COLUMN FECHA_HORA_ENVIO TO OLD_FECHA_HORA_ENVIO;
ALTER TABLE pro_tfestivo ADD FECHA_HORA_ENVIO TIMESTAMP WITH TIME ZONE;
UPDATE pro_tfestivo SET FECHA_HORA_ENVIO = FROM_TZ(CAST(OLD_FECHA_HORA_ENVIO AS TIMESTAMP), 'GMT');
ALTER TABLE pro_tfestivo DROP COLUMN OLD_FECHA_HORA_ENVIO;

Extra :) Convert from TIMESTAMP WITH TIME ZONE to DATE:

ALTER TABLE pro_tfestivo RENAME COLUMN FECHA_HORA_ENVIO TO OLD_FECHA_HORA_ENVIO;
ALTER TABLE pro_tfestivo ADD FECHA_HORA_ENVIO DATE;
UPDATE pro_tfestivo SET FECHA_HORA_ENVIO = CAST(to_timestamp_tz(OLD_FECHA_HORA_ENVIO, 'dd/mm/yyyy hh24:mi:ssXFF TZR') at time zone 'GMT' AS DATE);
ALTER TABLE pro_tfestivo DROP COLUMN OLD_FECHA_HORA_ENVIO;