如何在Big Query中将非Epoch Seconds的数值转换为YYYY-MM-DD格式

首先得搞清楚这类数值对应的日期编码规则，从你给出的示例8082320、8162320、8182320来看，最靠谱的猜测是：数值最后一位是冗余位，前6位是YYMMDD格式（两位年份+两位月份+两位日期）。下面基于这个规则给出转换方法，同时也会说明怎么根据实际规则调整。

操作步骤（前6位为YYMMDD的情况） #

1. 把数值转成字符串，方便截取需要的部分
2. 截取前6位有效日期串
3. 用BigQuery的PARSE_DATE函数按YYMMDD格式解析成日期

示例SQL代码 #

SELECT
  original_num,
  -- 截取前6位，按YYMMDD格式转成日期
  PARSE_DATE('%y%m%d', SUBSTR(CAST(original_num AS STRING), 1, 6)) AS converted_date
FROM
  `你的项目ID.你的数据集ID.你的表名`

示例转换结果 #

其他编码规则的调整方法 #

如果实际规则不是YYMMDD，比如数值是MMDDYYYY但少了一位前导0（比如8082320对应完整的08082320，即2320年8月8日），可以这么改：

SELECT
  original_num,
  -- 补前导0到8位，按MMDDYYYY格式解析
  PARSE_DATE('%m%d%Y', LPAD(CAST(original_num AS STRING), 8, '0')) AS converted_date
FROM
  `你的项目ID.你的数据集ID.你的表名`

要是数值是DDMMYYY格式（比如8082320对应08-08-2320），就把格式符改成%d%m%Y：

SELECT
  original_num,
  PARSE_DATE('%d%m%Y', LPAD(CAST(original_num AS STRING), 8, '0')) AS converted_date
FROM
  `你的项目ID.你的数据集ID.你的表名`

额外说明 #

• 转换的核心是先确定数值对应的日期编码规则，这是前提
• PARSE_DATE的格式符：%y代表两位年份，%Y代表四位年份，%m是两位月份，%d是两位日期
• 如果两位数年份要解析成20xx而不是19xx（比如80对应2080），可以加100年：

  PARSE_DATE('%y%m%d', SUBSTR(CAST(original_num AS STRING), 1, 6)) + INTERVAL 100 YEAR
  

内容的提问来源于stack exchange，提问作者sayanti bhattacharjee