sqlglot icon indicating copy to clipboard operation
sqlglot copied to clipboard

Fix(clickhouse)!: time string literals containing fractional seconds

Open treysp opened this issue 1 year ago • 0 comments

OUTSTANDING QUESTION:

How to handle removing UTC offset for python < 3.11 where datetime.datetime.fromisoformat() can't parse strings containing fractional seconds - dateutil library?


Clickhouse has two datetime types:

  • DateTime: fixed one second precision
  • DateTime64: accepts user-specified precision (default of 3 when bare type passed)

Currently, time string literals are always generated by casting to DateTime, causing Clicktime to error if the string contains fractional seconds.

This PR casts time string literals to a data type based on the fractional seconds present in the string literal:

  • No fractional seconds = DateTime
  • Fractional seconds of length 1 (.0, .1) to three (.000, .001) = DateTime64(3)
    • We use 3 under the assumption that people want the default over something smaller
  • Fractional seconds of length > 3 = DateTime64([length])

The returned data type is non-nullable for string literal input expressions because they may be used in contexts where nullable types cause errors (like an ORDER BY clause). Column/identifier input expressions still return a nullable type.

treysp avatar Oct 18 '24 22:10 treysp