On 7/23/22 03:04, Ludwig Isaac Lim wrote:
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)
Because:
select '2022-07-16 00:22:06.974'::date;
date
------------
2022-07-16
select '2022-07-16 00:22:06.974'::timestamp;
timestamp
-------------------------
2022-07-16 00:22:06.974
and:
select '2022-07-16'::date::timestamp;
timestamp
---------------------
2022-07-16 00:00:00
When you normalize all the values to a date it works e.g. t::date.
When you don't then the date values in the between get compared as
timestamps and 2022-07-16 00:22:06.974 is greater then 2022-07-16 00:00:00
-- 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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
