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

Activity

1. Dandandan

   added

   bugSomething isn't workingSomething isn't working

   good first issueGood for newcomersGood for newcomers

   on May 16, 2026
2. aguilaredu commented on May 16, 2026

   

   aguilaredu

   on May 16, 2026

   Contributor

   More actions

   take
3. github-actions

   assigned

   aguilaredu

   on May 16, 2026
4. kumarUjjawal commented on May 18, 2026

   

   kumarUjjawal

   on May 18, 2026

   Contributor

   More actions

   duckdb

   memory D   SELECT extract(second FROM TIMESTAMP '2020-01-01 00:00:12.345678');
   ┌───────────────────────────────────────────────────────────────────────────┐
   │ main.date_part('second', CAST('2020-01-01 00:00:12.345678' AS TIMESTAMP)) │
   │                                   int64                                   │
   ├───────────────────────────────────────────────────────────────────────────┤
   │                                                                        12 │
   
   

   DuckDB's date-part design returns integers across all sub-day fields.
5. aguilaredu commented on May 18, 2026

   

   aguilaredu

   on May 18, 2026

   Contributor

   More actions

   The date_part function previously returned float64. It was changed to follow ANSI SQL convention and also as a broader effort to improve ClickBench performance. See #13482 and #13449.

   Additionally, this other issue could also be solved if the microsecond calculations were made with f64: #14738. However, it was concluded that the fix should be made in arrow-rs.

   I am still working on the change, but wanted to bring it up for discussion.

   cc @Dandandan @kumarUjjawal @alamb

   👍React with 👍1Reacted by Kumar Ujjawal
6. kumarUjjawal commented on May 18, 2026

   

   kumarUjjawal

   on May 18, 2026

   Contributor

   More actions

   Duckdb behaviour: except for julian and epoch, which return DOUBLEs, all parts are extracted as integers. Fractional precision is intentionally exposed through separate parts (millisecond, microsecond) rather than as the fractional component of second.
7. alamb commented on May 18, 2026

   

   alamb

   on May 18, 2026

   Contributor

   More actions

   I don't know about this particular issue, but I think we should be careful with changing semantics just to conform more closely to some particular implementation

   • Add SQL as a category in breaking API change policy #22179

   I tried in the past to get consensus on what semantics to follow but we did not reach it:

   • Document the SQL dialect DataFusion attempts to follow #13704
   • Document SQL dialect guidance #13706
8. kumarUjjawal commented on May 19, 2026

   

   kumarUjjawal

   on May 19, 2026

   Contributor

   More actions

   IMO we should stick to the current behavior unless someone has a use case where they would benefit from semantics change like this.

   👍React with 👍1Reacted by Andrew Lamb
9. neilconway commented on May 22, 2026

   

   neilconway

   on May 22, 2026

   Contributor

   More actions

   My two cents, on the topic of Postgres compatibility / SQL dialect in general:

   • I think the SQL standard is a good place to start. In most situations, that is what Postgres follows, usually unless there is a good reason to deviate.
   • I think that's a reasonable rule of thumb for us as well: follow the SQL spec and/or Postgres in most circumstances / as a reasonable default, but if there is a good reason to deviate, that can be justified.

   As far as when to preserve existing behavior, I think DataFusion's current behavior in a lot of corner cases is either unintentional or actively buggy (#14612, #14272, #15161 are a few examples that come to mind in just type coercion!). SQL is a large and complex language and DF's implementation is relatively new -- there will (hopefully!) be many more new users in DataFusion in the coming years than there are today :) So while I think we should be aware of SQL backward compatibility and try to ease migration pain as much as we can (e.g., clear release notes / upgrade guidance, config flags when necessary), I do think evolving the SQL dialect to be more consistent and standards-conformant is a good thing.
10. alamb commented on May 22, 2026

    

    alamb

    on May 22, 2026

    Contributor

    More actions

    I think the SQL standard is a good place to start. In most situations, that is what Postgres follows, usually unless there is a good reason to deviate.

    I know people love to bandy around the term "SQL Standard" (and I have a copy of "Information technology — Database languages —SQL — Part 2: Foundation (SQL/Foundation)" !) but it is a pretty bad example of a spec in my opinion and leaves a lot of things "implementations defined"

    For example here is an excerpt about extract

    7. If is specified, then
       Case:
       a) If is a that does not specify SECOND or is
       not a , then the declared type of the result is an implementation-defined exact
       numeric type with scale 0 (zero).
       b) Otherwise, the declared type of the result is an implementation-defined exact numeric type with scale
       not less than the specified or implied or , as appropriate, of the SECOND of the .
11. neilconway commented on May 22, 2026

    

    neilconway

    on May 22, 2026

    Contributor

    More actions

    it is a pretty bad example of a spec in my opinion and leaves a lot of things "implementations defined"

    No argument from me that the SQL spec isn't perfect 😊 I do think it's often a good data point to consider, along with Postgres (which usually does a pretty decent job of trying to follow the spec).
12. alamb commented on May 25, 2026

    

    alamb

    on May 25, 2026

    Last edited by alamb

    Contributor

    More actions

    it is a pretty bad example of a spec in my opinion and leaves a lot of things "implementations defined"

    No argument from me that the SQL spec isn't perfect 😊 I do think it's often a good data point to consider, along with Postgres (which usually does a pretty decent job of trying to follow the spec).

    I just harbor suspicions that vendors claiming "we follow the ANSI SQL spec" is often somwaht specious -- because there is so much wiggle room allowed the actual spec.

    But anyhow that is just my own bias
13. aguilaredu commented on May 28, 2026

    

    aguilaredu

    on May 28, 2026

    Contributor

    More actions

    My two cents based on the discussion:

    • Following an established vendor like PostgreSQL feels like a sane default, especially if the project doesn't have the bandwidth to interpret the SQL standard from scratch.
    • Picking a direction is better than being stuck in limbo (which causes reworks, like this one). Mirroring a battle tested DB seems like a pretty safe bet (even if sub optimal).
    • Using them as a baseline shouldn't limit DataFusion. We can match their behavior where they have it, but we can take liberties with features they don't support.
    • Deviating from the reference is fine as long as there’s a good reason for it and we document it centrally.

    This at least gives the project a framework to have discussions on smaller features (like this one).

    P.S. This is my very first contribution to the project, so I might be missing some historical context! That said, I've got a PR up implementing this and would absolutely love some feedback: #22504

    👍React with 👍1Reacted by Neil Conway
14. kumarUjjawal commented on May 28, 2026

    

    kumarUjjawal

    on May 28, 2026

    Contributor

    More actions

    I think the best source to follow is not a vendor or a spec but rather use cases and the people who use and integrate datafusion extensively in production. They will have more ideas about what problems they face and what is the best case for them. Once we have a known issue with any semantics I think people are quick to figure out a solution.