[firestore-bigquery-export] Add DATETIME support to gen-schema-view

[jasonberryman]

This feature request is for extension: "firestore-bigquery-export"

Convert a ISO8601 date/time string to a DATETIME field in the BigQuery gen-schema-view utility. The timezone offset is not required, as this is simply to show the local time for the user.

This would be used for reporting when the date/time are required for the local time of the event, rather than the "moment in time" that the event happened.

[i14h]

@dackers86 lets investigate this a bit more

[dackers86]

Hi @jasonberryman

Would a specific DATETIME field type resolve this issue for the schema generation?

We also currently use timestamp: new Date(0).toISOString() for Timestamps, could this be the field you are referring to?

[jasonberryman]

Hi @dackers86

A Timestamp denotes a point in time, specified in UTC. The requirement here is to capture the user's local time.

[dackers86]

Thanks @jasonberryman

Before starting PR there are still some things I want to clarify.

According to a developer comment on the the Big Query Change Tracker...

  // The timestamp represented in ISO format.
  // Date is not appropriate because it only has millisecond precision.
  // Cloud Firestore timestamps have microsecond precision.
  timestamp: string;

For backwards compatibility, an option could be added to choose which format the timestamp field should be created in - we could for example allow the user to select the timeStampFormatas either DATETIME or TIMESTAMP depending on the user selection.

Additional tests would show how this would affect the data.