Support INTERVAL data type

[tswast]

"BigQuery now supports the INTERVAL type, which represents a duration or an amount of time. This type is in Preview."

https://cloud.google.com/bigquery/docs/release-notes#July_27_2021

Name	Range
INTERVAL	-10000-0 -3660000 -87840000:0:0 to 10000-0 3660000 87840000:0:0

An INTERVAL object represents duration or amount of time. Interval is composed of three independent parts:

• [sign]Y-M: Years and Months
• [sign]D: Days
• [sign]H:M:S.F: Hours, Minutes, Seconds and Subseconds.

Canonical format

[sign]Y-M [sign]D [sign]H:M:S[.F]

• Y: Year
• M: Month
• D: Day
• H: Hour
• M: Minute
• S: Second
• [.F]: Up to six fractional digits (microsecond precision)

TODO:

• Add relevant enums for data types
• Row data is converted to relevant type in RowIterator (list_rows()) feat: add support for INTERVAL data type to list_rows #840
• Convert data type in query parameters
• Covert data type in insert_rows

Edit: Removed pandas, arrow, db-api support in favor of #836, as those implementations are currently blocked on Arrow and the BQ Storage API.

[tswast]

Full pandas / arrow support may have to wait. Getting an error from the BQ Storage API when I added

  {
    "mode": "NULLABLE",
    "name": "interval_col",
    "type": "INTERVAL"
  }

to the scalars_schema.json file, and the test value "interval_col": "P7Y11M9DT4H15M37.123456S" (ISO 8601, which is used as the canonical format for JSON)

[tswast]

Query parameter support doesn't appear to be implemented yet in the backend. I've filed internal issue 195050789.

[tswast]

insert_rows works, but only with "canonical" format. The "canonical" format is a pain to work with since I'll have to have a bunch of client-side logic to detect if years/months are different signs and recalculate. Likewise for hours/minutes/seconds/microseconds.

I've filed internal issue 195051077 to support ISO 8601 Duration.

[tswast]

Issue 195051077 is now resolved. Both insert_rows and query parameters are now supported (or will be once the changes roll out, hopefully in December)

[tboddyspargo]

Hi, @tswast - would you be able to summarize the status of this request? Are there outstanding blockers or is it just a question of prioritization? My team would love to be able to support INTERVAL with GBQ (using pandas-gbq or bigquery-sqlalchemy), but I'm hesitant to pursue a potentially complicated custom workaround if there might be a more official solution in the works. Thanks in advance!