CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-bigquery
Felipe Hoffa
Felipe Hoffa

Reputation: 59225

formatting timestamp from BigQuery to Google Spreadsheet

I'm creating a spreadsheet that hits BigQuery with Apps Script. Here's part of my query:

SELECT
  id,
  USEC_TO_TIMESTAMP(created) AS created,
  USEC_TO_TIMESTAMP(modified) AS modified,
  USEC_TO_TIMESTAMP(resolved) AS resolved,
  status_id,
FROM
  [mydata:mydataset.latest]
WHERE  blah blah blah...

When I run this from http://bigquery.cloud.google.com, I get timestamps that look like "2013-08-22 19:19:01 UTC" (this is great). However, when I run the same query from a spreadsheet, I get dates that don't make sense. If I remove the function USEC_TO_TIMESTAMP, the dates still don't make sense. What I mean by "don't make sense", is that if I format the numbers as dates, I get garbage values. What is the recommended way to query dates from a spreadsheet?

(from a mailing list)

Upvotes: 0

Views: 1481

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

You should use FORMAT_UTC_USEC() instead of USEC_TO_TIMESTAMP().

Upvotes: 3

Related Questions