Why the query is not recognizing I changed the started_at to started

https://console.cloud.google.com/bigquery?sq=551559631904:7ff0b9b8313c42c79ac143008a0cb243

SELECT
    ride_id, started_at,
    CAST(started_at AS DATETIME) AS started_time,
    CAST(ended_at AS DATETIME) AS ended_time,
    DATETIME_DIFF(CAST(ended_at AS DATETIME),
    CAST(started_at AS DATETIME), MINUTE) as time_diff
FROM
    `fleet-gamma-355414.bikes_data.12_months_data` -- Data from 12 months;

I don't understand why the query says that does not recognize started_time, ended_time,time_diff when the query processes it.

SELECT
    started_time, ended_time, time_diff, 
    EXTRACT(DAYOFWEEK FROM started_at) as the_day
FROM  
    `fleet-gamma-355414.bikes_data.12_months_data`

CodePudding user response：

You have used column aliases in your first query but the underlying column names in fleet-gamma-355414.bikes_data.12_months_data table remained same. So instead of aliases from previous query you need to use actual column names when you are separately querying the table like below:

SELECT started_at , ended_at , DATETIME_DIFF(CAST(ended_at AS DATETIME),
CAST(started_at AS DATETIME), MINUTE), EXTRACT(DAYOFWEEK FROM started_at) as the_day FROM
fleet-gamma-355414.bikes_data.12_months_data