[Bug Report] Week Day

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)

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 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