Postgresql, change column data TYPE from timestamp to DATE?

Question

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?

double precision is not a "timestamp" to begin with. A timestamp is a timestamp. But to answer your question we would need to know what values does the column contain? — user330315
– user330315, Commented Jan 28, 2022 at 14:58
1) Create a new empty column of type TIMESTAMP with a different name. 2) Update the new column converting the number to a timestamp. 3) Drop the existing BAD column. 4) Rename the new column to have the name of the old column. — The Impaler
– The Impaler, Commented Jan 28, 2022 at 15:14

Laurenz Albe · Accepted Answer · 2022-01-28 19:40:10Z

4

Use the appropriate USING clause:

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

edited Jan 28, 2022 at 19:40

answered Jan 28, 2022 at 15:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Laurenz Albe Over a year ago

Ah, silly me, I declared the column as date rather than as timestamp. Fixed. You were right all along.

zumzum · Accepted Answer · 2022-01-28 16:19:36Z

1

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.

edited Jan 28, 2022 at 16:19

answered Jan 28, 2022 at 16:09

zumzum

20.7k31 gold badges133 silver badges214 bronze badges

2 Comments

bfris Over a year ago

Check out the docs for Date/Time data types. The date, timestamp, and timestamptz types consume 4, 8, and 12 bytes, respectively. One reason to use date would be to save space. Or maybe the application would never need resolution down to hours/minutes/seconds... and it would be extra work every time to cast a high resolution type (date+time) to a lower resolution type (date only). It's also worth noting that there are time only data types, too. Some DB designers might wish to have date and time in separate columns.

zumzum Over a year ago

Good info. Thanks!

Muhammad Roshan C · Accepted Answer · 2023-04-13 11:36:12Z

-1

How to update timestamp datatype in postgresql

ALTER TABLE [TABLE_NAME] ALTER COLUMN [COLUMN_NAME] TYPE TIMESTAMP without time zone

answered Apr 13, 2023 at 11:36

Muhammad Roshan C

73 bronze badges

Collectives™ on Stack Overflow

Postgresql, change column data TYPE from timestamp to DATE?

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related