Re: Strange behavior between timestamp and date comparison

On 23/07/22, Ludwig Isaac Lim (ludz_lim@yahoo.com) wrote:
> 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 doesn't return anything (unexpected)
>  select * from ts where t between '2022-07-16'::Date - make_interval(days => 30) and '2022-07-16'::Date;

It looks like all of your timestamps are outside of the upper bound of "between".

template1=> select ('2022-07-16'::Date)::timestamp;
      timestamp
---------------------
 2022-07-16 00:00:00