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 |

Community · Accepted Answer · 2017-05-23 12:40:45Z

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

user126897 · Accepted Answer · 2017-10-15 08:28:09Z

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}

Stack Exchange Network

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

2 Answers 2

Hot Network Questions

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

2 Answers 2

Related

Hot Network Questions