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

Question

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

`insert` is there to insert new **rows**. New columns are added with `alter table add column`. But in general it's a bad idea to store data that can easily (and cheaply) be derived from the one that is already there. Create a view that contains that column, there is no need to duplicate information. And why are you storing date and time in two columns? Why don't you use a single `timestamp` column? — , Apr 20 '16 at 19:19
That's exactly what I'm trying to do. I can't manually create a UTC date-time value for every row. But I happen to have a table that was already created that has date and time values. To use a single UTC column, I need to be able to create those values based on existing values. — Evan, Apr 20 '16 at 19:23
Also, depending on the situation, it can be helpful to have multiple columns that explain different parts of data or are readable in different ways. In this example, seeing a UTC date like 411730.830555 may be meaningless to a human, but is required for functionality, and so it may be nice to have the date and time columns as well. — Evan, Apr 20 '16 at 19:28
No, it is **not** "helpful" to store data redundantly. If you need to display a timestamp column in different formats, create a view that does that. Do not store the same information multiple times. — , Apr 20 '16 at 20:01
It's still unclear to me what you are trying to do. `alter` the table to have a new `timestamp` column? `extract(epoch...)` does **not** return a timestamp value, it returns an integer value. A `timestamp` is something completely different: http://www.postgresql.org/docs/current/static/datatype-datetime.html — , Apr 20 '16 at 20:02
I want to take a date value and a time value and create a utc value like 411730.83055. Similar to [this question](http://stackoverflow.com/questions/17725418/get-a-timestamp-from-concatenating-day-and-time-columns) — Evan, Apr 20 '16 at 20:03

score 4 · Answer 1 · answered Apr 20 '16 at 20:47

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.

What worked was changing: `INSERT INTO mytable (utc) SELECT` to `set utc = `. — Evan, Apr 20 '16 at 21:01

score 1 · Accepted Answer · edited May 08 '18 at 21:49

The formatting from the other answer was actually different than what I wanted, upon further investigating.

I mentioned the formatting desired was ddddd.tttttt where d is a numerical version of a date (specifically, the number of days since 1900-01-00), and t is a fraction of 24 hours. This is the format Excel uses to store date-time values, called "serial date-time." For more information see Dates and Times in Excel.

I thought this was similar to postgresql's timestamp format, but it turned out to not work for my purposes.

This is what worked:

UPDATE mytable SET utc = DATE_PART('day', '2012-09-21'::timestamp - '1899-12-30'::timestamp) + (EXTRACT( HOURS FROM time::time)/24) + (EXTRACT( MINUTES FROM time::time)/1440) + (EXTRACT( SECONDS FROM time::time)/86400);

Just wanted to update the answers for accuracy and in case anyone else stumbles into this problem. It appears there's little information on how to do this on the internet. I appreciate the answer from @a_horse_with_no_name; that did help me in some ways.

Again: do **not** do that. Store a regular `timestamp` and format that to whatever you want when you _display_ it. Storing redundant information is a bad idea — , Apr 25 '16 at 19:43

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

2 Answers2

What you want to do

What you should do