Skip to content
This repository was archived by the owner on Mar 6, 2026. It is now read-only.

Commit ef8634a

Browse files
tswasttswast
committed
Parse timestamps in query parameters using canonical format (#3945)
* Parse timestamps in query parameters according to BigQuery canonical timestamp format. The timestamp format in query parameters follows the canonical format specified at https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp-type This fixes a system test error which was happening in the bigquery-b2 branch. * Support more possible timestamp formats. Any of these formats may be returned from the BigQuery API. * Chop and string-replace timestamps into a canonical format. * BQ: fix lint errors. Remove references to table.name
1 parent 35b26bf commit ef8634a

3 files changed

Lines changed: 144 additions & 3 deletions

File tree

‎google/cloud/bigquery/_helpers.py‎

Lines changed: 41 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,39 @@ def _timestamp_from_json(value, field):
7171
return _datetime_from_microseconds(1e6 * float(value))
7272

7373

74+
def _timestamp_query_param_from_json(value, field):
75+
"""Coerce 'value' to a datetime, if set or not nullable.
76+
77+
Args:
78+
value (str): The timestamp.
79+
field (.SchemaField): The field corresponding to the value.
80+
81+
Returns:
82+
Optional[datetime.datetime]: The parsed datetime object from
83+
``value`` if the ``field`` is not null (otherwise it is
84+
:data:`None`).
85+
"""
86+
if _not_null(value, field):
87+
# Canonical formats for timestamps in BigQuery are flexible. See:
88+
# g.co/cloud/bigquery/docs/reference/standard-sql/data-types#timestamp-type
89+
# The separator between the date and time can be 'T' or ' '.
90+
value = value.replace(' ', 'T', 1)
91+
# The UTC timezone may be formatted as Z or +00:00.
92+
value = value.replace('Z', '')
93+
value = value.replace('+00:00', '')
94+
95+
if '.' in value:
96+
# YYYY-MM-DDTHH:MM:SS.ffffff
97+
return datetime.datetime.strptime(
98+
value, _RFC3339_MICROS_NO_ZULU).replace(tzinfo=UTC)
99+
else:
100+
# YYYY-MM-DDTHH:MM:SS
101+
return datetime.datetime.strptime(
102+
value, _RFC3339_NO_FRACTION).replace(tzinfo=UTC)
103+
else:
104+
return None
105+
106+
74107
def _datetime_from_json(value, field):
75108
"""Coerce 'value' to a datetime, if set or not nullable.
76109
@@ -139,6 +172,9 @@ def _record_from_json(value, field):
139172
'RECORD': _record_from_json,
140173
}
141174

175+
_QUERY_PARAMS_FROM_JSON = dict(_CELLDATA_FROM_JSON)
176+
_QUERY_PARAMS_FROM_JSON['TIMESTAMP'] = _timestamp_query_param_from_json
177+
142178

143179
def _row_from_json(row, schema):
144180
"""Convert JSON row data to row with appropriate types.
@@ -454,7 +490,7 @@ def from_api_repr(cls, resource):
454490
name = resource.get('name')
455491
type_ = resource['parameterType']['type']
456492
value = resource['parameterValue']['value']
457-
converted = _CELLDATA_FROM_JSON[type_](value, None)
493+
converted = _QUERY_PARAMS_FROM_JSON[type_](value, None)
458494
return cls(name, type_, converted)
459495

460496
def to_api_repr(self):
@@ -576,7 +612,9 @@ def _from_api_repr_scalar(cls, resource):
576612
for value
577613
in resource['parameterValue']['arrayValues']]
578614
converted = [
579-
_CELLDATA_FROM_JSON[array_type](value, None) for value in values]
615+
_QUERY_PARAMS_FROM_JSON[array_type](value, None)
616+
for value in values
617+
]
580618
return cls(name, array_type, converted)
581619

582620
@classmethod
@@ -732,7 +770,7 @@ def from_api_repr(cls, resource):
732770
converted = ArrayQueryParameter.from_api_repr(struct_resource)
733771
else:
734772
value = value['value']
735-
converted = _CELLDATA_FROM_JSON[type_](value, None)
773+
converted = _QUERY_PARAMS_FROM_JSON[type_](value, None)
736774
instance.struct_values[key] = converted
737775
return instance
738776

