From: | Ludwig Isaac Lim <ludz_lim(at)yahoo(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Strange behavior between timestamp and date comparison |
Date: | 2022-07-23 10:04:24 |
Message-ID: | 1485906141.916850.1658570664512@mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello:
Below is a sample case that exhibits a behavior that I can't explain:
-- create the table
create table ts (t timestamp without time zone);
-- populate
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
insert into ts(t) values ('2022-07-16 00:22:06.974000');
-- This one return expected results
select * from ts where t::date between '2022-07-16'::Date - make_interval(days => 30) and '2022-07-16'::Date;
t
-------------------------
2022-07-16 00:22:06.974
2022-07-16 00:22:06.974
2022-07-16 00:22:06.974
(3 rows)
-- This one doesn't return anything (unexpected)
select * from ts where t between '2022-07-16'::Date - make_interval(days => 30) and '2022-07-16'::Date;
t
---
(0 rows)
-- version
select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-15), 64-bit
(1 row)
Regards,
Ludwig Lim
From | Date | Subject | |
---|---|---|---|
Next Message | Martin Kalcher | 2022-07-23 12:20:47 | Re: [PATCH] Introduce array_shuffle() and array_sample() |
Previous Message | Rory Campbell-Lange | 2022-07-23 09:09:40 | Logical replication versus pglogical on PostgreSQL 14 |