Postgresql - Postgres Join tables using timestamp (part of it)

beginner
October 12, 2022
286 views
0 votes
2 Answers

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

Visualisation of tables

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)

Answers

- Stefanovsm
- October 12, 2022 at 3:32 pm
- 0 votes
0
Once you create your tables run this query. I believe that it is straightforward:
```
select to_char(t1.date_time, 'mm-dd hh24:mi') date_time,
  t1.v_2020 v_2020_2019,
  t2.v_2020 v_2020_2020,
  t3.v_2020 v_2020_2021
from table1 t1
join table2 t2 on t2.date_time = t1.date_time + interval '1 year'
join table3 t3 on t3.date_time = t1.date_time + interval '2 years';
```
See DB-fiddle

date_time v_2020_2019 v_2020_2020 v_2020_2021

01-01 01:00 50 60 55

01-01 01:01 49 59 54

01-01 01:02 56 56 48
Login or Signup to reply.

- JSpratt
- October 12, 2022 at 5:27 pm
- 0 votes
0
While you can do this with an INTERVAL I think you should consider a JOIN condition that uses date manipulating functions.

Keep in mind using something like WHERE DATE_TRUNC(...) or JOIN ... 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 on DATE_TRUNC('DAY', date_time) for example.

Here is another DBFiddle for you to consider

You can do this in a couple ways:
```
SELECT TO_CHAR(v19.date_time, 'MM-DD HH24:MI') datetime
     , v19.v_2019
     , v20.v_2020
     , v21.v_2021
  FROM t_2019 v19
  FULL JOIN t_2020 v20
    ON DATE_PART('MONTH', v19.date_time) = DATE_PART('MONTH', v20.date_time)
   AND DATE_PART('DAY', v19.date_time) = DATE_PART('DAY', v20.date_time)
   AND v19.date_time::TIME = v20.date_time::TIME
  FULL JOIN t_2021 v21
    ON DATE_PART('MONTH', v20.date_time) = DATE_PART('MONTH', v21.date_time)
   AND DATE_PART('DAY', v20.date_time) = DATE_PART('DAY', v21.date_time)
   AND v20.date_time::TIME = v21.date_time::TIME
;
```
```
SELECT TO_CHAR(v19.date_time, 'MM-DD HH24:MI') datetime
     , v19.v_2019
     , v20.v_2020
     , v21.v_2021
  FROM t_2019 v19
  FULL JOIN t_2020 v20
    ON TO_CHAR(v19.date_time, 'MM-DD HH24:MI') = TO_CHAR(v20.date_time, 'MM.DD HH24:MI')
  FULL JOIN t_2021 v21
    ON TO_CHAR(v20.date_time, 'MM-DD HH24:MI') = TO_CHAR(v21.date_time, 'MM.DD HH24:MI')
;
```
Both of these result in the following:

datetime v_2019 v_2020 v_2021

01-01 01:00 50 60 55

01-01 01:01 49 59 54

01-01 01:02 56 56 48
Login or Signup to reply.