BigQuery new data type DATE

Question

I see a new data typeDATE being introduced in the BigQuery Web UI but not documented in https://cloud.google.com/bigquery/data-types. Well DATE as such is not new but I have one major question. Unlike TIMESTAMP does DATE ignore timezone information after being stored.

Quoted text from TIMESTAMP documentation:

You can supply a timezone offset in your date and time strings, but BigQuery doesn't preserve the offset after converting the value to its internal format. If you need to preserve the original timezone data, store the timezone offset in a separate column.

Also whats the expected input for this format. I have tried the known timestamp strings but it does not seem to work.

You're right that we still need to update our main data types page. Mikhail's link to the standard SQL reference should convey similar information to what we will include there soon though. — Elliott Brossard
With more products and features, navigating through developer docs gets tricky. Something really needs to be done about it. — Abhishek Nandi

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-09-02T13:30:59

I see a new data typeDATE being introduced in the BigQuery Web UI but not documented

Date type was introduced with Standard SQL - see Date type for details

Also whats the expected input for this format

Canonical format

'YYYY-[M]M-[D]D'
YYYY: Four-digit year
[M]M: One or two digit month
[D]D: One or two digit day

Note: The DATE type represents a logical calendar date, independent of time zone.

BigQuery new data type DATE

1 Answers