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