Postgresql – Create a view and populate 1 column with the extracted day-month from a epoch date from the main table

postgresqlview

I have this main table (year 2010):

  Serial   | Day_start | Data  
-----------|-----------|--------
  1        |1262304000 | data1  
  2        |  epoch    | data2  
  3        |  epoch    | data3  
  4        |  [...]    | .      
  5        |  epoch    | .      
  6        |  epoch    | .      
  7        |  epoch    | .      
  [...]    |  epoch    | .      
  365      |1293753600 | .      
  366      |  epoch    |

Where Serial corresponds to the day of the year.

Serial: 1 -> 1/jan/2010 -> 1262304000

I want to create a view, where the first column of this view extracts the day-month from the epoch of this table.

CREATE OR REPLACE VIEW year_2010 AS 
 SELECT row_number() OVER (ORDER BY "MainTable".Day_start) AS index,
    "MainTable".Day_start,
    "MainTable".Data
   FROM "MainTable"
  WHERE "MainTable".Day_start >= 1262304000 AND "MainTable".Day_start <= 1293753600;

With this SQL I create a view using the column Day_start as Index. Each, for each year. This table contains 8 Years of data (that's why I do not use the "Serial".

How do I extract the day-month from the epoch and rebuild the view?
I can't find the function I would need from here:
https://www.postgresql.org/docs/9.1/static/functions-datetime.html

The generated view should be:

Index(date)| Data(double precision)  
-----------|--------
  01-01    | data1  
  02-01    | data2  
  03-01    | data3  
  04-01    | .      
  [...]    | .      
  06-11    | .      
  07-11    | .      
  [...]    | .      
  31-12    | .      
  01-01    |

Best Answer

The answer is, like you already mentioned in the PostgreSQL documentation. An example is in the reply of Postgres: how to convert from unix epoch to date?

select timestamp with time zone 'epoch' + Day_start * interval '1 second'
from   "MainTable";

or simpler (thanks to @a_horse_with_no_name):

select to_timestamp(day_start)
from   "MainTable";

Use the to_char(timestamp, 'DD-MM') to get the day and month. The result to put in your CREATE VIEW would be something like:

select to_char(timestamp with time zone 'epoch' + Day_start * interval '1 second',
               'DD-MM') AS index;
from   "MainTable";

If you want the whole date as DATE type then use:

select (timestamp with time zone 'epoch' + Day_start * interval '1 second')::date AS index;
from   "MainTable";
Related Question