Not all ISO8601 date-time work with BigQuery

[turb]

Sometimes LocalDateTime columns fail to be written to BigQuery, due to format.

For example, this fails in BigQuery: select cast('2025-01-07T10:47:38.1234567890' as datetime), with Invalid datetime string "2025-01-07T10:47:38.1234567890".

Whereas this works, truncated to microseconds: select cast('2025-01-07T10:47:38.123456' as datetime)

Thing is, java.time.LocalDateTime#toString can produce nano decimals, making such insert fail.

We solved the issue locally with:

// Fix BigQuery that refuses datetime with more than 6 digits after the second
  private val dtf = DateTimeFormatter.ofPattern("uuuu-MM-dd'T'HH:mm:ss[.SSSSSS]")
  implicit val bqShowLocalDateTime: BQShow[LocalDateTime] =
    x => BQSqlFrag(s"DATETIME('${x.format(dtf)}')")

It may go to BQShow:125. If we can agree on the right pattern (here I think timezone is missing), I can provide a PR