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?
