如何在BigQuery中将dd / mm / yyyy字符串转换为日期？ [英] How do I cast dd/mm/yyyy string into date in BigQuery?

问题描述

我有3列
1. dd / mm / yyyy（存储为字符串）
2. app_id和#downloads的应用程序

I have 3 columns 1. dd/mm/yyyy (stored as a string) 2. app_id and #downloads of apps

我必须在一周内找到下载的应用的唯一ID。

I have to find unique ids of apps downloaded within a week.

谢谢

推荐答案

yyyy字符串转换为BigQuery时间戳，使用类似以下内容：

You can convert your dd/MM/yyyy strings into BigQuery timestamps using something like the following:

SELECT TIMESTAMP(year + '-' + month + '-' + day) as output_timestamp
FROM (
  SELECT 
    REGEXP_EXTRACT(input_date, '.*/([0-9]{4})$') as year, 
    REGEXP_EXTRACT(input_date, '^([0-9]{2}).*') as day, 
    REGEXP_EXTRACT(input_date, '.*/([0-9]{2})/.*') AS month 
  FROM 
    (SELECT '30/10/2015' as input_date),
    (SELECT '25/01/2015' as input_date)
)

将它们转换为时间戳后，您可能会发现日期和时间函数有用，具体取决于你想要做什么。

Once you have converted them to timestamps, you may find the date and time functions useful, depending on what you're trying to do.

这篇关于如何在BigQuery中将dd / mm / yyyy字符串转换为日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！