Postgres generate_series wrong result

92 Views Asked by At

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

0

There are 0 best solutions below