From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ludwig Isaac Lim <ludz_lim(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Strange behavior between timestamp and date comparison |
Date: | 2022-07-24 15:33:44 |
Message-ID: | 0fcbe18f-5fd4-13e2-b827-3f25ec9563c4@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2022-07-24 16:29:06 | Re: Queries in another user's tables |
Previous Message | Rory Campbell-Lange | 2022-07-24 15:29:53 | Re: Strange behavior between timestamp and date comparison |