Postgresql, change column data TYPE from timestamp to DATE?-CodePudding

I am trying to migrate a column in a table from timestamp (double precision) to a Date.

For example, right now seen_timestamp column contains values like this one:

1643302746243

Values now are all UTC. So that unix timestamp would be:

Thu, 10 Mar 54044 17:04:03 GMT

Which is part of the mistake I made. The timestamp is supposed to be this:

1643302746.243

Which would be this date:

01/27/2022, 04:59:06 PM

So, I could first update all values by dividing by 1000, and then migrating over to UTC Date type....

I tried this:

ALTER TABLE car
ALTER COLUMN seen_timestamp TYPE DATE USING seen_timestamp::DATE;

I get the following error:

cannot cast type double precision to date

Makes sense. I just don't know how to change/migrate the column to Date type.

How can I make this work?

CodePudding user response：

Use the appropriate USING clause:

ALTER TABLE car
ALTER COLUMN seen_timestamp TYPE date
USING to_timestamp(seen_timestamp / 1000.0::numeric);

CodePudding user response：

So I got it working. Both comments from The Impaler and answer from Laurenz helped get in the right direction. This is what works for me:

-- add new tmp column to store timestamps
ALTER TABLE car ADD COLUMN tmp_seen_timestamp timestamptz;
-- update new column using seen_timestamp values/1000
UPDATE car SET tmp_seen_timestamp=to_timestamp(seen_timestamp / 1000.0::numeric) where seen_timestamp is not null;
-- remove original column
ALTER TABLE car drop column seen_timestamp;
-- raname column...
ALTER TABLE car rename column tmp_seen_timestamp to seen_timestamp;

This works too, following what Laurenz posted:

ALTER TABLE car
ALTER COLUMN seen_timestamp TYPE timestamptz
USING to_timestamp(seen_timestamp / 1000.0::numeric);

This does what it needs, but, I am confused why using 'date' as type would not provide full precision.