gpt4 book ai didi

sql - Postgres 将字符串转换为时间

转载 作者:行者123 更新时间:2023-11-29 13:23:28 25 4
gpt4 key购买 nike

我有以下查询:

SELECT id, start_date::TIME, occurrence->0->>'startsOn'  FROM service WHERE name='A.F';

哪个返回:

id  | start_date |            ?column?             
------+------------+---------------------------------
1573 | 18:00:00 | Mon, 29 Jun 2015 18:00:00 +0000
1592 | 10:00:00 | Wed, 24 Jun 2015 10:00:00 +0000
1605 | 18:00:00 | Thu, 25 Jun 2015 18:00:00 +0000
1571 | 10:00:00 | Mon, 29 Jun 2015 10:00:00 +0000
1591 | 20:15:00 | Tue, 30 Jun 2015 20:15:00 +0000
1578 | 18:00:00 | Mon, 29 Jun 2015 20:00:00 +0000
1620 | 12:00:00 | Sat, 27 Jun 2015 12:00:00 +0000
(7 rows)

我想做的是将 occurrence->0->>'startsOn' 转换为时间,因此预期结果应该是:

  id  | start_date |            ?column?             
------+------------+---------------------------------
1573 | 18:00:00 | 18:00:00
1592 | 10:00:00 | 10:00:00
1605 | 18:00:00 | 18:00:00
1571 | 10:00:00 | 10:00:00
1591 | 20:15:00 | 20:15:00
1578 | 18:00:00 | 20:00:00
1620 | 12:00:00 | 12:00:00

我尝试了以下方法:

SELECT id, start_date::TIME, occurrence->0->>'startsOn'::TIME  FROM service WHERE name='A.F';

But it is not working as it gives me the following syntax error:
ERROR: invalid input syntax for type time: "startsOn"

最佳答案

select ('[{"startsOn":"Mon, 29 Jun 2015 18:00:00 +0000"}]'::json->0->>'startsOn')::timestamp::time

我没有“发生”列,所以我从你的输出中模拟了它

关于sql - Postgres 将字符串转换为时间,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37791958/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com