CODEWITHSUNDEEP
sqlpostgresqlalter-table
Yrgl
Yrgl

Reputation: 653

Cannot change column type from date to timestamp with time zone in PostgreSQL

I would like to change type of column in PostgreSQL from date to timestamp with time zone. But execution of follow sql continues until I stop it despite there are only 3 records in the table and without any error or successful result.

alter table some_table
    alter column column_date type timestamp
        using to_timestamp(extract(epoch from column_date))

At the same time select to_timestamp(extract(epoch from column_date)) from some_table is executing correct without any error.

I've try another variations for using expression

If there are null value of the column in all records type changing immediately with simple alter sql

alter table some_table
    alter column column_date type timestamp
        using column_date::timestamp with time zone

I use PostgreSQL 12

Is there appropriate method to change date to timestamp with time zone?

P.S. I know I can do this task by creating a new column with appropriate type and then swap them:))

Upvotes: 0

Views: 1392

Answers (1)

nbk
nbk

Reputation: 49375

Here is a simple example how you would use UTC as time zone

CREATE tABLE tm( mydate date)

CREATE TABLE

INSERT INTO TM VALUES('2022-01-01'),('2021-01-01'),('2020-01-01')

INSERT 0 3

SELECT * FROM tm
mydate
2022-01-01
2021-01-01
2020-01-01 
SELECT 3

ALTER TABLE tm
ALTER COLUMN myDate TYPE timestamp with time zone
USING myDate::timestamp AT TIME ZONE 'UTC';

ALTER TABLE

SELECT * FROM tm
mydate
2022-01-01 00:00:00+00
2021-01-01 00:00:00+00
2020-01-01 00:00:00+00 
SELECT 3

fiddle

Upvotes: 2

Related Questions