Bird Bird - 7 months ago 37
SQL Question

SQL: Add UTC timestamp column to table from date and time

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


But I get the error:

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.


And there definitely are the columns date and time.

Does anyone know how to add values to UTC column based on date and time columns?

For example: 09/21/2012 12:56:00 should become something like 411730.830555

I'm trying to do something similar to this question (I think).

Answer

What you want to do

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.

What you should do

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.