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

Question

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

using column_date::timestamptz
using timestamp with time zone 'epoch' + (column_date + interval '0 second') * interval '1 second'
using column_date AT TIME ZONE 'UTC' with no effect.

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:))

nbk · Accepted Answer · 2022-11-18 20:12:55Z

2

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

answered Nov 18, 2022 at 20:12

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related