代码之家 › 专栏 › 技术社区 › udis

将具有PM和AM的字符串转换为时间戳Bigquery

google-bigquery sql

udis · 技术社区 · 2 年前

我知道有很多帖子与这个问题有关,但我还没有弄清楚。我有一个字符串字段,其中包含以下Unix时间戳值:

Feb 27, 2021 14:11:25 PM    
Dec 07, 2020 04:48:44 AM    
Dec 07, 2020 04:48:35 AM    
Dec 07, 2020 04:48:55 AM    
Dec 07, 2020 04:51:51 AM

我不知道为什么这个领域有PM和am,但我需要围绕它工作。

我想将此字段转换为BigQuery表中的TIMESTAMP。我尝试了几种方法:

    MIN(PARSE_TIMESTAMP('%b %d, %Y %H:%i:%s', REPLACE(REPLACE(messagecreatedat, ' AM', ''), ' PM', ''))) AS min_messagecreatedat,

和

MIN(
  COALESCE(
    PARSE_TIMESTAMP('%b %d, %Y %I:%M:%S %p', messagecreatedat),
    PARSE_TIMESTAMP('%b %d, %Y %H:%M:%S', messagecreatedat)
  )
) AS min_messagecreatedat,

两者都不起作用。我明白错误

无法解析输入字符串“2021年3月16日22:17:27”

0 回复 | 直到 2 年前

udis 2 年前

这最终对我有效

 MIN(PARSE_TIMESTAMP('%b %d, %Y %T %p', messagecreatedat, 'UTC')) AS min_messagecreatedat,

Mikhail Berlyant 2 年前

请参阅下面的示例(BigQuery Standard SQL),其中包含两个解决问题的方法

WITH your_table AS (
  SELECT 'Feb 27, 2021 14:11:25 PM' AS messagecreatedat UNION ALL
  SELECT 'Dec 07, 2020 04:48:44 AM' UNION ALL
  SELECT 'Dec 07, 2020 04:48:35 AM' UNION ALL
  SELECT 'Dec 07, 2020 04:48:55 AM' UNION ALL
  SELECT 'Mar 16, 2021 22:17:27' UNION ALL
  SELECT 'Dec 07, 2020 04:51:51 AM' 
)
SELECT 
  messagecreatedat, 
  PARSE_TIMESTAMP('%b %d, %Y %T', REGEXP_REPLACE(messagecreatedat, ' [AP]M$', ''), 'UTC') AS messagecreatedat_1, 
  IFNULL(
    SAFE.PARSE_TIMESTAMP('%b %d, %Y %T %p', messagecreatedat, 'UTC'),
    SAFE.PARSE_TIMESTAMP('%b %d, %Y %T', messagecreatedat, 'UTC')
  ) messagecreatedat_2
FROM your_table

带输出