2

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?

airliquide
  • 520
  • 7
  • 16

1 Answers1

2

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.

Guillaume F.
  • 5,905
  • 2
  • 31
  • 59
  • 1
    That's right ! Many thanks. – airliquide Oct 20 '21 at 13:44
  • 2
    ... and the returned timezone offset is considering the daylight saving, so the offset is not not the same between the `date_fin` and the computed 1st day of the month. ([ref](https://stackoverflow.com/questions/38785924/postgresql-date-trunc-with-time-zone-shifts-zone-by-1-hr)) – JGH Oct 20 '21 at 14:26