Bigquery Datetime format csv to bigquery YYYY-MM-DD HH:MM[:SS[.SSSSSS]]

Question

Read many datetime formating questions out there, but can't find one that fits. -Obviously- already tried to google this.

I have some datetime info on a csv file that I'm uploading to bigquery to create a new table. BQ specifies that the format for any datetime columns must be YYYY-MM-DD HH:MM:SS.

They also gave the example:

Event            | UNIX      | Datetime String
---------------------------------------------------------
"Neil Armstrong  | -14182916 | 1969-07-20 20:18:04
sets foot on     |           | 1969-07-20 20:18:04 UTC
the moon"        |           | 1969-07-20T20:18:04

However when I format the datetime column as YYYY-MM-DD HH:MM:SS in excel, and upload that csv to BQ, I get the error message:

Errors:
mediaupload-snapshot: CSV table encountered too many errors, giving up. 
Rows: 1; errors: 1. (error code: invalid)
query: Could not parse '2017/11/22 14:47:23 SGT' as a timestamp. 
Required format is YYYY-MM-DD HH:MM[:SS[.SSSSSS]]; Could not parse 
'2017/11/22 14:47:23 SGT' as datetime for field Start_Time (position 0) 
starting at location 492 (error code: invalidQuery)

When I try to convert the csv column to YYYY-MM-DD HH:MM[:SS[.SSSSSS]] using excel, I'm told it's not a legal format.

When I try YYYY-MM-DD HH:MM:SS \U\T\C or YYYY-MM-DD\THH:MM:SS, it looks good in excel, BUT I get the same error message from BQ as above.

What's the right way to store data in CSV so that it's compatible with BQ?

Answer 1

I ran into this same issue and got this working using the following format: YYYY-MM-DDTHH:MM:SS.00Z. I used the following examples from the TIMESTAMP data type as a reference: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#examples

At the time I was attempting to import a DATETIME field from MySQL and used the following function to format the value DATE_FORMAT(created, '%Y-%m-%dT%H:%I:%S.00Z') in my SELECT query.