SELECTing data based on a specific time frame

Question

I'm using PostgreSQL. I have a table which stores data related to some orders. It has four to five columns, including:

cust_code char varying (20),
sugg_order integer,
ordered_qty integer,
date date

Now I want to find all the rows which occur within a specific time frame. Let us assume that this time frame starts on 1st of April every year and ends on 31st of March next year (like 2017-04-01 to 2018-03-31 and 2018-04-01 to 2019-03-31 and so on).

Below are the examples of the output which I want from the query:

• If I run this query on 2018-08-01 then I need all the data from 2018-04-01 to 2018-08-01.

• If I run this query on 2019-03-01 then I need all the data from 2018-04-01 to 2019-03-01.

• If I run this query on 2019-05-01 then I need all the data from 2019-04-01 to 2019-05-01

In other words, I need all data between today (the day I run the query) and the latest (most recent) 1st of April before today.

I need a query which will work for any year and I cannot get the start date of the time frame from outside.

Answer 1

I think you want something like this:

with param (date, year) as
  ( select 
        @date_param,
        extract(year from @date_param)
        - case when extract(month from @date_param) < 4 then 1 else 0 end
  )
select t.*
from table_name as t, param as p
where t.date >= make_date(p.year, 4, 1) 
  and t.date <= p.date ; 

Answer 2

In order to answer this, I did the following:

Created the table:

CREATE TABLE cust (cust_code VARCHAR(20), sugg_order integer, ordered_qty integer, ord_date date);

Created some sample data (note how it spans from early 2017 - before Apr 1st) to after today.

INSERT INTO cust VALUES ('cust_1', 45, 450, '2018-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 350, '2018-02-28');
INSERT INTO cust VALUES ('cust_1', 45,  50, '2018-03-28');
INSERT INTO cust VALUES ('cust_1', 45, 451, '2018-04-01');
INSERT INTO cust VALUES ('cust_1', 45, 150, '2018-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  87, '2018-05-28');
INSERT INTO cust VALUES ('cust_1', 45,  93, '2018-06-28');
INSERT INTO cust VALUES ('cust_1', 45,  56, '2018-07-28');
INSERT INTO cust VALUES ('cust_1', 45, 400, '2018-08-28');
INSERT INTO cust VALUES ('cust_1', 45, 234, '2017-01-28');
INSERT INTO cust VALUES ('cust_1', 45, 432, '2017-02-28');
INSERT INTO cust VALUES ('cust_1', 45, 657, '2017-03-28');
INSERT INTO cust VALUES ('cust_1', 45,  43, '2017-04-28');
INSERT INTO cust VALUES ('cust_1', 45,  32, '2017-05-28');
INSERT INTO cust VALUES ('cust_1', 45, 111, '2017-06-28');

WITH cte1 AS
(
  SELECT
    CASE
      WHEN DATE_PART('MONTH', NOW()) >= 4 
        THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT,     '9999')     || '04' || '01', 'YYYYMMDD')
        ELSE TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT - 1, '9999')  || '04' || '01', 'YYYYMMDD')
    END AS start_tax_year
)
SELECT * FROM cust, cte1
WHERE ord_date <= NOW() AND ord_date >= cte1.start_tax_year
ORDER BY ord_date

(There's a dbfiddle here)

I then ran 2 queries varying this line:

WHEN DATE_PART('MONTH', NOW()) >= 4 -- also used < 4 
                                   -- to simulate a previous year.

Results with >= 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45     450  2018-04-01     2018-04-01
   cust_1         45     150  2018-04-28     2018-04-01

Only two records - which is to be expected since we're only at the beginning of the tax year - just into May. A check shows that there are, in fact, only two records between today (May 10th) and Apr 1st.

Results with < 4

cust_code sugg_order ord_qty    ord_date start_tax_year
   cust_1         45      43  2017-04-28     2017-04-01
   cust_1         45      32  2017-05-28     2017-04-01
   cust_1         45     111  2017-06-28     2017-04-01
   cust_1         45     450  2018-01-28     2017-04-01
   cust_1         45     350  2018-02-28     2017-04-01
   cust_1         45      50  2018-03-28     2017-04-01
   cust_1         45     451  2018-04-01     2017-04-01
   cust_1         45     150  2018-04-28     2017-04-01

8 records - again to be expected, as the period between now and Apr 1st 2017 covers most of the data. If the date as sometime late in 2017, it would pick up the records between then and Apr 1st 2017.

The query is rather convoluted:

THEN TO_DATE(TO_CHAR(DATE_PART('YEAR', NOW())::INT, '9999')     || '04' || '01', 'YYYYMMDD')

The whole point of the CTE is to get April 1st of the correct tax year - i.e. the one we're in at the moment, even if we've gone into the next calendar year.

1) get DATE_PART from NOW() cast as an INT when we're beyond April first in the year where the query is being performed. This becomes

DATE_PART('YEAR', NOW())::INT - 1, '9999')

if the month part of the current year is < 4, it's before April, so you have to go back to April 1st of the previous year - which is where the -1 comes in.

2) once you've got the correct year for April 1st, then all the rest is the same. You then use TO_CHAR to transform this into a string and then using the double pipe characters (||) to concatenate the month (04) and day (01) to the correct year. Then use TO_DATE to convert the correct string (20180401) using the format string 'YYYYMMDD'.

3) then use the CTE to feed this value into the main query - obtain the desired records. Et voilà.

Answer 3

The below query worked for my problem:

WITH get_year AS (SELECT
  CASE
  WHEN extract(MONTH FROM current_timestamp) < 4
  THEN extract(YEAR FROM  current_timestamp) - 1
  ELSE extract(YEAR FROM  current_timestamp)
  END)

  SELECT sugg_order, ordered_qty, date from table_1 t, get_year
  WHERE t.cust_id = $1
  AND t.date >= make_date(get_year.date_part::integer, 4, 1) and t.date <= current_timestamp
  ORDER BY t.date DESC