Select by array of dates and next day if row doesn't exist?

Question

I was wondering if it was possible to run a IN condition and a > condition (only if not found in the IN) at the same time when doing date comparisons from a row of dates?

For instance if 2018-01-01 doesn't exist it will pull the next available row of the date 2018-01-02 and it should do this for a random array of dates I supply. (NOT RANGE since this will pull in dates I'm not interested in.)

Example:

create table trade (
    id serial primary key,
    year int,
    month int,
    "data" json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 1, '{"2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "3": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json
);

insert into trade ("year", "month", "data") VALUES (
  2018, 2, '{"1": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}, "2": {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000}}'::json

SELECT
 t.prices,
 make_date("year", "month", t.day::int) as date
FROM
trade
JOIN json_each(trade.data) t(day, prices) ON TRUE
WHERE
make_date("year", "month", t.day::int) IN ('2018-01-1', '2018-01-03')
);

Would like it to return prices of 1950-01-03, 2018-01-03, AND 2018-01-02 (since 2018-01-01 doesn't exist)

I'm working on a function that will do a 1/1 ratio of results when I supply it dates I'm interested in, and if they don't exist, it will return the next available date.

klin · Accepted Answer · 2018-12-10 09:48:31Z

1

Assuming that the gaps may be larger than one day the following function seems the simplest and efficient solution:

create or replace function trade_on_dates(variadic date[])
returns table (prices json, date date) 
language plpgsql stable as $$
declare
    d date;
begin
    foreach d in array $1 loop
        return query select
            t.prices,
            make_date("year", "month", t.day::int) as date
        from trade
        join json_each(trade.data) t(day, prices) on true
        where make_date("year", "month", t.day::int) >= d
        order by 2
        limit 1;
    end loop;
end $$;

select *
from trade_on_dates('2018-01-01', '2018-01-03');

                              prices                              |    date    
------------------------------------------------------------------+------------
 {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000} | 2018-01-02
 {"low": 19, "high": 21, "open": 20, "close": 20, "volume": 1000} | 2018-01-03
(2 rows)

answered Dec 10, 2018 at 9:48

klin

123k15 gold badges240 silver badges262 bronze badges

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

1 Comment

Ajm113 Over a year ago

I definitely like this method. It's a lot cleaner and less error prone. Thanks @klin!

Kaushik Nayak · Accepted Answer · 2018-12-10 09:44:31Z

1

You may use generate_series to check for all dates in a given range.

SELECT
 t.prices,
 make_date("year", "month", t.day::int) as date
FROM
trade
JOIN json_each(trade.data) t(day, prices) ON TRUE
WHERE
("year"|| lpad( "month"::text,2,'0') || lpad(t.day,2,'0') )::DATE -- a simulation of 
                                                                 --your make_date function.

IN ( select 
           generate_series(DATE '2018-01-1',
                   DATE '2018-01-03',INTERVAL '1 DAY') ::DATE
);

Demo

Edit

And suppose I wanted a date in 1950-01-01, 2001-01-01, and 2002-01-04, etc, etc

You may use multiple ranges in a UNION ALL

..
IN ( select 
           generate_series(DATE '2018-01-1',
                   DATE '2018-01-03',INTERVAL '1 DAY') ::DATE
                   UNION ALL
     select 
           generate_series(DATE '1950-01-01',
                   DATE '1950-01-03',INTERVAL '1 DAY') ::DATE    
                   UNION ALL
     select 
           generate_series(DATE '2002-01-01',
                   DATE '2002-01-04',INTERVAL '1 DAY') ::DATE                  

)
..
..

edited Dec 10, 2018 at 9:44

answered Dec 10, 2018 at 9:06

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

2 Comments

Ajm113 Over a year ago

That's amazing! I love Postgres! -- I would have dug around the docs, but it seems like their site is down. To me at least. -- Anyway, thank you very much!

Ajm113 Over a year ago

I do have one question. What if I wanted to supply an array of random dates? Because I have data that date back to the 1800's to today. And suppose I wanted a date in 1950-01-01, 2001-01-01, and 2002-01-04, etc, etc. I don't want to pull in everything in between I don't want. If that makes sense. -- I'll update my question with a little more context.

Collectives™ on Stack Overflow

Select by array of dates and next day if row doesn't exist?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related