fix: preserve fractional seconds in 'second' and 'millisecond' by aguilaredu · Pull Request #22504 · apache/datafusion

aguilaredu · 2026-05-24T23:06:03Z

Which issue does this PR close?

Closes PostgreSQL compatibility: extract(second ...) should preserve fractional seconds #22266
Closes PostgreSQL compatibility: extract(milliseconds ...) should preserve fractional milliseconds #22256

Rationale for this change

The change will achieve PostgreSQL-compatible semantics. More discussion can be found on #22266

What changes are included in this PR?

Update return types: EXTRACT(SECOND) and EXTRACT(MILLISECOND) now return f64 instead of i32 to preserve fractionals.
Improve numerical stability: Tweaked the existing seconds method calculation formula to prevent floating-point precision errors.
Fix Spark compatibility: Spark's date_part now delegates its return type to DataFusion's implementation rather than hardcoding i32.
Update tests: Adjusted existing test files for the new f64 return types and added new tests to the Spark suite for EXTRACT(MILLISECOND).

Are these changes tested?

Yes. Existing tests were updated to assert f64 returns, and new tests were added to the Spark suite specifically for EXTRACT(MILLISECOND)

Are there any user-facing changes?

Yes, the return type of EXTRACT(SECONDS) and EXTRACT(MILLISECONDS) is now f64 instead of i32.

aguilaredu · 2026-05-28T12:49:12Z

