How to write dates and datetimes to database with write_to_source / write_to_model functions

[chamini2]

I have a dataframe with date values and when writing it with write_to_model (same for write_to_source) it fails not knowing what to do with it. I tried some dtype values and had no success.

dataframe.dtypes

ds                                      datetime64[ns]
trend                                          float64
yhat_lower                                     float64
yhat_upper                                     float64

BigQuery error:

Error in script /Users/matteo/Projects/fal/jaffle_shop_with_fal/models/orders_forecast.py with model orders_forecast:
Traceback (most recent call last):
  File "/.../lib/python3.9/site-packages/dbt/adapters/bigquery/connections.py", line 174, in exception_handler
    yield
  File "/.../lib/python3.9/site-packages/dbt/adapters/bigquery/connections.py", line 549, in _retry_and_handle
    return retry.retry_target(
  File "/.../lib/python3.9/site-packages/google/api_core/retry.py", line 190, in retry_target
    return target()
  File "/.../lib/python3.9/site-packages/dbt/adapters/bigquery/connections.py", line 378, in fn
    return self._query_and_results(client, sql, conn, job_params)
  File "/.../lib/python3.9/site-packages/dbt/adapters/bigquery/connections.py", line 535, in _query_and_results
    iterator = query_job.result(timeout=timeout)
  File "/.../lib/python3.9/site-packages/google/cloud/bigquery/job/query.py", line 1498, in result
    do_get_result()
  File "/.../lib/python3.9/site-packages/google/api_core/retry.py", line 283, in retry_wrapped_func
    return retry_target(
  File "/.../lib/python3.9/site-packages/google/api_core/retry.py", line 190, in retry_target
    return target()
  File "/.../lib/python3.9/site-packages/google/cloud/bigquery/job/query.py", line 1488, in do_get_result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/.../lib/python3.9/site-packages/google/cloud/bigquery/job/base.py", line 728, in result
    return super(_AsyncJob, self).result(timeout=timeout, **kwargs)
  File "/.../lib/python3.9/site-packages/google/api_core/future/polling.py", line 137, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Syntax error: Expected ")" or "," but got string literal '2018-01-01T00:00:00.000000000' at [2:1965]

Location: US

TODO: add Postgres error

[valex92]

I believe this is a constraint of the sqlalchemy library. I had to convert dates and datetimes to string

[chamini2]

That is a good temporary solution. But I wonder if we could get fal to make this work automatically for date/datetime columns.

[chamini2]

As a note for anyone looking at this, the workaround of converting to string is https://github.com/fal-ai/fal/blob/7764e066433b4067c5ab8c50158e1d5cc38ceaec/integration_tests/projects/002_jaffle_shop/fal_scripts/models/orders_forecast.py#L50-L56

If you want timestamp instead of date, you can do x.strftime("%Y-%m-%d %H:%M:%S.%f")