Converting Decimal Date Strings from Airtable to Standard Timestamps in BigQuery

Other Questions Airtable
1

Hey everyone, I’m having trouble with date formats in BigQuery. When I upload data from Airtable, the dates turn into weird decimal strings. Here’s what I mean:

Original Date    | What I Want
-----------------|--------------------
44493.61666667   | 2021-10-24T14:48:00
44522.775        | 2021-11-22T18:36:00
44493.67916666667| 2021-10-24T16:18:00
44522.739583333336| 2021-11-22T17:45:00

Does anyone know how to fix this? I need a query to change these decimal strings into proper timestamps. The table name is Airtable if that helps. Thanks in advance for any tips!

2

I’ve dealt with this exact issue before when migrating data from Airtable to BigQuery. The decimal format is indeed tricky, but there’s a straightforward solution.

In BigQuery, you can use the TIMESTAMP_ADD function along with the base date of December 30, 1899. Here’s a query that should work:

SELECT
original_date,
TIMESTAMP_ADD(TIMESTAMP ‘1899-12-30’, INTERVAL CAST(original_date * 86400 AS INT64) SECOND) AS converted_timestamp
FROM
Airtable;

This query multiplies the original_date by 86400 (seconds in a day) to convert it to seconds, then adds that interval to the base date.

One thing to watch out for: make sure your original_date column is typed as FLOAT or NUMERIC in BigQuery. If it’s coming in as a string, you’ll need to CAST it first.

Hope this helps solve your date conversion headache!

3

I’ve encountered this issue before when working with Airtable data in BigQuery. The solution involves using the TIMESTAMP_ADD function with the base date of December 30, 1899. Here’s a query that should work for you:

SELECT
original_date,
TIMESTAMP_ADD(TIMESTAMP ‘1899-12-30’, INTERVAL CAST(original_date * 86400 AS INT64) SECOND) AS converted_timestamp
FROM
Airtable;

This query multiplies the original_date by 86400 (seconds in a day) to convert it to seconds, then adds that interval to the base date. Make sure your original_date column is typed as FLOAT or NUMERIC in BigQuery. If it’s coming in as a string, you’ll need to CAST it first.

One additional tip: Always double-check the time zone of your converted timestamps to ensure they align with your expectations. You may need to adjust the base date’s time zone if necessary.

4

hey, i’ve run into this before! the trick is to use TIMESTAMP_ADD with dec 30, 1899 as the base. here’s a quick query:

SELECT original_date,
TIMESTAMP_ADD(TIMESTAMP ‘1899-12-30’, INTERVAL CAST(original_date * 86400 AS INT64) SECOND) AS converted_timestamp
FROM Airtable;

make sure original_date is FLOAT/NUMERIC. good luck!