Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j4LKc-0004gm-0A for pgsql-bugs@arkaria.postgresql.org; Wed, 19 Feb 2020 09:07:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j4LKa-00045F-M4 for pgsql-bugs@arkaria.postgresql.org; Wed, 19 Feb 2020 09:07:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j4LKa-000453-Bu for pgsql-bugs@lists.postgresql.org; Wed, 19 Feb 2020 09:07:44 +0000 Received: from ms11p00im-hyfv17291101.me.com ([17.58.38.40]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1j4LKY-0003rX-EF for pgsql-bugs@lists.postgresql.org; Wed, 19 Feb 2020 09:07:43 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=1a1hai; t=1582103257; bh=5rOyWu6Ml4bfciUcG/eFTVYOAhGgql6kngtc8fPVH0U=; h=From:Message-Id:Content-Type:Subject:Date:To; b=fuzDAjKU5YeRKrY5PFP0l1Vi1bBwDHweOOfQCCWgTaY48fHqiUcy1joqUFcWfSFPn qezQMMTX71Jtt9ESUmNhknpEMvTZd2Um4yp78+0cwLQWplxJK1rD5e8aEzQ5etZxEH B5XM7AemB52eJS5+okSXiAjXKRmo2XzrXE3hmQg8vPRcz7LdaSqOg7RFZwDTsE4oUl x/5pzlkj1AnB8yi8cvIybUYcaV01546Z9V/1Yl6o93DKnfn7hx0wga0WHd9QAN9FKi 6gf4Uf67hmhkBz0wGDKt09gKevivzPCiSO9jh/xcdL6D2uItj9mLrXQAPmujO4E6xT Ei5GwNBVmbk0A== Received: from [192.168.178.101] (94-209-183-163.cable.dynamic.v4.ziggo.nl [94.209.183.163]) by ms11p00im-hyfv17291101.me.com (Postfix) with ESMTPSA id D0A616C0510; Wed, 19 Feb 2020 09:07:36 +0000 (UTC) From: Frank Heikens Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_C657610B-8445-4953-801A-200018D2E789" Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\)) Subject: Re: [Bug Report] Week Day Date: Wed, 19 Feb 2020 10:07:34 +0100 In-Reply-To: Cc: pgsql-bugs@lists.postgresql.org To: Atila Rangel References: X-Mailer: Apple Mail (2.3445.104.11) X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2020-02-19_02:,, signatures=0 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 suspectscore=0 malwarescore=0 phishscore=0 bulkscore=0 spamscore=0 clxscore=1011 mlxscore=0 mlxlogscore=998 adultscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1908290000 definitions=main-2002190069 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_C657610B-8445-4953-801A-200018D2E789 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 It=E2=80=99s not a bug, this is how to_char() works. =46rom the manual:=20= 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=20 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') =3DANY('{"friday "}'::text[]); Abra=C3=A7o, Frank > On 19 Feb 2020, at 03:59, Atila Rangel > wrote: >=20 > 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. >=20 >=20 > --=20 >=20 > --Apple-Mail=_C657610B-8445-4953-801A-200018D2E789 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
It=E2=80=99s not a = bug, this is how to_char() works. =46rom 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') =3DANY('{"friday   = "}'::text[]);

Abra=C3=A7o,
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>
=
= --Apple-Mail=_C657610B-8445-4953-801A-200018D2E789--