Repository navigation
[BigQuery] Can't insert correct TIMESTAMP. #1247
Description
In my environment, I can't insert correct data to the TIMESTAMP column.
- gcloud 0.8.0
- Python 2.7.10
- MacOS X 10.10.5
The following snippet is the smallest reproducible example.
Please let me know that it's a bug or my mistake.
import os
from datetime import datetime
from gcloud import bigquery
GCP_KEY_PATH = os.path.join(os.path.dirname(__file__), './gcpkey.json')
project_id = 'my_project'
dataset = 'tmp'
table_name = 'test'
bq = bigquery.Client.from_service_account_json(GCP_KEY_PATH, project=project_id)
table = bq.dataset(dataset).table(name=table_name)
if not table.exists():
table.schema = [bigquery.SchemaField('time', 'TIMESTAMP')]
table.create()
table.reload()
rows = [[datetime.now()]]
table.insert_data(rows)
Activity
kamatama41 commentedon Nov 28, 2015
I suspect that here is the cause.
BigQuery's
TIMESTAMPrequires the UNIX timestamp as seconds but the lines convert datetime to milliseconds.ref: https://cloud.google.com/bigquery/preparing-data-for-bigquery?hl=en#datatypes
dhermes commentedon Nov 30, 2015
Totally agree. @tseaver and I had a discussion about this before and we thought we had a found a decisive place indicating how the value should be sent. It seems we got it wrong.
kamatama41 commentedon Nov 30, 2015
Thank you for your comment!
Does it mean that this is a bug and you're going to fix it?
(Sorry for my poor English proficiency)
dhermes commentedon Nov 30, 2015
I can't say 100% it's a bug, but it seems pretty likely. It means we'll dig into verifying and then fixing if we can find a verifiable bug. (Again seems very likely.)
kamatama41 commentedon Nov 30, 2015
OK, I got it. I hope it's fixed. 😄
tseaver commentedon Nov 30, 2015
@kamatama41 The docs you link above state:
So, rather than passing milliseconds, we should just pass seconds.
dhermes commentedon Nov 30, 2015
FYI @tseaver I am fixing this right now
dhermes commentedon Nov 30, 2015
I'm also finding lots of out-of-date docs in http://gcloud-python.readthedocs.org/en/latest/bigquery-usage.html
kalanyuz commentedon Oct 31, 2019
What if we want to store millisec sensitive data here? Like sensors data that are collected at more than 1 Hz
tseaver commentedon Oct 31, 2019
@kalanyuz This issue was closed by a PR which correctly Python datetime objects into
TIMESTAMPfields at microsecond precision.chore(deps): update dependency jinja2 to v3.1.1 (#1247)
chore(deps): update dependency libcst to v0.4.3 (#1247)
samples: add samples for object retention (#1247)
fix: allow setting connection.read_only to same value (#1247)
docs: migrate code from googleapis/python-bigquery (#18303)