Converting Decimal Date Format from Airtable to Standard Timestamp in BigQuery

Other Questions Airtable
1

Hey folks, I’m stuck with a date conversion issue. I’ve got some data from Airtable that’s showing up weird in BigQuery. Check this out:

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

The left column is what I’m seeing in BigQuery, but I need it to look like the right column. Anyone know a good query to fix this? I’ve been banging my head against the wall trying to figure it out. Help a newbie out!

PS: I tried looking online, but couldn’t find anything that worked. If you’ve dealt with this before, I’d really appreciate some tips. Thanks!

2

hey miat, i’ve seen this before! it’s a weird excel thing. try this query:

SELECT
decimal_date,
TIMESTAMP_ADD(TIMESTAMP ‘1899-12-30’, INTERVAL CAST(decimal_date * 24 * 60 * 60 AS INT64) SECOND) AS normal_date
FROM your_table

this should convert those funky numbers to regular dates. lemme know if it works!

3

I’ve encountered this issue before when working with data from Excel-based systems. Those decimal numbers actually represent days since December 30, 1899 (Excel’s epoch date). To convert these in BigQuery, you can use a combination of DATE_ADD and TIMESTAMP functions.

Here’s a query that should work for you:

SELECT
  decimal_date,
  TIMESTAMP_ADD(TIMESTAMP('1899-12-30'), INTERVAL CAST(decimal_date * 86400 AS INT64) SECOND) AS converted_timestamp
FROM your_table

This query first multiplies the decimal date by 86400 (number of seconds in a day) to get the total seconds, then adds that interval to the Excel epoch date. The CAST to INT64 is necessary because INTERVAL expects an integer.

Remember to replace ‘your_table’ with your actual table name. This should give you timestamps in the format you’re looking for. Hope this helps!

4

I’ve dealt with similar date conversion challenges before. The decimal format you’re seeing is actually days elapsed since December 30, 1899 - a common epoch used in spreadsheet applications. To convert this in BigQuery, you can leverage the TIMESTAMP_SECONDS function along with some arithmetic.

Try this query:

SELECT
decimal_date,
TIMESTAMP_SECONDS(CAST((decimal_date - 25569) * 86400 AS INT64)) AS converted_timestamp
FROM your_table

The ‘25569’ constant adjusts for the difference between Unix epoch (1970-01-01) and the spreadsheet epoch (1899-12-30). Multiply by 86400 to convert days to seconds. This should produce the timestamp format you’re after.

Let me know if you encounter any issues with this approach.