BigQuery: named parameter timestamp sample

tswast · tswast · commit a788ea182994 · 2016-12-19T13:58:25.000-08:00
An example query using timestamps in named parameters. See: https://cloud.google.com/bigquery/querying-data#using_timestamps_in_parameterized_queries
diff --git a/bigquery/cloud-client/requirements.txt b/bigquery/cloud-client/requirements.txt
@@ -1 +1,2 @@
 google-cloud-bigquery==0.22.1
+pytz==2016.10
diff --git a/bigquery/cloud-client/sync_query_params.py b/bigquery/cloud-client/sync_query_params.py
@@ -24,7 +24,9 @@
 """
 
 import argparse
+import datetime
 
+import pytz
 from google.cloud import bigquery
 
 
@@ -109,6 +111,20 @@ def sync_query_array_params(gender, states):
     print_results(query_results)
 
 
+def sync_query_timestamp_params():
+    client = bigquery.Client()
+    query_results = client.run_sync_query(
+            'SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);',
+        query_parameters=[
+            bigquery.ScalarQueryParameter(
+                'ts_value',
+                'TIMESTAMP',
+                datetime.datetime(2016, 12, 7, 8, tzinfo=pytz.UTC))])
+    query_results.use_legacy_sql = False
+    query_results.run()
+    print_results(query_results)
+
+
 if __name__ == '__main__':
     parser = argparse.ArgumentParser(
         description=__doc__,
@@ -145,6 +161,9 @@ def sync_query_array_params(gender, states):
         'states',
         help='U.S. States to consider for popular baby names.',
         nargs='+')
+    timestamp_parser = subparsers.add_parser(
+        'timestamp',
+        help='Run a query with a timestamp parameter.')
     args = parser.parse_args()
 
     if args.sample == 'named':
@@ -153,5 +172,7 @@ def sync_query_array_params(gender, states):
         sync_query_positional_params(args.corpus, args.min_word_count)
     elif args.sample == 'array':
         sync_query_array_params(args.gender, args.states)
+    elif args.sample == 'timestamp':
+        sync_query_timestamp_params()
     else:
         print('Unexpected value for sample')
diff --git a/bigquery/cloud-client/sync_query_params_test.py b/bigquery/cloud-client/sync_query_params_test.py
@@ -23,6 +23,12 @@ def test_sync_query_array_params(cloud_config, capsys):
     assert 'James' in out
 
 
+def test_sync_query_array_params(cloud_config, capsys):
+    sync_query_params.sync_query_timestamp_params()
+    out, _ = capsys.readouterr()
+    assert '2016-12-07 09:00:00' in out
+
+
 def test_sync_query_named_params(cloud_config, capsys):
     sync_query_params.sync_query_named_params(
         corpus='romeoandjuliet',

Original file line number	Diff line number	Diff line change
`@@ -1 +1,2 @@`
`1`	`1`	`google-cloud-bigquery==0.22.1`
	`2`	`+pytz==2016.10`