SQL BigQuery - How to convert date(2020-11-14 16:04:15 UTC) to WEEKDAY value (0-6)-CodePudding

I am currently working through this. Here's some relevant documentation

I have a time date column in { 2020-11-14 16:04:15 UTC } format. I want to end up with a column with an integer 1-7 corresponding to the day of the week. 1 = Sunday and 7 = Saturday.

Currently, I have this that works for a specific date.

SELECT EXTRACT(DAYOFWEEK FROM DATE '2013-12-25') AS the_day

How could I apply this function to a whole column?

thank you for any help.

SQL BigQuery

CodePudding user response：

Adapted from the documentation of date functions 'Extract' example 2. https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ar-YE#extract

SELECT
  date,
  EXTRACT(DAYOFWEEK FROM date) AS the_day
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;

CodePudding user response：

Below is the example.

WITH
org_table AS (
    SELECT DATE('2013-12-25') as org_col
    UNION ALL SELECT '2013-12-26'
    UNION ALL SELECT '2013-12-28'
    UNION ALL SELECT '2013-12-29'
)
SELECT
  org_col,
  EXTRACT(DAYOFWEEK FROM org_col) AS the_day
FROM org_table
ORDER BY org_col
;