‎tests/system.py‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1081,6 +1081,14 @@ def test_dbapi_w_query_parameters(self):
10811081
},
10821082
'expected': datetime.datetime(2012, 3, 4, 5, 6, 0, tzinfo=UTC),
10831083
},
1084+
{
1085+
'sql': 'SELECT TIMESTAMP_TRUNC(%(zoned)s, MINUTE)',
1086+
'query_parameters': {
1087+
'zoned': datetime.datetime(
1088+
2012, 3, 4, 5, 6, 7, 250000, tzinfo=UTC),
1089+
},
1090+
'expected': datetime.datetime(2012, 3, 4, 5, 6, 0, tzinfo=UTC),
1091+
},
10841092
]
10851093
for example in examples:
10861094
msg = 'sql: {} query_parameters: {}'.format(

‎tests/unit/test__helpers.py‎

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -158,6 +158,63 @@ def test_w_base64_encoded_text(self):
158158
self.assertEqual(coerced, expected)
159159

160160

161+
class Test_timestamp_query_param_from_json(unittest.TestCase):
162+
163+
def _call_fut(self, value, field):
164+
from google.cloud.bigquery import _helpers
165+
166+
return _helpers._timestamp_query_param_from_json(value, field)
167+
168+
def test_w_none_nullable(self):
169+
self.assertIsNone(self._call_fut(None, _Field('NULLABLE')))
170+
171+
def test_w_timestamp_valid(self):
172+
from google.cloud._helpers import UTC
173+
174+
samples = [
175+
(
176+
'2016-12-20 15:58:27.339328+00:00',
177+
datetime.datetime(2016, 12, 20, 15, 58, 27, 339328, tzinfo=UTC)
178+
),
179+
(
180+
'2016-12-20 15:58:27+00:00',
181+
datetime.datetime(2016, 12, 20, 15, 58, 27, tzinfo=UTC)
182+
),
183+
(
184+
'2016-12-20T15:58:27.339328+00:00',
185+
datetime.datetime(2016, 12, 20, 15, 58, 27, 339328, tzinfo=UTC)
186+
),
187+
(
188+
'2016-12-20T15:58:27+00:00',
189+
datetime.datetime(2016, 12, 20, 15, 58, 27, tzinfo=UTC)
190+
),
191+
(
192+
'2016-12-20 15:58:27.339328Z',
193+
datetime.datetime(2016, 12, 20, 15, 58, 27, 339328, tzinfo=UTC)
194+
),
195+
(
196+
'2016-12-20 15:58:27Z',
197+
datetime.datetime(2016, 12, 20, 15, 58, 27, tzinfo=UTC)
198+
),
199+
(
200+
'2016-12-20T15:58:27.339328Z',
201+
datetime.datetime(2016, 12, 20, 15, 58, 27, 339328, tzinfo=UTC)
202+
),
203+
(
204+
'2016-12-20T15:58:27Z',
205+
datetime.datetime(2016, 12, 20, 15, 58, 27, tzinfo=UTC)
206+
),
207+
]
208+
for timestamp_str, expected_result in samples:
209+
self.assertEqual(
210+
self._call_fut(timestamp_str, _Field('NULLABLE')),
211+
expected_result)
212+
213+
def test_w_timestamp_invalid(self):
214+
with self.assertRaises(ValueError):
215+
self._call_fut('definitely-not-a-timestamp', _Field('NULLABLE'))
216+
217+
161218
class Test_timestamp_from_json(unittest.TestCase):
162219

163220
def _call_fut(self, value, field):
@@ -1820,6 +1877,44 @@ def test_w_scalar(self):
18201877
self.assertEqual(parameter.type_, 'INT64')
18211878
self.assertEqual(parameter.value, 123)
18221879

1880+
def test_w_scalar_timestamp(self):
1881+
from google.cloud.bigquery._helpers import ScalarQueryParameter
1882+
from google.cloud._helpers import UTC
1883+
1884+
RESOURCE = {
1885+
'name': 'zoned',
1886+
'parameterType': {'type': 'TIMESTAMP'},
1887+
'parameterValue': {'value': '2012-03-04 05:06:07+00:00'},
1888+
}
1889+
1890+
parameter = self._call_fut(RESOURCE)
1891+
1892+
self.assertIsInstance(parameter, ScalarQueryParameter)
1893+
self.assertEqual(parameter.name, 'zoned')
1894+
self.assertEqual(parameter.type_, 'TIMESTAMP')
1895+
self.assertEqual(
1896+
parameter.value,
1897+
datetime.datetime(2012, 3, 4, 5, 6, 7, tzinfo=UTC))
1898+
1899+
def test_w_scalar_timestamp_micros(self):
1900+
from google.cloud.bigquery._helpers import ScalarQueryParameter
1901+
from google.cloud._helpers import UTC
1902+
1903+
RESOURCE = {
1904+
'name': 'zoned',
1905+
'parameterType': {'type': 'TIMESTAMP'},
1906+
'parameterValue': {'value': '2012-03-04 05:06:07.250000+00:00'},
1907+
}
1908+
1909+
parameter = self._call_fut(RESOURCE)
1910+
1911+
self.assertIsInstance(parameter, ScalarQueryParameter)
1912+
self.assertEqual(parameter.name, 'zoned')
1913+
self.assertEqual(parameter.type_, 'TIMESTAMP')
1914+
self.assertEqual(
1915+
parameter.value,
1916+
datetime.datetime(2012, 3, 4, 5, 6, 7, 250000, tzinfo=UTC))
1917+
18231918
def test_w_array(self):
18241919
from google.cloud.bigquery._helpers import ArrayQueryParameter
18251920

0 commit comments

Comments
 (0)