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.klaver@aklaver.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.klaver@aklaver.com