Re: Query not producing expected result

On 5/1/19 11:04 AM, Francisco Olarte wrote:
> 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.

Technically it is correct as:

test_(postgres)# select '2019-05-02'::timestamp;
timestamp
---------------------
2019-05-02 00:00:00

which is Midnight and is both the end of one day and start of another.

It comes down to where you want to draw the line between days.

>
> 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.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com