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

Question

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    |        

Answer 1

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

Answer 2

The final SQL is:

CREATE OR REPLACE VIEW year_2010 AS
select to_char(timestamp with time zone 'epoch' + Day_start * interval '1 second', 'DD-MM') as Day_start,
       "MainTable".data,
       "MainTable".whatever
FROM "MainTable"
WHERE "MainTable".Day_start BETWEEN 1262304000 AND 1293753600;

Another good solution, but prints the date as date, year-month-day:

select to_timestamp(day_start)::date as Day_start,
    "MainTable".Data
FROM "MainTable"
WHERE "MainTable".day_start BETWEEN 1262304000 AND 1293753600;

I solved adding a new column only of timestamp, I order the SQL based on timestamp and the results are from 1 Jan to 31 Dec.

---

_{Community Wiki answer containing answers originally added to the question or left in comments by the question author}