BigQuery timestamps casted as datetime instead of timestamp

1

Hello,

I have a field called "Created" in a BigQuery table which contains the creation timestamp of an object, in this form : "2021-06-28T11:28:52Z".

I have declared it as such in the Metabase data model:

image
image1561×125 8.34 KB

But when I try to run a query, it seems that Metabase tries to cast it to a datetime object instead of timestamp, which results in an error in BigQuery.

Reality :
image

Expectation:
image

Is there any way to tell Metabase to cast the timestamp using the timestamp type and not the datetime type ?

Thanks

PS : I am using Metabase Cloud

2

Hi @AnatoleC
Please post "Diagnostic Info" from Admin > Troubleshooting.
There's two types of manipulations that Metabase does. Display formatting, and casting.
You are looking for casting, since your column is a string (not a timestamp, or datetime).
https://www.metabase.com/docs/latest/administration-guide/03-metadata-editing.html#casting-to-a-specific-data-type

3

Hi @flamber,

Thanks, it seems to be the right option for me. But unfortunately, it seems that none of the cast options are working for my case.

The most appropriate option would be this one:
image

But it gives following error when running a query :
image

Here is the diagnostic info :

{
"browser-info": {
"language": "fr-FR",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.93 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.13+8",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.13",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.13+8",
"os.name": "Linux",
"os.version": "4.14.219-164.354.amzn2.x86_64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"postgres",
"bigquery-cloud-sdk",
"h2"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.9"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"date": "2021-12-10",
"tag": "v1.41.4",
"branch": "release-x.41.4",
"hash": "471cfb9"
},
"settings": {
"report-timezone": "Europe/Berlin"
}
}
}

4

@AnatoleC
Post the output by going to the URL /api/field/123, where 123 is the field ID, which you can see in the URL by going to Admin > Data Model > (db) > (table) > (column) :gear: > the last part of the URL .../123/general
Might want to redact email etc.

5

@flamber

Here is what you asked for:

{
"description":null,
"database_type":"STRING",
"semantic_type":"type/CreationTimestamp",
"table_id":527,
"coercion_strategy":"Coercion/ISO8601->DateTime",
"table":{
"description":null,
"entity_type":"entity/GenericTable",
"schema":null,
"db":{
"description":null,
"features":[
"full-join",
"basic-aggregations",
"standard-deviation-aggregations",
"expression-aggregations",
"percentile-aggregations",
"foreign-keys",
"right-join",
"left-join",
"native-parameters",
"nested-queries",
"expressions",
"regex",
"case-sensitivity-string-filter-options",
"binning",
"inner-join",
"advanced-math-expressions"
],
"cache_field_values_schedule":"0 0 1 * * ? *",
"timezone":"UTC",
"auto_run_queries":true,
"metadata_sync_schedule":"0 47 * * * ? *",
"name":"BigQuery Prod",
"caveats":null,
"is_full_sync":true,
"updated_at":"2021-12-01T12:52:55.540421Z",
"cache_ttl":null,
"details":{
"project-id":"pledg-331014",
"service-account-json":"MetabasePass",
"dataset-id":"prod",
"use-jvm-timezone":false,
"include-user-id-and-hash":true,
"project-id-from-credentials":"pledg-331014"
},
"is_sample":false,
"id":101,
"is_on_demand":false,
"options":null,
"engine":"bigquery-cloud-sdk",
"refingerprint":true,
"created_at":"2021-11-18T16:52:58.507655Z",
"points_of_interest":null
},
"show_in_getting_started":false,
"name":"purchase",
"caveats":null,
"updated_at":"2021-11-22T14:47:00.463387Z",
"entity_name":null,
"active":true,
"id":527,
"db_id":101,
"visibility_type":null,
"field_order":"database",
"display_name":"Purchase",
"created_at":"2021-11-18T16:52:59.420615Z",
"points_of_interest":null
},
"name":"created",
"fingerprint_version":5,
"has_field_values":"search",
"settings":null,
"caveats":null,
"fk_target_field_id":null,
"dimensions":[

],
"updated_at":"2021-12-14T12:54:47.521072Z",
"custom_position":0,
"effective_type":"type/DateTime",
"active":true,
"parent_id":null,
"id":7327,
"last_analyzed":"2021-12-14T12:47:20.336106Z",
"position":4,
"visibility_type":"normal",
"preview_display":true,
"display_name":"Created",
"database_position":5,
"name_field":null,
"fingerprint":{
"global":{
"distinct-count":7015,
"nil%":0.0
},
"type":{
"type/Text":{
"percent-json":0.0,
"percent-url":0.0,
"percent-email":0.0,
"percent-state":0.0,
"average-length":20.0
}
}
},
"created_at":"2021-11-18T16:53:17.707529Z",
"base_type":"type/Text",
"points_of_interest":null
}

6

@AnatoleC
Hmmm... strange, not sure what the problem is with BigQuery, but I've created an issue for it:
https://github.com/metabase/metabase/issues/19355 - upvote by clicking :+1: on the first post
Try converting the column to an actual datetime or timestamp. That's the only workaround currently.

7

Done.

Thanks for your help. Any ETA for the resolution of this kind of bug ? Is it a matter of days, weeks, months ?

8

@AnatoleC I cannot tell you. Not sure if it is a limitation of BigQuery or not, or why it was not included. Will have to see what the developers say.
But from a performance standpoint, I would recommend that you use real timestamps, since queries are much quicker with those compared to converting from string/number.