时间戳

首页 > 解决方案 > 如何使用 SQL 从日期列中提取会计月份编号而不使用 case 语句

sql - 如何使用 SQL 从日期列中提取会计月份编号而不使用 case 语句

问题描述

目前我正在使用下面的案例语句提取月份数字

WHEN cast(estimated_arrival_datetime as date)  >= cast('2019-12-29' as date) AND cast(estimated_arrival_datetime as date) <= cast('2020-02-01' as date) THEN 1
WHEN cast(estimated_arrival_datetime as date)  >= cast('2020-02-02' as date) AND cast(estimated_arrival_datetime as date) <= cast('2020-02-29' as date) THEN 2
WHEN cast(estimated_arrival_datetime as date)  >= cast('2020-03-01' as date) AND cast(estimated_arrival_datetime as date) <= cast('2020-03-28' as date) THEN 3
WHEN cast(estimated_arrival_datetime as date)  >= cast('2020-03-29' as date) AND cast(estimated_arrival_datetime as date) <= cast('2020-05-02' as date) THEN 4
WHEN cast(estimated_arrival_datetime as date)  >= cast('2020-05-03' as date) AND cast(estimated_arrival_datetime as date) <= cast('2020-05-30' as date) THEN 5

但是有没有更好的方法来提取月份数字，日期列也将包含 2021 年的数据

编辑：使用红移

标签： sqldateamazon-redshift

解决方案

不幸的是，Redshift 似乎并不直接支持 ISO 月份。您可以从 ISO 周转换：

(case when to_char(estimated_arrival_datetime, 'IW') <= '04' then 1
      when to_char(estimated_arrival_datetime, 'IW') <= '08' then 2
      when to_char(estimated_arrival_datetime, 'IW') <= '13' then 3
      when to_char(estimated_arrival_datetime, 'IW') <= '17' then 4
      when to_char(estimated_arrival_datetime, 'IW') <= '21' then 5
      when to_char(estimated_arrival_datetime, 'IW') <= '26' then 6
      when to_char(estimated_arrival_datetime, 'IW') <= '30' then 7
      when to_char(estimated_arrival_datetime, 'IW') <= '34' then 8
      when to_char(estimated_arrival_datetime, 'IW') <= '39' then 9
      when to_char(estimated_arrival_datetime, 'IW') <= '43' then 10
      when to_char(estimated_arrival_datetime, 'IW') <= '47' then 11
      else 12
end)

这应该适用于任何一年。

推荐阅读