Hi,
I have a column of type
TIMESTAMP
t in which I safe timestamps with 6 digit precision e.g. 03-FEB-17 04.45.01.909000 PM
Due to the way they have ben added to the table they are missing two things:
1. The time zone information
2. the correct century
Regarding 1: To add the timezone I created a new column t_tz of type
TIMESTAMP(6) WITH TIME ZONE
and want to copy the old to the new column, adding the timezone in the process with:
UPDATE myTable
SET t_tz = t
Regarding 2: The display 03-FEB-17 is irritating because the database understands the value as 3rd February 17 BC but should be 3rd February 2017. I tried
add_month( t, 2000 * 12 )
to get the year right but in the process the fractional seconds are lost e.g.
03-FEB-17 04.45.01.909000 PM --> 03-FEB-17 04.45.01.000000 PM -05:00
What would be the right way to achieve both goals.
I wasn't able to find a matching answer despite extensive search.
1. Yes, you can set the new timestamp with time zone column equal to the original:
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24.MI.SSXFF';
alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF TZH:TZM';
create table t (
ts timestamp
);
insert into t values (timestamp'0017-02-03 04:45:01.909');
select * from t;
alter table t add (tsz timestamp with time zone);
update t
set tsz = ts;
select * from t;
TS
03-FEB-0017 04.45.01.909000000
update t
set tsz = to_timestamp_tz (
to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00',
'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
);
TS TSZ
03-FEB-0017 04.45.01.909000000 03-FEB-0017 04.46.01.909000000 +00:00
Though it's better practice to explicitly state your conversions. This also enables you to set a particular time zone if you want:
update t
set tsz = to_timestamp_tz (
to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00',
'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
);
select * from t;
TS TSZ
03-FEB-0017 04.45.01.909000000 03-FEB-0017 04.45.01.909000000 -08:00
If your table is "large" it may be better (faster) to use "create as select" to make a new table with the correct data type:
create table t_new as
select to_timestamp_tz (
to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00',
'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
) ts
from t;
Then have a process to copy the constraints to the new table and switch them over.
2. Add_months implicitly converts the timestamps to a date. This is why you're losing the fractional seconds:
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24.MI.SSXFF';
select add_months (
to_timestamp (
'03-FEB-0017 04.45.01.909000 PM',
'DD-MON-YYYY HH:MI:SS.FF AM'
),
2000*12
) t
from dual;
T
03-FEB-2017 16:45:01
To overcome this, add an interval of 2000 years, avoiding the implicit conversion:
select to_timestamp (
'03-FEB-0017 04.45.01.909000 PM',
'DD-MON-YYYY HH:MI:SS.FF AM'
) + interval '2000' year(4) t
from dual;
T
03-FEB-2017 16.45.01.909000000