How to run a query for every date for last 3 month

Question

I have a table(pkg_date) in redshift. I want to fetch some data for every date for the last 3 months.

Here is my query

select * from pkg_data where scan_date < current_date;

How can I use current_date as a variable in the query itself and run this query for every date from April 1.

I have set a cron job which will run in every hour. In every hour it should run with different current_date

`In every hour it should run with different current_date` Which date do you want to be used at which time_of_execution? is there some kind of formula? — joop, Jun 21 '18 at 08:55

sandeep rawat · Answer 1 · 2018-06-22T04:15:28.647

1

Use dateadd() for getting date 3 moth old day and GETDATE() for get current date.

ie code will look like.

select * from pkg_data where scan_date < dateadd(month,-3,GETDATE());

edited Jun 22 '18 at 04:15

answered Jun 21 '18 at 08:16

sandeep rawat

score 1 · Answer 2 · answered Jun 21 '18 at 09:18

SELECT *
FROM pkg_data
WHERE scan_date > CURRENT_DATE - INTERVAL '3 months'

Be careful — Redshift works in UTC, so the CURRENT_DATE might suffer from timezone effects and be +/- what you expect sometimes.

SELECT
  CURRENT_DATE,
  (CURRENT_DATE - INTERVAL '3 months')::date

Returns:

2018-06-21  2018-03-21

Also be careful with strange lengths of months!

SELECT DATE '2018-05-31' - INTERVAL '3 months'

returns:

2018-02-28 00:00:00

Notice that it gave the last day of the month (31st vs 28th).

By the way, you can use DATE '2018-05-31' or '2018-05-31'::DATE, and also INTERVAL '3 months' or '3 months'::INTERVAL to convert types.

2 Answers2