You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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
The text was updated successfully, but these errors were encountered:
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
The text was updated successfully, but these errors were encountered: