Migrating Oracle Date Columns to Timestamp with Timezone

Migrating Oracle DATE columns to TIMESTAMP with timezone

You could just run:

ALTER TABLE your_table MODIFY your_date_column TIMESTAMP WITH TIME ZONE;

But I would recommend adding a TIMESTAMP column to the table, using an UPDATE statement to populate, and drop the original date column if you so choose:

ALTER TABLE your_table ADD date_as_timestamp TIMESTAMP WITH TIME ZONE;

UPDATE your_table
   SET date_as_timestamp = CAST(date_column AS TIMESTAMP WITH TIME ZONE);

The conversion is backwards compatible - you can switch back & forth as you like.

Oracle: set timezone for column

Creating and populating the new column ...

SQL> alter table t23
  2      add new_col timestamp(3) with time zone
  3  /

Table altered.

SQL> update t23
  2      set new_col = col3
  3  /

7 rows updated.

SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') new_col
  2  from t23
  3  /

NEW_COL
----------------------------
22-MAR-2010 03:20:58.000 PST
21-MAR-2010 03:20:58.000 PST
20-MAR-2010 03:20:58.000 PST
19-MAR-2010 03:20:58.000 PST
18-MAR-2010 03:20:58.000 PST
17-MAR-2010 03:20:58.000 PST
16-MAR-2010 03:20:58.000 PST

7 rows selected.

SQL> 

So now to set the values of COL3 to NOON UTC, or GMT as we Brits (and Oracle) know it:

SQL> alter session set time_zone = 'GMT'
  2  /

Session altered.

SQL> update t23
  2      set col3 =
  3          cast(to_char(col3, 'DD-MON-YYYY')||'12:00:00' as timestamp) at time zone sessiontimezone
  4  /

7 rows updated.

SQL>

Let's see the outcome:

SQL> alter session set time_zone = 'PST'
  2  /

Session altered.

SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as orig_val
  2         , to_char(col3,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as upd_val
  3  from t23
  4  /

ORIG_VAL                     UPD_VAL
---------------------------- ----------------------------
22-MAR-2010 03:20:58.000 PST 22-MAR-2010 12:00:00.000 GMT
21-MAR-2010 03:20:58.000 PST 21-MAR-2010 12:00:00.000 GMT
20-MAR-2010 03:20:58.000 PST 20-MAR-2010 12:00:00.000 GMT
19-MAR-2010 03:20:58.000 PST 19-MAR-2010 12:00:00.000 GMT
18-MAR-2010 03:20:58.000 PST 18-MAR-2010 12:00:00.000 GMT
17-MAR-2010 03:20:58.000 PST 17-MAR-2010 12:00:00.000 GMT
16-MAR-2010 03:20:58.000 PST 16-MAR-2010 12:00:00.000 GMT

7 rows selected.

SQL>

All that's left to do is to drop the backup column...

SQL> alter table t23 drop column new_col
  2  /

Table altered.

SQL>

Although, if it is a big table you may prefer to set it to UNUSED and then drop it in slow time.

How to update a TIMESTAMP column to TIMESTAMP WITH TIME ZONE in Oracle

With a little help from @JustinCave, I arrived at the following solution, which accomplishes exactly what I wanted:

-- Rename the old columns so we can use them as a data source *AND* so
-- we can roll back to them if necessary.
alter table OOPSIE_TABLE rename column COLUMN_A to OLD_COLUMN_A;
alter table OOPSIE_TABLE rename column COLUMN_B to OLD_COLUMN_B;
-- Define COLUMN_A and COLUMN_B to have TIME ZONE support.
alter table OOPSIE_TABLE add (
    COLUMN_A timestamp(6) with time zone,
    COLUMN_B timestamp(6) with time zone
);
-- Populate the "new" columns with the adjusted version of the old data.
update OOPSIE_TABLE set
    COLUMN_A = from_tz(OLD_COLUMN_A, 'America/New_York') at time zone 'UTC',
    COLUMN_B = from_tz(OLD_COLUMN_B, 'America/New_York') at time zone 'UTC'
;

ORACLE SQL adjust date for timezone without casting to timestamp or char

It seems that you need something like this (which would be a lot easier if Oracle also had "date with time stamp" data types):

...
cast(from_tz(cast(your_date as timestamp), 'UTC') at time zone 'US/Eastern'
  as date) as your_column_alias
...

Cast the date as timestamp, so you can give it a time zone (UTC is the new GMT - that's a separate issue), convert to your required time zone, and then convert back to date - no time zone, no fractional seconds.

Alter table with timestamp with time zone need to migrate old data or not

timestamp with time zone does not store time zone information in PostgreSQL, it stores an UTC timestamp that is converted to the session time zone upon retrieval.

The ALTER TABLE you show will interpret the timestamp without time zone values in the current session time zone, so if all your data are to be interpreted in the Europe/London time zone, the result should be correct.

If you need to store the time zone of the timestamp, e.g. to preserve the input time zone, you'll have to store the time zone as a separate field.

SHOW TimeZone;
┌───────────────┐
│   TimeZone    │
├───────────────┤
│ Europe/Vienna │
└───────────────┘
(1 row)

CREATE TABLE mytime(
   id integer PRIMARY KEY,
   ts timestamp without time zone NOT NULL
);

INSERT INTO mytime VALUES (1, '2017-05-15 12:00:00');

SELECT * FROM mytime;
┌────┬─────────────────────┐
│ id │         ts          │
├────┼─────────────────────┤
│  1 │ 2017-05-15 12:00:00 │
└────┴─────────────────────┘
(1 row)

ALTER TABLE mytime ALTER ts TYPE timestamp with time zone;

SELECT * FROM mytime;
┌────┬────────────────────────┐
│ id │           ts           │
├────┼────────────────────────┤
│  1 │ 2017-05-15 12:00:00+02 │
└────┴────────────────────────┘
(1 row)

If I change my time zone, the values displayed change:

SET TimeZone = 'Asia/Kolkata';

SELECT * FROM mytime;
┌────┬───────────────────────────┐
│ id │            ts             │
├────┼───────────────────────────┤
│  1 │ 2017-05-15 15:30:00+05:30 │
└────┴───────────────────────────┘
(1 row)

SQL to convert TimeZone of TimeStamp column from A to B when system is in TimeZone C

You can use the following query:

select
   ID, Name, DOB, Place, BirthTime as orig_BT,
   FROM_TZ(BirthTime, 'UTC') AT TIME ZONE 'Europe/London' AS BT_BST
FROM BirthDatabase;

When you make cast(BirthTime as timestamp with time zone) the Oracle takes your SESSIONTIMEZONE for conversion - not the database system time zone.

cast(BirthTime as timestamp with time zone) at time zone 'Europe/London' will give correct results when you execute alter session set time_zone = 'UTC'; beforehand.

How to convert Timestamp to Date column in Oracle?

I believe you can do direct modify to this column and all will be ok:

ALTER TABLE testTable
MODIFY test_c date;

DEMO

---

Related Topics