Thread: [Bug Report] Week Day
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.
--
Attachment
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
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)
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:<postgres_bug_report.sql>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.
--
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