[CALCITE-6392] Support all PostgreSQL 14 date/time patterns for to_date/to_timestamp

• Type: Sub-task
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.38.0
• Labels: pull-request-available
• Assignee: Norman Jordan
• Reporter: Norman Jordan
• Created: 29/Apr/24 21:23
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

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

• links to: GitHub Pull Request #3791
• links to: GitHub Pull Request #3805