Обсуждение: [Bug Report] Week Day

Поиск
Список
Период
Сортировка

[Bug Report] Week Day

От
Atila Rangel
Дата:
Hi, I'm a software developer working with Postgresql database. Recently, I have had a issue with Week Day queries. Basically, I tried to filter some datetime based on day of week name. I'm sending a sql that you can reproduce the bug. This may not be a bug. But, I would like to report this.


--

Вложения

Re: [Bug Report] Week Day

От
Heikki Linnakangas
Дата:
On 19/02/2020 04:59, Atila Rangel wrote:
> Hi, I'm a software developer working with Postgresql database. Recently, 
> I have had a issue with Week Day queries. Basically, I tried to filter 
> some datetime based on day of week name. I'm sending a sql that you can 
> reproduce the bug. This may not be a bug. But, I would like to report this.

The to_char() function pads the weekday names with spaces, as you can 
see with this query:

postgres=# select *, 'X' || to_char(some_date, 'day')::text || 'X' from 
postgres_report_bug limit 5;
  id |       some_date        |  ?column?
----+------------------------+-------------
   1 | 2019-11-11 00:00:00+02 | Xmonday   X
   2 | 2019-11-11 01:00:00+02 | Xmonday   X
   3 | 2019-11-11 02:00:00+02 | Xmonday   X
   4 | 2019-11-11 03:00:00+02 | Xmonday   X
   5 | 2019-11-11 04:00:00+02 | Xmonday   X
(5 rows)

You can use 'FMday' instead of 'day' to avoid that.

- Heikki

Re: [Bug Report] Week Day

От
Frank Heikens
Дата:
It’s not a bug, this is how to_char() works. From the manual: 
day full lower case day name (blank-padded to 9 chars)

It returns 9 characters. And only wednesday is without blanks. Change your where condition to solve the problem.

WITH testcases AS (
SELECT dd
FROM generate_series('2019-11-11'::timestamp, now(), '1 day'::interval) dd
)
SELECT 
dd, to_char(dd, 'day'), length(to_char(dd, 'day'))
FROM testcases
WHERE to_char(dd, 'day') IN('monday   ','tuesday  ')
OR to_char(dd, 'day') =ANY('{"friday   "}'::text[]);

Abraço,
Frank


On 19 Feb 2020, at 03:59, Atila Rangel <atila.rangel@cyberlabs.com.br> wrote:

Hi, I'm a software developer working with Postgresql database. Recently, I have had a issue with Week Day queries. Basically, I tried to filter some datetime based on day of week name. I'm sending a sql that you can reproduce the bug. This may not be a bug. But, I would like to report this.


--

<postgres_bug_report.sql>

Re: [Bug Report] Week Day

От
hubert depesz lubaczewski
Дата:
On Tue, Feb 18, 2020 at 11:59:58PM -0300, Atila Rangel wrote:
> Hi, I'm a software developer working with Postgresql database. Recently, I
> have had a issue with Week Day queries. Basically, I tried to filter some
> datetime based on day of week name. I'm sending a sql that you can
> reproduce the bug. This may not be a bug. But, I would like to report this.

It's not a bug. It is described in
https://www.postgresql.org/docs/current/functions-formatting.html :

day: full lower case day name (blank-padded to 9 chars)

you most likely want to use to_char(some_date, 'FMday')

depesz