CODEWITHSUNDEEP
databaseoracle-database
Felipe
Felipe

Reputation: 13

Oracle change Date to time stamp

My table has a DATE type column but I need to see the GMT information my idea is change to timestamp column. How to change the column which already has value filled?

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

Thank you all!!

Upvotes: 1

Views: 2540

Answers (2)

LauDec
LauDec

Reputation: 548

If you are luck enough to have the "Advanced Replication" license, another way to do this would be to use the DBMS_REDEFINITION package

--your existing table
create table MY_SCHEMA.MY_TABLE ( START_DATE DATE );
INSERT INTO MY_TABLE VALUES (SYSDATE);
commit;
--The new Structure of your table
create table MY_TABLE_NEW ( START_DATE TIMESTAMP );
begin
  dbms_redefinition.start_redef_table(
  uname=>'MY_SCHEMA',
  orig_table =>'MY_TABLE',
  int_table =>'MY_TABLE_NEW',
  col_mapping =>'cast(START_DATE as timestamp) START_DATE',
  options_flag =>dbms_redefinition.cons_use_rowid);
end;
/ 
exec dbms_redefinition.finish_redef_table('MY_SCHEMA','MY_TABLE','MY_TABLE_NEW');

After that, your original table ( which will keep it's name MY_TABLE ) should have it's column changed from DATE to TIMESTAMP with all the data converted on the fly. The advantage is that your original table stays available to the other applications during the process so you might do it in production without any interruption of service

PS : I don't have the license on my test DB so I can't test it now, but it should work

Upvotes: 0

Thiago Cavalcanti
Thiago Cavalcanti

Reputation: 71

You cannot change the DATE/TIMESTAMP type without:

  1. Create a temporary column with the existing values;
  2. Create a new table with the original name;
  3. Fill the existing values to the "new" column.

Convert 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;

Plus :) Convert 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;

Upvotes: 2

Related Questions