Open jcbmllgn opened 1 year ago
@jcbmllgn I think this is a neat solution with a low scope. The best practice in many taps is to format the timestamps as ISO but because of the sheer variation, it would be nice to have this capability here. I think something we should think about too is using safe_cast
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#safe_casting
Using this would let us essentially use coalesce
to encapsulate fallback logic. Then we can just statically account for a few more date formats outside of which we say use a mapper / fix the dates from the tap.
I have a timestamp coming from a source that causes BigQuery's default timestamp parsing to fail with how this package by default parses timestamps, this line of code generates this sql:
Which fails for this reason:
The issue is that there's a space between the seconds and timezone offset, here's what comes from my source system
2023-04-26 14:57:00 -0700
, however, here's what BigQuery's defaultCAST(.. as TIMESTAMP)
function wants2023-04-26 14:57:00-0700
.I would like to be able to add a config option to my
meltano.yml
file for specifying how timestamps should be parsed, something like this:and then the package would parse timestamps like this instead: