Casting a Firebase timestamp to a Date/Time in BigQuery - referencing a computed field again in the same query?

Question

I am not a coder, and very much flying blind, so please excuse the simplicity of this query.

I am streaming Firebase Firestore updates to a BigQuery table using the Firebase extension "Stream collection to BigQuery" which I am then linking as a DataSource in Google Data Studio. This is currently working as intended.

I have 2 questions:

1. Is there a more efficient way to convert a Firebase timestamp into a BigQuery Date/Time value? The Firebase Timestamp shows in JSON format in the BigQuery table as follows:

   {"created_time":{"_seconds":1647554254,"_nanoseconds":234000000}}

   My BigQuery SQL code to convert it (which works) is:

   DATETIME(TIMESTAMP_SECONDS(CAST(JSON_VALUE(DATA,'$.created_time._seconds') AS int64)),"Africa/Johannesburg") AS createDate

   Is there a more efficient way to do this, or is this reasonable?

2. How do I reference the createDate computed field (above) in another computed field ageDays within this same query? I haven't found it in Google or StackOverflow, either because of poor phrasing or its just too basic a query. I tried using a table alias referencing the createDate computed field (e.g. T.createDate) but no dice. My very ugly workaround was therefore just to reperform the createDate calculation in it's entirety (which feels wrong) in my new computed column ageDays as follows:

   DATE_DIFF(current_date("Africa/Johannesburg"),DATETIME(TIMESTAMP_SECONDS(CAST(JSON_VALUE(DATA,'$.created_time._seconds') AS int64)),"Africa/Johannesburg"), DAY) AS ageDays

Would be sincerely grateful for any insights - many thanks.

Answer 1

For your requirement, JSON_EXTRACT can also be used instead of JSON_VALUE. You can use below query to get the expected output.

select
 date(timestamp_seconds(cast(json_extract( data , '$.created_time._seconds') as int64))) AS Date_Created
from `project.dataset.timetable` 

Output

Table alias cannot be used to reference a field in another column with a SELECT statement as it has limited visibility. Alias can be used with Order By, Group By or Having clauses in a SELECT statement. The best way to get the ageDays is by again computing the whole createDate field.