Postgres generate_series how to exclude last day when hour is 00:00

Question

I need to generate a series of days in postgresql that would produce different result depending on the hours in the timestamp.

My series generation works fine when the time is not midnight. For time range 2023-01-06 10:00:00+00 - 2023-02-03 10:00:00+00 I get a list of days where the first element is 2023-01-06 and the last is 2023-02-03. This works as expected:

generate_series('2023-01-06 10:00:00+00'::date, '2023-02-03 10:00:00+00'::date, '1 day')

However, for time range 2023-01-06 00:00:00+00 - 2023-02-03 00:00:00+00 I would like to get a list of days where the first element is 2023-01-06 and the last is 2023-02-02 as effectively 2023-02-03 hasn't started. That series still gives me an output that includes 2023-02-03, which is not what I want:

generate_series('2023-01-06 00:00:00+00'::date, '2023-02-03 00:00:00+00'::date, '1 day')

Is that possible to achieve in postgres?

Answer 1

you could check if ot os midnight and then subtract 1 Minute or 1 second from the end date

SELECt * 
  FROM generate_series('2023-01-06 00:00:00+00'::date,
  (CASE WHEN to_char('2023-02-03 00:00:00+00'::date, 'HH24:MI:SS') = '00:00:00' THEN 
  '2023-02-03 00:00:00+00'::date - interval '1 Minute'
  ELSE '2023-02-03 00:00:00+00'::date END) , '1 day')
 

generate_series
2023-01-06 00:00:00
2023-01-07 00:00:00
2023-01-08 00:00:00
2023-01-09 00:00:00
2023-01-10 00:00:00
2023-01-11 00:00:00
2023-01-12 00:00:00
2023-01-13 00:00:00
2023-01-14 00:00:00
2023-01-15 00:00:00
2023-01-16 00:00:00
2023-01-17 00:00:00
2023-01-18 00:00:00
2023-01-19 00:00:00
2023-01-20 00:00:00
2023-01-21 00:00:00
2023-01-22 00:00:00
2023-01-23 00:00:00
2023-01-24 00:00:00
2023-01-25 00:00:00
2023-01-26 00:00:00
2023-01-27 00:00:00
2023-01-28 00:00:00
2023-01-29 00:00:00
2023-01-30 00:00:00
2023-01-31 00:00:00
2023-02-01 00:00:00
2023-02-02 00:00:00

SELECT 28

fiddle