Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #18883: Epoch issue

BUG #18883: Epoch issue

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18883
Logged by:          RAVI KANT BHUSHAN
Email address:      ravi105173@gmail.com
PostgreSQL version: 16.1
Operating system:   Windows 10
Description:

Number of days calculation using EPOCH is not providing right answer:

Query:      SELECT (EXTRACT(EPOCH FROM ('2025-04-08 11:25:01'::timestamp -
'2025-03-13 15:33:30'::timestamp)) / 86400)::INT   
                    ==> output is 26, but right answer is 25 days

Re: BUG #18883: Epoch issue

From
"David G. Johnston"
Date:
On Tuesday, April 8, 2025, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      18883
Logged by:          RAVI KANT BHUSHAN
Email address:      ravi105173@gmail.com
PostgreSQL version: 16.1
Operating system:   Windows 10
Description:       

Number of days calculation using EPOCH is not providing right answer:

Query:      SELECT (EXTRACT(EPOCH FROM ('2025-04-08 11:25:01'::timestamp -
'2025-03-13 15:33:30'::timestamp)) / 86400)::INT   
                    ==> output is 26, but right answer is 25 days


select 1.75::int; — yields 2

We don’t document the precise rules for what casting does but rounding seems reasonable.  Use the floor function to get your expected answer.

David J.