Re: Query not producing expected result

Adrian:

On Wed, May 1, 2019 at 7:57 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> You will have to explain further as I am not seeing it:
> test_(postgres)# select '2019-05-01 9:52' <= '2019-05-01 24:00'::timestamp;
> ?column?
> ----------
> t
>
> test_(postgres)# select '2019-05-01 24:00' <= '2019-05-01 24:00'::timestamp;
> ?column?
> ----------
> t

Because you are using two selected examples. The one with 9:52 is ok.

The last one is misleading because you are using a constant for a
particular timestamp in MAY THE SECOND wich can be written to look
like it is in MAY THE FIRST.

Rewrite it as
select '2019-05-02'::timestamp <= '2019-05-01 24:00'::timestamp;

And you'll see and out of range date selected.

This is why <= AND 24:00 are bad and misleading.

You may not have problems with 00:00:00 times, but work a bit billing
phone calls and you'll find about one in 86400 hit it ( more in my
case as traffic distribution is skewed ). Use that kind of condition
and you end up chasing why the monthly report has a dozen less calls
than the sum of the daily ones the billing guys made using excel.

Francisco Olarte.