Postgres March stange results-CodePudding

I'm tring to calculate time prorata between two dates over multi month periods using SQL on PostgresSQL.

My formula is pretty simple

select iot.*, 
       (iot.DATE_FIN - iot.DATE_DEBUT)::double precision / NULLIF(  extract (day from iot.DATE_FIN - date_trunc('month', iot.DATE_DEBUT)), 0::double precision) as prorata
from (
  select DATE '2020-05-01' as DATE_DEBUT,   DATE '2020-05-31' as DATE_FIN
) iot

Give me expected result => 1

But when I switch to march with

select iot.*,   
       (iot.DATE_FIN - iot.DATE_DEBUT)::double precision / NULLIF(extract (day from iot.DATE_FIN - date_trunc('month', iot.DATE_DEBUT)), 0::double precision) as prorata
from (
   select DATE '2020-03-01' as DATE_DEBUT, DATE '2020-03-31' as DATE_FIN
) iot

I've got a weird result of 1.0344827586206897

Any ideas?

CodePudding user response：

This is caused by date_trunc('month', this function returns a date with a timezone, but your whole calculus doesn't handle the timezone.

If you force Postgres to ignore the timezone, it should work:

select iot.*,   
       (iot.DATE_FIN - iot.DATE_DEBUT)::double precision / NULLIF(extract (day from iot.DATE_FIN - date_trunc('month', iot.DATE_DEBUT)::timestamp without time zone), 0::double precision) as prorata
from (
   select DATE '2020-03-01' as DATE_DEBUT, DATE '2020-03-31' as DATE_FIN
) iot

I just cast date_trunc with ::timestamp without time zone.