Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[BigQuery] Can't insert correct TIMESTAMP. #1247

Closed
kamatama41 opened this issue Nov 28, 2015 · 10 comments
Closed

[BigQuery] Can't insert correct TIMESTAMP. #1247

kamatama41 opened this issue Nov 28, 2015 · 10 comments
Assignees
Labels
api: bigquery Issues related to the BigQuery API. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.

Comments

@kamatama41
Copy link

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)

2015-11-28 15 47 08

@kamatama41
Copy link
Author

I suspect that here is the cause.
BigQuery's TIMESTAMP requires 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
Copy link
Contributor

dhermes commented 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
Copy link
Author

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
Copy link
Contributor

dhermes commented 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
Copy link
Author

OK, I got it. I hope it's fixed. 😄

@tseaver tseaver self-assigned this Nov 30, 2015
@tseaver
Copy link
Contributor

tseaver commented Nov 30, 2015

@kamatama41 The docs you link above state:

A positive or negative decimal number. A positive number specifies the number of seconds since the epoch (1970-01-01 00:00:00 UTC), and a negative number specifies the number of seconds before the epoch. Up to 6 decimal places (microsecond precision) are preserved.

So, rather than passing milliseconds, we should just pass seconds.

@dhermes
Copy link
Contributor

dhermes commented Nov 30, 2015

FYI @tseaver I am fixing this right now

@dhermes
Copy link
Contributor

dhermes commented Nov 30, 2015

I'm also finding lots of out-of-date docs in http://gcloud-python.readthedocs.org/en/latest/bigquery-usage.html

@tseaver tseaver assigned dhermes and unassigned tseaver Nov 30, 2015
@dhermes dhermes added api: bigquery Issues related to the BigQuery API. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns. labels Nov 30, 2015
@kalanyuz
Copy link

What if we want to store millisec sensitive data here? Like sensors data that are collected at more than 1 Hz

@tseaver
Copy link
Contributor

tseaver commented Oct 31, 2019

@kalanyuz This issue was closed by a PR which correctly Python datetime objects into TIMESTAMP fields at microsecond precision.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
api: bigquery Issues related to the BigQuery API. type: bug Error or flaw in code with unintended results or allowing sub-optimal usage patterns.
Projects
None yet
Development

No branches or pull requests

4 participants