Support extracting week and week's year from date or timestamp

[findepi]

Add support for extract(week_iso from date_time) and date_part('week_iso', date_time).
Add support for extract(year_of_week_iso from date_time) and date_part('year_of_week_iso', date_time).

References

PostgreSQL

https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

• week (ISO week)
• isoyear (year of week)

Trino

https://github.com/trinodb/trino/blob/8e88778a03f137a0c0924875f8d4560138d23d10/core/trino-parser/src/main/java/io/trino/sql/tree/Extract.java#L36

https://github.com/trinodb/trino/blob/8e88778a03f137a0c0924875f8d4560138d23d10/core/trino-parser/src/main/java/io/trino/sql/tree/Extract.java#L43

Snowflake

https://docs.snowflake.com/en/sql-reference/functions-date-time#supported-date-and-time-parts

• week
• weekiso | week_iso
• yearofweek
• yearofweekiso

Notes

Snowflake has two variants of these functions / date-time fields. The ISO variant is means always the same thing. The non-ISO is session-dependent.
In our implementation we should support the ISO semantics. We could use ISO terminology (_iso names), or support them as aliases.

[kosiew]

take

[Omega359]

Looks like the code in date_part (which extract maps to) uses IntervalUnit for the matching of the parts. 'Week' is already supported and maps to iso week of the year (0-53) though it doesn't have the week-iso alias. That would be a change in arrow-arith.

I think supporting isoyear/yearofweek/etc would be more work as there is no support for that anywhere in arror-arith that I can find.

[kosiew]

@Omega359 ,
Thanks for the pointer.