Repository navigation
extract(second from dt) should return seconds without fraction #13482
Description
In ANSI SQL, Trino, Snowflake, DuckDB, ... extract(second from a_date_time_value) returns just seconds (integer number 0-59 inclusive), which is consistent with other extraction of other fields like minute (meaning minute-of-hour), hour (meaning hour-of-day), etc. especially when sub-second fields are also supported.
in DataFusion extract(second from dt) returns second with fraction (as in PostgreSQL).
We should probably change it to return just second without fraction, especially given that the fraction part can be extracted with e.g. extract(millisecond ..) field
Reacted by Jay Zhan and Oleks V
Activity
jayzhan211 commentedon Nov 19, 2024
Given there are other systems return integer too, I'm fine if we return int for second and mili/micro/nano second
jayzhan211 commentedon Nov 20, 2024
I found that nanosecond is neither supported in postgres and duckdb. If it is true, I prefer to remove it.
comphead commentedon Nov 20, 2024
I dont remember why we do support nanos although most of systems do not.
not sure if we need to remove it if it works it can probably be helpful for someone if they store and do maths exacly on nanosecond level, not sure what the real world use case could be. Perhaps scientific thing
jayzhan211 commentedon Nov 21, 2024
If they really need nanosecond precision, they could customize the function for themselves
findepi commentedon Nov 22, 2024
i don't know why other systems don't support nanosecond field while supporting nanosecond precision timestamps. maybe that's a legacy thing?