CODEWITHSUNDEEP
sqlamazon-redshift
khusnanadia
khusnanadia

Reputation: 853

SELECT interval where the number of interval is from table (redshift)

In redshift, I have query

CREATE TABLE result_table AS(
  SELECT * FROM table_name
  WHERE issued_timestamp >= date_part(epoch, trunc(getdate())- interval '2 days' + interval '17 hours')*1000
        AND issued_timestamp < date_part(epoch, trunc(getdate())- interval '0 days' + interval '17 hours')*1000
);

Can I select where the number of interval is from another table?
For example I have a table day_interval

day_since|day_until
21|5

So I can query like this

CREATE TABLE result_table AS(
  SELECT * FROM table_name
  WHERE issued_timestamp >= date_part(epoch, trunc(getdate())- interval '2 days' + interval '17 hours')*1000
        AND issued_timestamp < date_part(epoch, trunc(getdate())- interval '5 days' + interval '17 hours')*1000
);

I tried

CREATE TABLE result_table AS(
  SELECT * FROM table_name
  WHERE issued_timestamp >= date_part(epoch, trunc(getdate())- interval (SELECT day_since FROM "day_interval")' days' + interval '17 hours')*1000
        AND issued_timestamp < date_part(epoch, trunc(getdate())- interval '0 days' + interval '17 hours')*1000
);

but didn't work.

Thanks in advance

Upvotes: 3

Views: 714

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I understand correctly, you just want a JOIN:

CREATE TABLE result_table AS
  SELECT t.*
  FROM table_name t JOIN
       day_interval di
       ON t.issued_timestamp >= date_part(epoch, trunc(getdate()) - di.day_since * interval '2 days' + interval '17 hours') * 1000 AND
          t.issued_timestamp < date_part(epoch, trunc(getdate()) - di.day_until * interval '0 days' + interval '17 hours') * 1000
);

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Interval is always a literal.
interval 'x' days or interval x days won't work, but -
x * interval '1' days will. 

CREATE TABLE result_table AS(
  SELECT * FROM table_name
  WHERE issued_timestamp >= date_part(epoch, trunc(getdate()) - (SELECT day_since FROM "day_interval") * interval '1 days' + interval '17 hours')*1000
        AND issued_timestamp < date_part(epoch, trunc(getdate())- interval '0 days' + interval '17 hours')*1000
);

Upvotes: 3

Related Questions