I have data with a frequency of one minute for 3 years and I would need to put it in one table to make it comparable.
Table1-2019
date_time | v_2020 |
---|---|
01.01.2019 01:00:00 | 50 |
01.01.2019 01:01:00 | 49 |
01.01.2019 01:02:00 | 56 |
Table2-2020
date_time | v_2020 |
---|---|
01.01.2020 01:00:00 | 60 |
01.01.2020 01:01:00 | 59 |
01.01.2020 01:02:00 | 56 |
Table3-2021
date_time | v_2020 |
---|---|
01.01.2021 01:00:00 | 55 |
01.01.2021 01:01:00 | 54 |
01.01.2021 01:02:00 | 48 |
requested table
date_time | v_2019 | v_2020 | v_2021 |
---|---|---|---|
01.01. 01:00:00 | 50 | 60 | 55 |
01.01. 01:01:00 | 49 | 59 | 54 |
01.01. 01:02:00 | 56 | 56 | 48 |
I tried several codes, but they didn’t work. With functions JOIN
and LEFT
, I have a problem with the format of date_time column (it is a timestamp without zone
). With the SUBSTR
I had also a problem with format of date_time.
Finally I tried code below, but it also doesn’t work.
CREATE TABLE all AS
SELECT A.date_time, A.v_2019 FROM Table1 AS A
JOIN Table2
WHERE (select datepart(day, month, hour, minute) from A.date_time)=(select datepart(day, month, hour, minute) from Table2.date_time)
JOIN Table3
WHERE (select datepart(day, month, hour, minute) from A.date_time)=(select datepart(day, month, hour, minute) from Table3.date_time)
2
Answers
Once you create your tables run this query. I believe that it is straightforward:
See DB-fiddle
While you can do this with an
INTERVAL
I think you should consider aJOIN
condition that uses date manipulating functions.Keep in mind using something like
WHERE DATE_TRUNC(...)
orJOIN ... ON DATE_TRUNC(...)
will NOT respect indexes on these fields. When passing the field value into a function you’re essentially creating a black box that cannot take advantage of an index. You would need to create an index specifically onDATE_TRUNC('DAY', date_time)
for example.Here is another DBFiddle for you to consider
You can do this in a couple ways:
Both of these result in the following: