Bigquery Datetime format csv to bigquery YYYY-MM-DD HH:MM[:SS[.SSSSSS]]
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.
angsty_robot
Updated on June 30, 2022Comments
-
angsty_robot almost 2 years
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
orYYYY-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?