1

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    |        
2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.