When I make a Query in BigQuery Query Editor (in the browser), using this query on the Public Datasets > nyc-tcl:green dataset.
#standardSQL
select pickup_datetime from nyc-tlc.green.trips_2014
limit 10000
Then the timestamp fields are shown as:
2014-03-27 10:40:00.000 UTC
2014-01-08 06:45:20.000 UTC
When I copy the same query to OPNBI and run it the field is also recognized as a timestamp field but the results are shown like this:
1.3959168E9
1.38916352E9
changing the query to:
#standardSQL
select datetime(pickup_datetime) from nyc-tlc.green.trips_2014
limit 10000
fixes this but it would be nice when timestamp fields return a human readable format like the query does when run in BigQuery itself.
I tend to write the queries in the BQ Query Editor (color-coded, auto-complete, ect.) and copy then to my visualization tool of choice.
If this is the desired behaviour I respect that but as it is known that the field is of a type timestamp it seams strange to me to present it as a float?
You can describe TIMESTAMP data types as either UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision. check here for more information https://cloud.google.com/bigquery/data-types
Currently in OPNBI it support legacy query and standard SQL query support should be added shortly. If this is blocking just send an email to support@opnbi.com and they should provide you patch or you can use the workaround like you did or format timestamp using function FORMAT_UTC_USEC.