我正在尝试将数据从UTC转换为不同的欧洲时区。我正在使用案例语句,发现只执行了案例语句中的第一个条件,而没有检查其他条件。
SELECT sale_id,appointment_time,timezone,
case when timezone = 'EDT' then (appointment_time + interval '-4' HOUR * 1)
when timezone = 'BST' then (appointment_time + interval '1' HOUR * 1)
when timezone = 'CEST' then (appointment_time + interval '2' HOUR * 1)
when timezone = 'EEST' then (appointment_time + interval '3' HOUR * 1)
when timezone = 'MSK' then (appointment_time + interval '3' HOUR * 1)
when timezone = 'WEST' then (appointment_time + interval '1' HOUR * 1)
else null
end as NewTime
FROM sales
谁能建议我哪里出错了。谢谢
你错过了其他只是在结束之前添加并使用dateadd
函数
SELECT sale_id,appointment_time,timezone,
case when timezone = 'EDT' then dateadd(h,-4,appointment_time)
when timezone = 'BST' then dateadd(h,1,appointment_time)
--------------
--------------
else null
end as NewTime
FROM sales
为什么不使用内置convert_timezone函数。它会更快,因为您不需要使用案例
SELECT sale_id, appointment_time, timezone,
convert_timezone(timezone, appointment_time) as NewTime
FROM sales