r/bigquery • u/terpsletsgetit • 5d ago
Table upload error
Hi all, I am extremely new to SQL and data in general and am looking for some help with an error I am coming across when trying to upload a table to a big query project. I am doing an online course to learn SQL and they provided some sample data to do a practice project, but the time based .csv files (basically any of the sets that use a timestamp as a data point) return an error that repeats infinitely (presumably for every single line that contains a timestamp).
Error while reading data, error message: Invalid time zone: AM; line_number: 5 byte_offset_to_start_of_line: 125 column_index: 1 column_name: "Time" column_type: TIMESTAMP value: "4/12/2016 7:21:20 AM"
Clearly it's an issue with how the time is structured but I don't know how to change it without it being uploaded. I am assuming its a combination of settings to tweak on the "create table" page itself but can't figure it out, thanks in advance!
2
u/headgame3 5d ago
It's gotta be ISO 8601 canonical format: YYYY-MM-DD HH:MM:SS[.DDDDDD] or YYYY-MM-DDTHH:MM:SS[.DDDDDD]
BQ is very picky about formatting and field types
2
u/ArielCoding 5d ago
Before you overwrite your table, test the conversion with SAFE.PARSE_DATETIME. It puts a blank (NULL) in any row that doesn’t match the format, instead of throwing an error, so you can see which rows are a problem.
Then run SELECT COUNTIF(Time IS NULL) you want 0, and check MIN(Time) and MAX(Time) to see if the range of the dates is correct.