Modify datatype of date column into integer datatype

user_18363474 0
Viewed 38

I have a table of millions or records and now the requirement is to change the datatype of the date column into integer.

Can we do this ? If yes then how? without loosing any data.

Thanks

oracle19c
2 Answers

You can convert the date to a formatted string and then to a number:

SELECT TO_NUMBER(TO_CHAR(your_date, 'YYYYMMDDHH24MISS')) AS date_number
FROM   your_table

Or you could convert the values to be relative to an epoch:

SELECT ROUND((your_date - DATE '1970-01-01')*86400) AS seconds_since_1970
FROM   your_table

If you want to add a virtual column to the database to do the conversion then:

ALTER TABLE your_table
  ADD date_number INTEGER
        GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(your_date, 'YYYYMMDDHH24MISS')));

or

ALTER TABLE your_table
  ADD seconds_since_1970 INTEGER
        GENERATED ALWAYS AS (ROUND((your_date - DATE '1970-01-01') * 86400));

Then the table would contain:

YOUR_DATE DATE_NUMBER SECONDS_SINCE_1970
2022-04-26T14:36:48 20220426143648 1650983808

db<>fiddle here

user_15092640

Modify, no.

Realistically, you'd want to add a new integer column to the table, move the data to the new column, drop the old column, and rename the new column. Something like

alter table foo
  add( new_column integer );

update foo
   set new_column = to_number( to_char( old_column, 'YYYYMMDDHH24MISS' ) );

alter table foo
  drop column old_column;

alter table foo
  rename column new_column to old_column;

It may be more efficient to set the old column to unused rather than dropping it depending on how big your table is and what sort of window you have.

And this doesn't address the underlying wisdom of the requirement. If you have data that represents a date, it very seldom makes sense to store that data as an integer or a varchar2 or anything other than a date.

Justin_Cave_103970
Related