BigQuery将RFC1123日期格式字符串转换为unixtime

Question

我正在解析一些HTTP报头字段，我的输入是RFC1123格式的日期时间。例如："Wed，21 Nov 2012 23:10:20 GMT“

我需要将上面的字符串转换为BigQuery中的标准TIMESTAMP date类型，这意味着我需要将其转换为YYYY-MM-DD HH:MM:SS.uuuuuu。我想知道前进的最好方法是什么。我最初的方法是多次使用REGEXP_REPLACE，但我相信还有更好的方法。

使用strftime格式的输入格式为'%a，%d%b%Y%H：%M：%S‘，需要以某种方式转换为YYYY-MM-DD HH:MM:SS

让我知道

Answer 1

在Python中，你可以这样做：

import datetime
from dateutil import parser
date_object = parser.parse('Wed, 21 Nov 2012 23:10:20 GMT')
date_object.strftime('%Y-%m-%d %H:%M:%S GMT')

Answer 2

在SQL中解决这个问题的方法过于复杂：

SELECT TIMESTAMP(SUBSTR(date, 13, 4)+'/'+month_number+'/'+SUBSTR(date, 6, 2)+' '+SUBSTR(date, 18, 8)) ts
FROM
  (SELECT 'Wed, 21 Aug 2012 23:10:20 GMT' date) a
CROSS JOIN
  (SELECT STRING(POSITION(month)) month_number, month FROM (SELECT SPLIT(months) month
   FROM (SELECT 'Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec' months))) b
WHERE SUBSTR(date, 9, 3)=b.month

将(SELECT 'Wed, 21 Aug 2012 23:10:20 GMT' date)替换为您自己的表。

Answer 3

我最终在下面的SQL中找到了一个乏味但有效的解决方案，我相信它有很好的机会让它变得更高效

    select DATE(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
         regexp_replace(
          regexp_replace(
            regexp_replace(
               regexp_replace(<insert_date>,
                           r'(Mon|Tue|Wed|Thu|Fri|Sat|Sun)[,] ',''),
            r' GMT',''),
         r'Jan', '01'),
         r'Feb', '02'),
         r'Mar', '03'),
         r'Apr', '04'),
         r'May', '05'),
         r'Jun', '06'),
         r'Jul', '07'),
         r'Aug', '08'),
         r'Sep', '09'),
         r'Oct', '10'),
         r'Nov', '11'),
         r'Dec', '12'),
         r'(\S+)\s(\S+)\s(\S+)\s(\S+)',r'\3-\2-\1 \4'))
         from <table>;