This change requires documentation changes. Specifically: https://datafusion.apache.org/user-guide/sql/scalar_functions.html#date-part
Before changing documentation I would like to get confirmation that the change is OK. There is an ongoing discussion in PostgreSQL compatibility: extract(second ...) should preserve fractional seconds #22266
This is my first contribution to the project so any feedback is highly welcome (even if it won't be merged)!

cc @Dandandan

github-actions · 2026-06-05T05:49:09Z

Thank you for opening this pull request!

Reviewer note: cargo-semver-checks reported the current version number is not SemVer-compatible with the changes in this pull request (compared against the base branch).

Details

     Cloning apache/main
    Building datafusion-functions v53.1.0 (current)
       Built [  32.665s] (current)
     Parsing datafusion-functions v53.1.0 (current)
      Parsed [   0.082s] (current)
    Building datafusion-functions v53.1.0 (baseline)
       Built [  28.219s] (baseline)
     Parsing datafusion-functions v53.1.0 (baseline)
      Parsed [   0.085s] (baseline)
    Checking datafusion-functions v53.1.0 -> v53.1.0 (no change; assume patch)
     Checked [   0.562s] 222 checks: 222 pass, 30 skip
     Summary no semver update required
    Finished [  65.000s] datafusion-functions
    Building datafusion-spark v53.1.0 (current)
       Built [  53.421s] (current)
     Parsing datafusion-spark v53.1.0 (current)
      Parsed [   0.062s] (current)
    Building datafusion-spark v53.1.0 (baseline)
       Built [  53.806s] (baseline)
     Parsing datafusion-spark v53.1.0 (baseline)
      Parsed [   0.062s] (baseline)
    Checking datafusion-spark v53.1.0 -> v53.1.0 (no change; assume patch)
     Checked [   0.425s] 222 checks: 219 pass, 3 fail, 0 warn, 30 skip

--- failure function_missing: pub fn removed or renamed ---

Description:
A publicly-visible function cannot be imported by its prior path. A `pub use` may have been removed, or the function itself may have been renamed or removed entirely.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/function_missing.ron

Failed in:
  function datafusion_spark::function::math::pow, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/spark/src/function/math/mod.rs:46
  function datafusion_spark::function::math::expr_fn::pow, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/spark/src/function/math/mod.rs:71
  function datafusion_spark::expr_fn::pow, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/spark/src/function/math/mod.rs:71

--- failure module_missing: pub module removed or renamed ---

Description:
A publicly-visible module cannot be imported by its prior path. A `pub use` may have been removed, or the module may have been renamed, removed, or made non-public.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/module_missing.ron

Failed in:
  mod datafusion_spark::function::math::pow, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/spark/src/function/math/pow.rs:18

--- failure struct_missing: pub struct removed or renamed ---

Description:
A publicly-visible struct cannot be imported by its prior path. A `pub use` may have been removed, or the struct itself may have been renamed or removed entirely.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/struct_missing.ron

Failed in:
  struct datafusion_spark::function::math::pow::SparkPow, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/spark/src/function/math/pow.rs:42

     Summary semver requires new major version: 3 major and 0 minor checks failed
    Finished [ 109.779s] datafusion-spark
    Building datafusion-sqllogictest v53.1.0 (current)
       Built [ 166.913s] (current)
     Parsing datafusion-sqllogictest v53.1.0 (current)
      Parsed [   0.023s] (current)
    Building datafusion-sqllogictest v53.1.0 (baseline)
       Built [ 168.743s] (baseline)
     Parsing datafusion-sqllogictest v53.1.0 (baseline)
      Parsed [   0.028s] (baseline)
    Checking datafusion-sqllogictest v53.1.0 -> v53.1.0 (no change; assume patch)
     Checked [   0.111s] 222 checks: 218 pass, 4 fail, 0 warn, 30 skip

--- failure feature_missing: package feature removed or renamed ---

Description:
A feature has been removed from this package's Cargo.toml. This will break downstream crates which enable that feature.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#cargo-feature-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/feature_missing.ron

Failed in:
  feature memory-accounting in the package's Cargo.toml

--- failure function_missing: pub fn removed or renamed ---

Description:
A publicly-visible function cannot be imported by its prior path. A `pub use` may have been removed, or the function itself may have been renamed or removed entirely.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/function_missing.ron

Failed in:
  function datafusion_sqllogictest::local_balance, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:196
  function datafusion_sqllogictest::set_default_budget, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:147
  function datafusion_sqllogictest::account_balance, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:189
  function datafusion_sqllogictest::set_account_balance, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:168
  function datafusion_sqllogictest::current_context_id, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:127
  function datafusion_sqllogictest::memory_tracker_limit, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:184
  function datafusion_sqllogictest::default_budget, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:153
  function datafusion_sqllogictest::next_context_id, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:99
  function datafusion_sqllogictest::settle_thread_local, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:202
  function datafusion_sqllogictest::set_thread_context_id, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:105
  function datafusion_sqllogictest::set_memory_tracker_limit, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:179
  function datafusion_sqllogictest::reset_account_to_default, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:162

--- failure pub_module_level_const_missing: pub module-level const is missing ---

Description:
A public const is missing or renamed
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/pub_module_level_const_missing.ron

Failed in:
  SLT_TARGET_PARTITIONS in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/test_context.rs:74

--- failure struct_missing: pub struct removed or renamed ---

Description:
A publicly-visible struct cannot be imported by its prior path. A `pub use` may have been removed, or the struct itself may have been renamed or removed entirely.
        ref: https://doc.rust-lang.org/cargo/reference/semver.html#item-remove
       impl: https://github.com/obi1kenobi/cargo-semver-checks/tree/v0.47.0/src/lints/struct_missing.ron

Failed in:
  struct datafusion_sqllogictest::OverdraftPanic, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:140
  struct datafusion_sqllogictest::AccountingAllocator, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting.rs:260
  struct datafusion_sqllogictest::AccountingMemoryPool, previously in file /home/runner/work/datafusion/datafusion/target/semver-checks/git-apache_main/93ef206f9e0a3bf9f01340b4b67d9749849b3bc1/datafusion/sqllogictest/src/accounting_pool.rs:45

     Summary semver requires new major version: 4 major and 0 minor checks failed
    Finished [ 339.227s] datafusion-sqllogictest

Adam-Alani · 2026-06-09T14:12:02Z

-                        if is_epoch(part) {
+                        if is_epoch(part) || is_fractional_second(part) {
                            Field::new(self.name(), DataType::Float64, nullable)
                        } else if is_nanosecond(part) {
                            // See notes on [seconds_ns] for rationale
                            Field::new(self.name(), DataType::Int64, nullable)
                        } else {
                            Field::new(self.name(), DataType::Int32, nullable)


I'd be in favor of dropping the conditional completely, and always returning float64, following Postgres semantics

comphead

Thanks @aguilaredu for the PR, I'm currently checking https://spark.apache.org/docs/latest/api/sql/#datepart

The second is finest fraction you can extract from the timestamp

Adam-Alani · 2026-07-29T08:51:13Z

Hey, I opened up a PR making date_part postgres compliant here #22851, lmk if thats okay. Thanks!

github-actions · 2026-09-28T01:52:53Z

Thank you for your contribution. Unfortunately, this pull request is stale because it has been open 60 days with no activity. Please remove the stale label or comment or this will be closed in 7 days.

github-actions Bot added sqllogictest SQL Logic Tests (.slt) functions Changes to functions implementation spark Changes to datafusion-spark crate labels May 24, 2026

feat: preserve fractionals in time extraction functions

e688270

aguilaredu force-pushed the bugfix/issue-22266-preseve-fractional-seconds branch from 80d035a to e688270 Compare May 28, 2026 12:14

Merge branch 'main' into bugfix/issue-22266-preseve-fractional-seconds

51d179d

aguilaredu marked this pull request as ready for review May 28, 2026 12:45

aguilaredu mentioned this pull request May 28, 2026

PostgreSQL compatibility: extract(second ...) should preserve fractional seconds #22266

Open

github-actions Bot added the auto detected api change Auto detected API change label Jun 5, 2026

Adam-Alani reviewed Jun 9, 2026

View reviewed changes

comphead reviewed Jun 10, 2026

View reviewed changes

Adam-Alani mentioned this pull request Jul 29, 2026

feat: make date_part always return Float64 #22851

Closed

github-actions Bot added the Stale PR has not had any activity for some time label Sep 28, 2026

github-actions Bot closed this Oct 5, 2026

Navigation Menu

Repository navigation

fix: preserve fractional seconds in 'second' and 'millisecond' - #22504

fix: preserve fractional seconds in 'second' and 'millisecond'#22504
aguilaredu wants to merge 2 commits into
apache:mainfrom
aguilaredu:bugfix/issue-22266-preseve-fractional-seconds

aguilaredu commented May 24, 2026 •

edited

Loading

Uh oh!

aguilaredu commented May 28, 2026

Uh oh!

github-actions Bot commented Jun 5, 2026

Uh oh!

Adam-Alani Jun 9, 2026

Uh oh!

comphead left a comment

Uh oh!

Adam-Alani commented Jul 29, 2026

Uh oh!

github-actions Bot commented Sep 28, 2026

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

aguilaredu commented May 24, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Which issue does this PR close?

Rationale for this change

What changes are included in this PR?

Are these changes tested?

Are there any user-facing changes?

Uh oh!

aguilaredu commented May 28, 2026

Uh oh!

github-actions Bot commented Jun 5, 2026

Uh oh!

Adam-Alani Jun 9, 2026

Choose a reason for hiding this comment

Uh oh!

comphead left a comment

Choose a reason for hiding this comment

Uh oh!

Adam-Alani commented Jul 29, 2026

Uh oh!

github-actions Bot commented Sep 28, 2026

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

aguilaredu commented May 24, 2026 •

edited

Loading