Skip to content

EXTRACT EPOCH inconsistently extracts fractional seconds #26673

Closed
Bug
#26676
Closed
EXTRACT EPOCH inconsistently extracts fractional seconds#26673
Bug
#26676
Assignees
mbutrovich
Labels
bugSomething isn't working

Description

@alamb
alamb
opened on Oct 23, 2026

Describe the bug

When running EXTRACT EPOCH sometimes DataFusion extracts the fractional seconds, but sometimes does not

So extracting the epoch from 2.10 seconds gives 2.1 as expected, but extracting the epoc from 2.50 seconds returns `2.0 (dropping the fraction incorrectly)

To Reproduce

Reproducer

 WITH cases (
      description,
      started_at,
      finished_at,
      expected_seconds
  ) AS (
      VALUES
          ('1.25 seconds: fraction retained',
           TIMESTAMP '2025-01-01 00:00:00.000000',
           TIMESTAMP '2025-01-01 00:00:01.250000',
           1.250000),

          ('2.10 seconds: fraction retained',
           TIMESTAMP '2025-01-01 00:00:00.000000',
           TIMESTAMP '2025-01-01 00:00:02.100000',
           2.100000),

          ('2.50 seconds: fraction dropped',
           TIMESTAMP '2025-01-01 00:00:00.000000',
           TIMESTAMP '2025-01-01 00:00:02.500000',
           2.500000),

          ('4.763 seconds: fraction dropped',
           TIMESTAMP '2025-01-01 00:00:00.000000',
           TIMESTAMP '2025-01-01 00:00:04.763000',
           4.763000),

          ('12.923350 seconds: fraction dropped',
           TIMESTAMP '2025-01-01 00:00:00.000000',
           TIMESTAMP '2025-01-01 00:00:12.923350',
           12.923350)
  )
  SELECT
      description,
      finished_at - started_at AS elapsed,
      EXTRACT(EPOCH FROM (finished_at - started_at)) AS actual_seconds,
      expected_seconds
  FROM cases;

Results in

+-------------------------------------+-----------------------------------------+----------------+------------------+
| description                         | elapsed                                 | actual_seconds | expected_seconds |
+-------------------------------------+-----------------------------------------+----------------+------------------+
| 1.25 seconds: fraction retained     | 0 days 0 hours 0 mins 1.250000000 secs  | 1.25           | 1.25             |
| 2.10 seconds: fraction retained     | 0 days 0 hours 0 mins 2.100000000 secs  | 2.1            | 2.1              |
| 2.50 seconds: fraction dropped      | 0 days 0 hours 0 mins 2.500000000 secs  | 2.0            | 2.5              |
| 4.763 seconds: fraction dropped     | 0 days 0 hours 0 mins 4.763000000 secs  | 4.0            | 4.763            |
| 12.923350 seconds: fraction dropped | 0 days 0 hours 0 mins 12.923350000 secs | 12.0           | 12.92335         |
+-------------------------------------+-----------------------------------------+----------------+------------------+
5 row(s) fetched.
Elapsed 0.013 seconds.

DataFusion CLI v55.1.0 produces:

1.25 seconds → actual 1.25, expected 1.25
2.10 seconds → actual 2.1, expected 2.1
2.50 seconds → actual 2.0, expected 2.5
4.763 seconds → actual 4.0, expected 4.763
12.923350 seconds → actual 12.0, expected 12.92335

Expected behavior

The fractional second part should be extracted consistently;

Additional context

THis seems similar to

Willingness to contribute

None

Activity

  1. alamb
    added
    bugSomething isn't working
    on Oct 23, 2026
  2. alamb
    added the
    Bug
    issue type on Oct 23, 2026
  3. github-actions
    assigned
    Kristofferjoha
    on Oct 23, 2026

  4. mbutrovich commented on Oct 23, 2026

    @mbutrovich
    mbutrovich
    on Oct 23, 2026
    Contributor

    Thanks @alamb for filing this. The cutoff is 2.147483647 seconds, which is i32::MAX nanoseconds. That matches your cases (2.10 keeps its fraction, 2.50 loses it):

    SELECT d, EXTRACT(EPOCH FROM d) AS epoch
FROM (VALUES
  (arrow_cast(2147483647, 'Duration(ns)')),
  (arrow_cast(2147483648, 'Duration(ns)'))
) AS v(d);
    | 0 days 0 hours 0 mins 2.147483647 secs | 2.147483647 |
| 0 days 0 hours 0 mins 2.147483648 secs | 2.0         |

    As I read it, epoch sends Duration to seconds, which reads date_part(array, Nanosecond) as an Int32 array. For a Duration(ns), arrow-rs returns the total nanoseconds with try_into().ok(), so the value is NULL above i32::MAX. seconds treats a NULL sub-second part as zero. I have not stepped through it in a debugger. This looks like the same Int32 overflow on Duration as #14738.

    EXTRACT(EPOCH FROM INTERVAL '4.322 seconds') returns 4.322, so only the Duration path is affected. The same happens for Duration(µs) and Duration(ms) inputs.

  5. Kristofferjoha commented on Oct 23, 2026

    @Kristofferjoha
    Kristofferjoha
    on Oct 23, 2026
    Contributor

    5. Thanks @alamb for filing this. The cutoff is 2.147483647 seconds, which is i32::MAX nanoseconds. That matches your cases (2.10 keeps its fraction, 2.50 loses it):
    SELECT d, EXTRACT(EPOCH FROM d) AS epoch
    FROM (VALUES
    (arrow_cast(2147483647, 'Duration(ns)')),
    (arrow_cast(2147483648, 'Duration(ns)'))
    ) AS v(d);

    ```
