时间戳

首页 > 解决方案 > 我想从日期中提取月份和日期

sql - 我想从日期中提取月份和日期

问题描述

我的代码

SELECT 
    PARSE_DATE('%Y%m%d', CAST(date AS STRING)) AS date, 
    EXTRACT(DAY from date) AS day_of_month,
    EXTRACT(MONTH from date) AS week_of_year,  
    channelGrouping, deviceCategory, sessions, conversions,

我想将列中的日期（格式为 20170101）转换为 2017-01-01。

然后我想从中提取月份和日期。但是我不断收到错误消息：

参数类型的函数 EXTRACT 没有匹配的签名：DATE_TIME_PART FROM INT64。支持的签名：EXTRACT(DATE_TIME_PART FROM DATE); EXTRACT(DATE_TIME_PART FROM TIMESTAMP [AT TIME ZONE STRING]); 提取（DATE_TIME_PART FROM DATETIME）；EXTRACT(DATE_TIME_PART FROM TIME) [1:60]

标签： sqlgoogle-bigquery

解决方案

BigQuery 标准 SQL 的以下几个选项 - 避免多次预解析

选项1

SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_month,
  EXTRACT(WEEK FROM date) AS week_of_year,  
  channelGrouping, deviceCategory, sessions, conversions, 
FROM `project.dataset.table`, 
UNNEST([PARSE_DATE('%Y%m%d', CAST(date AS STRING))]) date

选项 2

SELECT 
  date, 
  EXTRACT(DAY FROM date) AS day_of_month,
  EXTRACT(WEEK FROM date) AS week_of_year,  
  channelGrouping, deviceCategory, sessions, conversions, 
FROM (
  SELECT * REPLACE(PARSE_DATE('%Y%m%d', CAST(date AS STRING)) AS date)  
  FROM `project.dataset.table`
)

推荐阅读