database – Error changing table in PostgreSQL. How do I change the type of an int column to a timestamp type?

Question:

I'm studying PostgreSQL BD from a recent book I purchased. When trying to change a table based on the command I learned in the book there was an error, I used the exact same command written in the book without alteration. I'm trying to change an int type field to a timestamp type but the following error occurs

–change in table with change from int type column to timestamp type

alter table commissions alter column payment_date type timestamp using payment_date_timestamp;

obs: in the book it is written data_pagamento_timestamp. Timestamp is a type in SQL to store date and time but with " timestamp" it is part of the table name. I removed the " " and left the timestamp separate from the column name, considering that this was a book error, but it didn't work because it still doesn't work. How to fix this?

the error that occurs below:

LINE 1: …pay_date type timestamp using pay_date timestamp; ^ SQL state: 42601 Character: 87

Answer:

timestamp is closed with one hour.

To transform numbers into timestamps, you must indicate these numbers they represent (seconds, minutes, hours). Y which corresponds to l 0.

For example if it is seconds and today is the moment 0:

create table table11(
  id serial primary key,
  seconds int
);

insert into table11 (seconds) values 
  (15), (60), (60*60);

alter table table11 alter seconds type timestamp
  using '2018-11-18'::date + (seconds||' seconds')::interval;

select * from table11;  

Note that the USING part is the one that indicates how the transformation should be done.

To see an example, go to: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=9f687c66e2fa82bddd14130d82c27b3a

Scroll to Top