| 0 days 0 hours 0 mins 2.147483647 secs | 2.147483647 |
| 0 days 0 hours 0 mins 2.147483648 secs | 2.0         |
```


    
      
    

      
    

    
  
As I read it, [`epoch`](https://github.com/apache/datafusion/blob/c76ed5396ad3f4a4dc9d9ceeddc419a146b871f9/datafusion/functions/src/datetime/date_part.rs#L592) sends `Duration` to [`seconds`](https://github.com/apache/datafusion/blob/c76ed5396ad3f4a4dc9d9ceeddc419a146b871f9/datafusion/functions/src/datetime/date_part.rs#L512-L524), which reads `date_part(array, Nanosecond)` as an `Int32` array. For a `Duration(ns)`, arrow-rs returns the total nanoseconds with [`try_into().ok()`](https://github.com/apache/arrow-rs/blob/df4dc3ede59bd43b5f71847231212381d068bb72/arrow-arith/src/temporal.rs#L774), so the value is NULL above `i32::MAX`. `seconds` treats a NULL sub-second part as zero. I have not stepped through it in a debugger. This looks like the same `Int32` overflow on `Duration` as [#14738](https://github.com/apache/datafusion/issues/14738).
`EXTRACT(EPOCH FROM INTERVAL '4.322 seconds')` returns `4.322`, so only the `Duration` path is affected. The same happens for `Duration(µs)` and `Duration(ms)` inputs.

    Trying to unassign myself, seems like you have it figured out

  6. Kristofferjoha commented on Oct 23, 2026

    @Kristofferjoha
    Kristofferjoha
    on Oct 23, 2026
    Contributor

    untake

  7. github-actions
    unassigned
    Kristofferjoha
    on Oct 23, 2026
  8. mbutrovich
    self-assigned this
    on Oct 23, 2026

  9. mbutrovich commented on Oct 23, 2026

    @mbutrovich
    mbutrovich
    on Oct 23, 2026
    Contributor
    Trying to unassign myself, seems like you have it figured out

    Okay I can try to knock this out.

  10. mbutrovich
    added a commit that references this issue on Oct 23, 2026

    test: check EXTRACT(EPOCH) on the reproducer from apache#26673.

    d1e23e0
  11. mbutrovich
    closed this as completedin #26676on Oct 23, 2026
  12. mbutrovich
    added a commit that references this issue on Oct 23, 2026

    fix: keep fractional seconds in EXTRACT(EPOCH) for Duration (apache#2…

    702803d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

Bug

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions