computing dT from an interval

From: "Michael J(dot) Baars" <mjbaars1977(dot)pgsql-hackers(at)cyberfiber(dot)eu>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: computing dT from an interval
Date: 2021-02-20 09:27:20
Message-ID: 510361c1d852bc654885ba3e215b1c2b4f5b9281.camel@cyberfiber.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

Can someone please tell me which of these two queries gives the correct result and which one the incorrect?

/* * * *
* dT in days for 1000 samples
*/

// 2.922 (&)
with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from "00" ) +
extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) / 24 as dT from A1;

// 2.88 (X)
with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes from "00" ) /
1440 + extract ( seconds from "00" ) / 86400 as dT from A1;

Personally I think only the first one gives the correct answer.

Best regards,
Mischa.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2021-02-20 09:28:05 Re: repeated decoding of prepared transactions
Previous Message Joel Jacobson 2021-02-20 09:19:04 Re: Some regular-expression performance hacking