如何修改SQL查询结果时区，合并两个查询实现日期字段时区转换

合并后可直接运行的查询代码 #

SELECT
max(CASE WHEN `label` = 'Order Number' THEN `response` else '' END)as `Order Number`,
max(CASE WHEN `label` = 'Date' THEN Date_format(Convert_TZ(STR_TO_DATE(`response`, '%Y-%c-%dT%H:%i:%s'), '+00:00', '+10:00'), '%Y-%c-%d %H:%i:%s') else '' END)as `Date`,
max(CASE WHEN `label` = 'Creditor Name' THEN `response` else '' END)as `Creditor Name`,
max(CASE WHEN `label` = 'Purchaser' THEN `response` else '' END)as `Purchaser`,
max(CASE WHEN `label` = 'Farm' THEN `response` else '' END)as `Farm`,
max(CASE WHEN `label` = 'Quantity' THEN `response` else '' END)as `Quantity`,
max(CASE WHEN `label` = 'Description' THEN `response` else '' END)as `Description`,
max(CASE WHEN `label` = 'Allocation' THEN `response` else '' END)as `Allocation`
From `inspection_items`
where `template_id` = 'template_xxxx' and `type` not like 'section' and `type` not like 'information' and `type`not like 'element' and `type` not like 'dynamicfield'
group by `parent_id`, `audit_id`
order by `created_at`,`item_index`

改动说明 #

• 仅修改了label为Date对应的CASE分支逻辑，把原来直接取response值的部分替换为你提供的时区转换代码，其余查询逻辑完全和你原有的正常运行版本保持一致
• 转换逻辑匹配你的需求：先把response存储的ISO格式时间字符串转为datetime类型，再从UTC时区（+00:00）转换为东十区（+10:00），最后格式化为年-月-日 时:分:秒的展示格式

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