Postgres Join tables using timestamp (part of it)

Question

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)

Stefanov.sm · Accepted Answer · 2022-10-12 13:43:30Z

1

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

edited Oct 12, 2022 at 13:43

answered Oct 12, 2022 at 13:32

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

beginner Over a year ago

Thank you for your help. I have an additional question. How can I deal with the occasional missing value in some columns? For example for 01-01 01:01 there will miss v_2020_2020 but still, I would have a row with this time.

J Spratt Over a year ago

@beginner Use LEFT JOIN instead of JOIN. Left, right, and inner join are represented often as a venn diagram. There is also OUTER JOIN.

J Spratt · Accepted Answer · 2022-10-13 13:58:30Z

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

Collectives™ on Stack Overflow

Postgres Join tables using timestamp (part of it)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related