pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed[Bug Report] Week Day
4+ messages / 4 participants
[nested] [flat]
* [Bug Report] Week Day
@ 2020-02-19 02:59 Atila Rangel <atila.rangel@cyberlabs.com.br>
0 siblings, 3 replies; 4+ messages in thread
From: Atila Rangel @ 2020-02-19 02:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
--
Attachments:
postgres_bug_report.sql (application/sql, 729B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [Bug Report] Week Day
@ 2020-02-19 09:07 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Atila Rangel <atila.rangel@cyberlabs.com.br>
2 siblings, 0 replies; 4+ messages in thread
From: Heikki Linnakangas @ 2020-02-19 09:07 UTC (permalink / raw)
To: Atila Rangel <atila.rangel@cyberlabs.com.br>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [Bug Report] Week Day
@ 2020-02-19 09:07 Frank Heikens <frankheikens@mac.com>
parent: Atila Rangel <atila.rangel@cyberlabs.com.br>
2 siblings, 0 replies; 4+ messages in thread
From: Frank Heikens @ 2020-02-19 09:07 UTC (permalink / raw)
To: Atila Rangel <atila.rangel@cyberlabs.com.br>; +Cc: pgsql-bugs@lists.postgresql.org
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 <mailto: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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [Bug Report] Week Day
@ 2020-02-19 10:47 hubert depesz lubaczewski <depesz@depesz.com>
parent: Atila Rangel <atila.rangel@cyberlabs.com.br>
2 siblings, 0 replies; 4+ messages in thread
From: hubert depesz lubaczewski @ 2020-02-19 10:47 UTC (permalink / raw)
To: Atila Rangel <atila.rangel@cyberlabs.com.br>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-02-19 10:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-19 02:59 [Bug Report] Week Day Atila Rangel <atila.rangel@cyberlabs.com.br>
2020-02-19 09:07 ` Heikki Linnakangas <hlinnaka@iki.fi>
2020-02-19 09:07 ` Frank Heikens <frankheikens@mac.com>
2020-02-19 10:47 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox