Badly formatted TIMESTAMPs

[vanetten]

I was getting weird results from a TIMESTAMP column. After some research, came up with the following workaround.

Under the covers, BigQuery stores timestamps as a UNIX timestamps, not date/time strings as you get from the BigQuery console.

"TIMESTAMP data types can be described in two ways: UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision. UNIX timestamps A positive or negative decimal number. A positive number specifies the number of seconds since the epoch (1970-01-01 00:00:00 UTC), and a negative number specifies the number of seconds before the epoch. Up to 6 decimal places (microsecond precision) are preserved."

To resolve this, I told the table's model to interpret the TESTDATE column as a decimal, then I converted the UNIX timestamp to a UTC date/time string.

class << columns_hash['TESTDATE'] def type :decimal end end

def TESTDATE Time.at(self[:TESTDATE]).utc end

Now the TESTDATE(s) come out correctly.

s = Table.find(id)

before: s.TESTDATE => Sat, 01 Jan 1972 00:00:00 UTC +00:00

after:
s.TESTDATE => 2000-10-30 00:00:00 UTC