extract(second from dt) should return seconds without fraction

[findepi]

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

cc @jayzhan211 @comphead

[jayzhan211]

Given there are other systems return integer too, I'm fine if we return int for second and mili/micro/nano second

[jayzhan211]

I found that nanosecond is neither supported in postgres and duckdb. If it is true, I prefer to remove it.

[comphead]

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]

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

If they really need nanosecond precision, they could customize the function for themselves

[findepi]

i don't know why other systems don't support nanosecond field while supporting nanosecond precision timestamps. maybe that's a legacy thing?