1

I don't know if it's a bug or it's intended, but the simple query

select generate_series('2021-03-28'::date, '2021-03-29'::date - interval '1 minute', interval '1 hour') as date_hour;

> 2021-03-28 00:00:00.000
  2021-03-28 01:00:00.000
  2021-03-28 03:00:00.000 <---
  2021-03-28 03:00:00.000 <---
  2021-03-28 04:00:00.000
  2021-03-28 05:00:00.000
  2021-03-28 06:00:00.000
  2021-03-28 07:00:00.000
  2021-03-28 08:00:00.000
  2021-03-28 09:00:00.000
  2021-03-28 10:00:00.000
  2021-03-28 11:00:00.000
  2021-03-28 12:00:00.000
  2021-03-28 13:00:00.000
  2021-03-28 14:00:00.000
  2021-03-28 15:00:00.000
  2021-03-28 16:00:00.000
  2021-03-28 17:00:00.000
  2021-03-28 18:00:00.000
  2021-03-28 19:00:00.000
  2021-03-28 20:00:00.000
  2021-03-28 21:00:00.000
  2021-03-28 22:00:00.000
  2021-03-28 23:00:00.000

generates a duplicate row 2021-03-28 03:00:00.000, as you can also see in the picture below.

What I'm missing here?

enter image description here

select version();

> PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit

UPDATE

Despite the result, under the scene it looks like the values are correctly different. I wonder if it's just a representation issue.

select date_trunc('hour', date_hour), extract(epoch from date_hour), count(*)
from (
    select generate_series('2021-03-28'::date, '2021-03-29'::date - interval '1 minute', interval '1 hour') as date_hour
) s
group by date_trunc('hour', date_hour), extract(epoch from date_hour)
order by date_trunc('hour', date_hour), extract(epoch from date_hour)

enter image description here

Improve this question
15
  • You need until 29 day ? Commented Aug 23, 2022 at 7:47
  • @Nickname_used I've edited the question in order to list the full result. I need only day 28. 24 rows total, from time 00:00 to time 23:00. The result is effectively 24 rows, but the time 02:00 is skipped and 03:00 is replicated. Commented Aug 23, 2022 at 7:51
  • 1
    In my pgsql same problem Commented Aug 23, 2022 at 7:57
  • 5
    I think this is a timezone-handling bug: 28th March 2021 was the day that Summer Time began in most of Europe, so at what would have been 2am the clocks jumped forward to 3am. That explains the skip, but not the repeat. Commented Aug 23, 2022 at 7:58
  • 1
    Using timestamptzinsetad of date, I get the output I would expect for a forward clock change: 1AM followed by 3AM, then 4AM. Commented Aug 23, 2022 at 9:03
Related questions
4
Postgres generate_series
2
generate_series in MySQL
0
sum and generate series does't work in postgresql
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.