Converting Airtable's decimal timestamp to standard format in BigQuery

Other Questions Airtable
1

Hey everyone, I’m stuck with a weird timestamp issue. I’ve got data from Airtable that looks fine there, but when I put it into BigQuery, the dates turn into these strange decimal numbers. Check it out:

Decimal Time    | Actual Date
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

I’m scratching my head trying to figure out how to change these decimal numbers back into normal dates and times in BigQuery. The table name is Airtable if that helps. Anyone know a good way to fix this? I’d really appreciate some help with the right query to use. Thanks!

2

Hey DancingFox, I’ve dealt with this exact issue before! Those decimal timestamps are a real headache, aren’t they? Here’s what worked for me:

First, you need to understand that these numbers are days since December 30, 1899 (weird, I know). The whole number part is the days, and the decimal is the time.

Try this query in BigQuery:

SELECT
TIMESTAMP_ADD(
TIMESTAMP ‘1899-12-30’,
INTERVAL CAST(FLOOR(Decimal_Time) AS INT64) DAY
) AS date_part,
TIMESTAMP_ADD(
date_part,
INTERVAL CAST((Decimal_Time - FLOOR(Decimal_Time)) * 86400 AS INT64) SECOND
) AS full_timestamp
FROM Airtable

This should convert your decimal timestamps into proper datetime format. The trick is to add the days first, then the seconds for the time part.

Let me know if this works for you. If not, we might need to tweak the base date or the calculation a bit. Good luck!

3

I’ve encountered this issue before when working with Airtable data in BigQuery. Those decimal timestamps can be tricky, but there’s a straightforward solution.

The key is understanding that these numbers represent days since December 30, 1899. Here’s a query that should work for you:

SELECT
TIMESTAMP_ADD(
TIMESTAMP ‘1899-12-30’,
INTERVAL CAST(FLOOR(Decimal_Time) AS INT64) DAY
) AS date_part,
TIMESTAMP_ADD(
date_part,
INTERVAL CAST((Decimal_Time - FLOOR(Decimal_Time)) * 86400 AS INT64) SECOND
) AS full_timestamp
FROM Airtable

This query first adds the whole days, then calculates the remaining time in seconds. It should give you accurate timestamps that match your Airtable data.

If you’re still having issues, double-check that ‘1899-12-30’ is indeed the correct base date for your Airtable setup. Some systems might use a different reference point.

4

hey dancingfox, looks like ur dealing with excel-style dates. try this in bigquery:

SELECT TIMESTAMP_ADD(TIMESTAMP ‘1899-12-30’, INTERVAL CAST(FLOOR(Decimal_Time) AS INT64) DAY) AS converted_date
FROM Airtable

this should give u normal dates. adjust the base date if needed. good luck!