I have a table in Postgres that has a date column and time column. I would like to add a new column that has utc (or timestamp) values based on on the date and time columns.
I tried the following:
INSERT INTO mytable (utc) SELECT EXTRACT(EPOCH FROM (date || ' ' || time )::timestamp);
ERROR: column "date" does not exist
SQL state: 42703
Hint: There is a column named "date" in table "mytable", but it cannot be referenced from this part of the query.
To add a new column you need an
alter table statement:
alter table mytable add column utc float;
Then you need to update that column with the new data:
update mytable set utc = EXTRACT(EPOCH FROM (date || ' ' || time )::timestamp);
You now need a trigger that keeps the date/time and utc columns in sync.
The above is not a good idea. The rule of thumb in a relational database:
Never store information that can be derived from the data that is already in the database.
So the clean solution is to not store date and time in two different columns and not add another column that contains exactly the same information in a different representation.
You should have a single column with the type
timestamp, then create a view that returns that
timestamp with the different formats you want:
-- add the new timestamp column alter table mytable add column ts_column timestamp; -- copy the data update mytable set ts_column = (date || ' ' || time )::timestamp; -- get rid of the old columns alter table mytable drop column date, drop column time; -- now create a view that display the information in different ways create view formatted_mytable as select id, ts_column, to_char(ts_column, 'yyyy-mm-dd') as date_only, to_char(ts_column, 'hh24:mi:ss') as time_only, extract(epoch from ts_column) as epoch from my_table;
That way you don't need to duplicate the information stored in the table just because you need a different display format.