0

I have a query the following query : 

SELECT EXTRACT(YEAR FROM t::date),
       EXTRACT(MONTH FROM t::date),t::date+ interval '1 month'- t::date  
FROM  generate_series('2015-04-01', '2016-02-09', '1 month'::interval) t

The query gives the correct number of days for all the months, except for the month of Feb, 2016 ie, I expect to get 9 days and not 29 days as the result. What change should I make to the query above? Thanks

Anjali
  • 73
  • 3
  • 4
  • 13
  • generate_series(..., '1 month') is going to add one month to the first argument until it is larger than the second argument. I think if your first argument was not the first of the month, you'd see more is wrong than just the last row. What is the meaning of the calculation you are trying to make? – Chris Bandy Nov 22 '19 at 05:12
  • I am trying to generate a report based on the user's selection of date. And as per the example query above, I want dates from 1 through the 9th of Feb and the related data (not mentioned in the query above) only for those dates and not for the whole month of Feb,but for all the days in the months from April to Jan. – Anjali Nov 24 '19 at 20:59
  • Would you want "all the days in the months from April" if the first date is '2015-04-05'? Try a call to least: `SELECT LEAST(t + '1 month', '2016-02-09') - t FROM ...` https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-GREATEST-LEAST – Chris Bandy Nov 27 '19 at 03:57
  • Perfect! this is what i want.. but again, the total days for the month of Feb shows 8 days. Is there a way to get the expected result of 9 days without changing the date to `'2016-02-10'` – Anjali Dec 03 '19 at 20:41
  • I figured it out and came up with the following query. Also adding with reference to [this](https://stackoverflow.com/questions/14113469/generating-time-series-between-two-dates-in-postgresql/46499873#46499873) , I changed the query accordingly to ignore the Daylight Savings Time from the `generate_series(..)` Please have a look at the following query : `SELECT t, LEAST(t + '1 month', '2016-02-09'::date + interval '1 day') - t FROM generate_series(timestamp '2015-04-01', timestamp '2016-02-09', '1 month'::interval) t `. – Anjali Dec 03 '19 at 21:48

0 Answers0