CODEWITHSUNDEEP
sqlcsvgoogle-bigquerytableau-api
matt_black
matt_black

Reputation: 1330

Odd error with casting to timestamp in standard SQL/Tableau

The latest version of Tableau has started using standard SQL when it connects to Google's BigQuery.

I recently tried to update a large table but found that there appeared to be errors when trying to parse datetimes. The table originates as a CSV which is loaded into BigQuery where further manipulations happen. The datetime column in the original CSV contain strings in ISO standard date time format (basically yyyy-mm-dd hh:mm). This saves a lot of annoying manipulation later.

But on trying to convert the datetime strings in Tableau into dates or datetimes I got a bunch of errors. On investigation they seemed to come from BigQuery and looked like this:

Error: Invalid timestamp: '2015-06-28 02:01'

I thought at first this might be a Tableau issue so I loaded a chunk of the original CSV into Tableau directly where the conversion of the string to a data worked perfectly well.

I then tried simpler versions of the conversion (to a year rather than a full datetime) and they still failed. The generated SQL for the simplest conversion looks like this:

SELECT
  EXTRACT(YEAR
  FROM
    CAST(`Arrival_Date` AS TIMESTAMP)) AS `yr_Arrival_Date_ok`
FROM
  `some_dataset`.`some_table` `some_table`
GROUP BY
  1

The invalid timestamp in the error message always looks to me like a perfectly valid timestamp. And further analysis suggests it doesn't happen for all the rows in the source table, just occasional ones.

This error did not appear in older versions of Tableau/BigQuery where legacy SQL was the default for Tableau. So i'm presuming it is a consequence of standard SQL.

So is there an intermittent problem with casting to timestamps in BigQuery? Or is this a Tableau problem which causes the SQL to be incorrectly formatted? And what can I do about it?

Upvotes: 0

Views: 1397

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

The seconds part in the canonical timestamp representation required if the hour and minute are also present. Try this instead with PARSE_TIMESTAMP and see if it works:

SELECT
  EXTRACT(YEAR
    FROM
      PARSE_TIMESTAMP('%F %R', `Arrival_Date`)) AS `yr_Arrival_Date_ok`
FROM
  `some_dataset`.`some_table`.`some_table`
GROUP BY
  1

Upvotes: 1

Related Questions