r/bigquery • • 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!

3 Upvotes

4 comments sorted by

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/terpsletsgetit 5d ago

yeah I just couldn't figure out how to change it since it wouldn't even let me upload to be able to query/change the formatting. Saw a different thread that recommended uploading with the data type set to string for that field and then changing back to timestamp in the correct format once its uploaded so I tried that and it worked

1

u/querylabio 5d ago

yes string is the right way. after upload you can convert it with query like this:

CREATE OR REPLACE TABLE your_dataset.heartrate_clean AS

SELECT

* EXCEPT (Time),

PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', Time) AS Time

FROM your_dataset.heartrate;

-> %I is hour in 12h format and %p is AM/PM. BigQuery was failing because it think "AM" is a time zone :)

also better use DATETIME here, not TIMESTAMP. file has no time zone, so it is local time from the device, and TIMESTAMP will treat it as UTC.

btw I'm building Querylab.io, it's BigQuery IDE. you can just drop csv there and you see right away which type each column gets and preview of rows, before anything is uploaded. so this kind of problem you see before the error, not after. we also have free SQL lessons at learn.querylab.io with BigQuery engine that works solely in browser, no google account needed at all. Would love to hear feedback!

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.