[CALCITE-6358] Support all PostgreSQL 14 date/time patterns for to_char

• Type: Sub-task
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: Norman Jordan
• Reporter: Norman Jordan
• Created: 10/Apr/24 17:46
• Updated: 6 days ago 16:40
• Resolved: 6 days ago 16:40

Description

Many of the date/time format patterns supported by PostgreSQL 14 are not supported in Calcite.

• HH
• US
• SSSS
• SSSSS
• AM
• A.M.
• am
• a.m.
• PM
• P.M.
• pm
• p.m.
• Y,YYY
• YYY
• Y
• IYYY
• IYY
• IY
• I
• BC
• B.C.
• bc
• b.c.
• AD
• A.D.
• ad
• a.d.
• MONTH
• month
• MON
• mon
• DAY
• day
• Dy
• dy
• IDDD
• ID
• TZH
• TZM
• OF

There are also template pattern modifiers that need to be supported.

• FM (prefix)
• TH (suffix)
• th (suffix)
• FX (prefix)
• TM (prefix)

Some format patterns in Calcite behave differently from PostgreSQL 14.

• FF1
• FF2
• FF4
• FF5
• FF6

Also verify that the other existing format strings produce results that match PostgreSQL 14.

Issue Links

• is related to: CALCITE-6269 Fix missing/broken BigQuery date-time format elements
• links to: GitHub Pull Request #3773
• links to: GitHub Pull Request #3792