skip to Main Content

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)

2

Answers


  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
    Login or Signup to reply.
  2. 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.
Please signup or login to give your own answer.
Back To Top
Search