sql - 根据时间戳派生新列

Question

我有一个数据类型为文本的“customer_date”列。

根据我需要导出新列的时间

条件:

• when customer_date <12 then 'AM' else 'PM

customer_date

01Mar2018 10:03:54
02Mar2018 13:03:54

预期输出

  customer_date             session

01Mar2018 10:03:54        AM
02Mar2018 13:03:54        PM

当我写的时候它会抛出错误

select 
case EXTRACT(HOUR FROM customer_date)<12 then 'AM' else 'PM' end as session
from my table;

Answer 1

这在我测试时有效:

select (case when EXTRACT(HOUR FROM customer_date::timestamp) < 12 then 'AM' else 'PM' end) as session
from mytable;

我添加了显式转换和when。