Postgres Pro
Downloads
Home   >   mailing lists

Thread: Strange behavior between timestamp and date comparison

Strange behavior between timestamp and date comparison

From
Ludwig Isaac Lim
Date:
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

Re: Strange behavior between timestamp and date comparison

From
Rory Campbell-Lange
Date:
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

Re: Strange behavior between timestamp and date comparison

From
Adrian Klaver
Date:
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@aklaver.com