Juan Carlos Oropeza Juan Carlos Oropeza - 7 months ago 20
SQL Question

How remove the last item on generate_series sequence

I try create 10 min ranges for every day on 2016.

SELECT i::timestamp date_start,
i::timestamp + '599 second'::interval date_end,
0::bit download
FROM generate_series('2016-01-01',
'2017-01-01',
'10 minute'::interval) i;


enter image description here

There are 366 days in 2016 so
366 days * 24 hours * 6 interval = 52704


The last one row belong to 2017

I could just delete the last row, but want fix the query in case want to run it again in 2017.

Answer

How about subtracting 10 minutes from the end of the interval?

SELECT i::timestamp date_start, 
       i::timestamp + '599 second'::interval date_end,
       0::bit download
FROM generate_series('2016-01-01', 
                     '2017-01-01'::timestamp - interval '10 minute', 
                     '10 minute'::interval
                    ) i
Comments