[Bug Report] Week Day

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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


--


postgres_bug_report.sql (996 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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


Reply | Threaded
Open this post in threaded view
|

Re: [Bug Report] Week Day

Frank Heikens-3
In reply to this post by Atila Rangel
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 <[hidden email]> 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>

Reply | Threaded
Open this post in threaded view
|

Re: [Bug Report] Week Day

hubert depesz lubaczewski-2
In reply to this post by Atila Rangel
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