darshil929 opened a new issue, #27212:
URL: https://github.com/apache/datafusion/issues/27212
### Describe the bug
`extract(epoch from <interval>)` returns the wrong number of seconds once an
interval reaches one year. DataFusion treats every 12 months as 360 days (12
months of 30 days each). PostgreSQL and DuckDB count each whole year as 365.25
days and only use 30 days for the months left over, so for a one year interval
DataFusion comes out 5.25 days short.
Intervals shorter than a year give the same answer in all three.
### To Reproduce
In `datafusion-cli` built from main:
```sql
SELECT s AS input, extract(epoch FROM i) AS epoch_seconds
FROM (VALUES
('11 months', INTERVAL '11 months'),
('1 year', INTERVAL '1 year'),
('14 months', INTERVAL '14 months'),
('-14 months', INTERVAL '-14 months'),
('1 year 1 day', INTERVAL '1 year 1 day')
) AS t(s, i);
```
```
+--------------+---------------+
| input | epoch_seconds |
+--------------+---------------+
| 11 months | 28512000.0 |
| 1 year | 31104000.0 |
| 14 months | 36288000.0 |
| -14 months | -36288000.0 |
| 1 year 1 day | 31190400.0 |
+--------------+---------------+
```
The same query in PostgreSQL 18:
```
input | epoch_seconds
--------------+------------------
11 months | 28512000.000000
1 year | 31557600.000000
14 months | 36741600.000000
-14 months | -36741600.000000
1 year 1 day | 31644000.000000
(5 rows)
```
DuckDB returns the same numbers as PostgreSQL for every row.
The `Interval(YearMonth)` type behaves the same way:
```sql
SELECT extract(epoch FROM arrow_cast('1 year', 'Interval(YearMonth)'));
-- 31104000.0
```
### Expected behavior
Whole years in an interval should count as 365.25 days and the remaining
months as 30 days each, the way PostgreSQL and DuckDB do it. With that, `1
year` would return 31557600 and `14 months` would return 36741600 (365.25 days
plus 60 days). Results for intervals under a year would stay as they are.
### Additional context
The interval conversion is in `epoch()` in `date_part.rs`. Its comment says
the 30 day month follows PostgreSQL. PostgreSQL does use 30 days for the
leftover months, but it counts the whole years in an interval as 365.25 days,
while DataFusion currently applies the 30 day rule to all of the months.
I'd like to work on the fix, with sqllogictest coverage for these cases,
once we've settled on an approach.
### Willingness to contribute
I would like to contribute a fix for this bug, but request guidance from the
DataFusion community
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
