Re: Strange behavior in generate_series(date, date, interval) with DST
От | Adrian Klaver |
---|---|
Тема | Re: Strange behavior in generate_series(date, date, interval) with DST |
Дата | |
Msg-id | 5484BD4D.2000009@aklaver.com обсуждение исходный текст |
Ответ на | Strange behavior in generate_series(date, date, interval) with DST (Sérgio Saquetim <sergiosaquetim@gmail.com>) |
Список | pgsql-general |
On 12/07/2014 12:11 PM, Sérgio Saquetim wrote: > I've noticed a strange behavior in the generate_series functions. > > I'm trying to get all days between a start and an end date including the > bounds. So naturally I've tried something like the query below > . > > The real query uses generate_series to join other tables and is much > more complicated, but for the sake of brevity, I think that this query > is good enough to show the problem. > : > > postgres=# SELECT generate_series('2014-10-10'::DATE, > > '2014-10-15'::DATE, '1 DAY'::INTERVAL); > generate_series > ------------------------ > 2014-10-10 00:00:00-03 > 2014-10-11 00:00:00-03 > 2014-10-12 00:00:00-03 > 2014-10-13 00:00:00-03 > 2014-10-14 00:00:00-03 > 2014-10-15 00:00:00-03 > > (6 rows) > > Please note that the upper bound > > '2014-10-15' is included in the resulting rows. > > Now if I try this same query with slightly different dates I get: > > postgres=# SELECT generate_series('2014-10-15'::DATE, > '2014-10-20'::DATE, '1 DAY'::INTERVAL); > generate_series > ------------------------ > 2014-10-15 00:00:00-03 > 2014-10-16 00:00:00-03 > 2014-10-17 00:00:00-03 > 2014-10-18 00:00:00-03 > 2014-10-19 01:00:00-02 > (5 rows) > > ----------------------------------------------- > The upper bound is not included in the results! > ----------------------------------------------- > > Here, in Brazil our DST started on Oct 19. So if I had to guess I would > say that this strange behavior is due to the DST, but I'm having a hard > time to understand why this is happening! > > Is this expected behavior? > > I know that I can achieve the results I expect with the following query: > > postgres=# WITH RECURSIVE days(d) AS ( > SELECT '2014-10-15'::DATE > UNION ALL > SELECT d+1 FROM days WHERE d < '2014-10-20'::DATE > ) > SELECT * FROM days; > d > ------------ > 2014-10-15 > 2014-10-16 > 2014-10-17 > 2014-10-18 > 2014-10-19 > 2014-10-20 > (6 rows) > > But using that instead of generate_series, just feels wrong so I would > like to understand what's happening and if there is a way to overcome > that, before changing my queries. First generate_series is really looking for a timestamp on input and returns timestamps: http://www.postgresql.org/docs/9.3/interactive/functions-srf.html generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step So: test=# set timezone='Brazil/East'; SET test=# SELECT generate_series('2014-10-10'::TIMESTAMP, '2014-10-20'::TIMESTAMP, '1 DAY'::INTERVAL); generate_series --------------------- 2014-10-10 00:00:00 2014-10-11 00:00:00 2014-10-12 00:00:00 2014-10-13 00:00:00 2014-10-14 00:00:00 2014-10-15 00:00:00 2014-10-16 00:00:00 2014-10-17 00:00:00 2014-10-18 00:00:00 2014-10-19 00:00:00 2014-10-20 00:00:00 test=# SELECT generate_series('2014-10-10'::TIMESTAMP WITH TIME ZONE, '2014-10-20'::TIMESTAMP WITH TIME ZONE, '1 DAY'::INTERVAL); generate_series ------------------------ 2014-10-10 00:00:00-03 2014-10-11 00:00:00-03 2014-10-12 00:00:00-03 2014-10-13 00:00:00-03 2014-10-14 00:00:00-03 2014-10-15 00:00:00-03 2014-10-16 00:00:00-03 2014-10-17 00:00:00-03 2014-10-18 00:00:00-03 2014-10-19 01:00:00-02 Though this part I do not understand: test=# select '2014-10-19'::timestamp with time zone; timestamptz ------------------------ 2014-10-19 01:00:00-02 (1 row) test=# select '2014-10-20'::timestamp with time zone; timestamptz ------------------------ 2014-10-20 00:00:00-02 (1 row) I thought interval understood 23/25 'day' across DST/ST boundaries. I will have to think more on this. > > Thank you! > > Sérgio Saquetim > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Sérgio SaquetimДата:
Сообщение: Strange behavior in generate_series(date, date, interval) with DST
Следующее
От: Adrian KlaverДата:
Сообщение: Re: Strange behavior in generate_series(date, date, interval) with DST