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.

  • Would it also be correct that on 2018-05-09 you'd want all data from 2018-04-01 to 2018-05-09? or would it still be 2018-04-01 to 2018-05-01?
    – RDFozz
    May 9, 2018 at 17:02
  • 1
    I attempted to clarify your requirements based on my understanding of them; please feel free to roll back the edit if you think I got them wrong.
    – mustaccio
    May 9, 2018 at 17:55
  • It should be from 2018-04-01 to 2018-05-09 (all the date in between those two dates). May 9, 2018 at 17:56
  • I've completely revised my answer and I'm now 100% sure that it gives the correct answer for whatever the data and year may be. You might want to take a look?
    – Vérace
    May 10, 2018 at 16:49

3 Answers 3


I think you want something like this:

with param (date, year) as
  ( select 
        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 ; 
  • Can you please explain the query? I am not very familiar with PostgreSQL. May 10, 2018 at 4:51
  • I modified the above query just a bit and it worked for me. May 10, 2018 at 12:11

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
      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à.

  • The query suggested by you has given me an idea for another query. May 11, 2018 at 6:20
  • Can you give me an example of using with clause twice in the same SELECT query? Like you are using it once and I need a second CTE so that I can get some more data and instead of just one cust_id, I can get a list of them. May 11, 2018 at 6:26
  • And what's wrong with putting something like AND cust_id IN (... list of cust_ids...) into the WHERE clause? p.s. +1 for your more elegant solution.
    – Vérace
    May 11, 2018 at 7:45
  • Exactly what I am trying to do here but we need to get cust_ids dynamically and for calculating those I have a separate query. I am thinking of using that query as second CTE. I need suggestions for this. May 11, 2018 at 7:49
  • I think that might be a second question. Can you provide a fiddle with sample data and your desired result and then point back to this question to help give context? Take a look here.
    – Vérace
    May 11, 2018 at 7:54

The below query worked for my problem:

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

  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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.