我至少有10年没有使用PostgreSQL了,对它从来都不太熟悉。我现在发现自己不得不处理PostgreSQL数据库中JSONB属性中嵌入的日期+时间字符串-而且没有取得多大成功。我对查询嵌入在任何关系数据库中的JSON一无所知(当然,除了它是错误的-但我不能在这里修复)。
如果相关的话,服务器端是13.10,我用于测试的psql客户端是9.2
我可以检索数据:
SELECT id, ddata->'domain'->'created_at'
FROM schema.mytable
WHERE id=100
id | ?column?
--------+---------------------------------
100 | "Tue, 13 Mar 2018 16:10:01 GMT"
但是我不知道如何让PostgreSQL将值解析为日期。
我尝试过的一些事情和相关的错误....
SELECT id, TO_DATE(ddata->'domain'->'created_at', '"Dy, DD Mon YYYY HH24:MI:SS GMT"')
FROM myschema.mytable
WHERE id = 100
No function matches the given name and argument types
SELECT id, ddata->'domain'->'created_at'.datetime()
FROM myschema.mytable
WHERE id = 100
syntax error at or near "."
SELECT id, jsonb_path_query(ddata->'domain'->'created_at', '$[*].datetime("HH24:MI")')
FROM myschema.mytable
WHERE id = 100
ERROR: invalid value "Tu" for "HH24"
DETAIL: Value must be an integer.
SELECT id, jsonb_path_query(ddata->'domain'->'created_at', '$[*].datetime("Dy, DD Mon YYYY HH24:MI:SS GMT")')
FROM myschema.mytable
WHERE id = 100
ERROR: trailing characters remain in input string after datetime format
(the如果我能找到一种将类型转换为字符串的方法,那么上面的块中的第一次尝试看起来很有希望。我找不到这样做的方法)
最后,我想选择日期小于N天的记录-但只有在PostgreSQL解析日期时才能做到这一点。
属性中原始数据的简化版本为:
{
"domain": {
"created_at": "Tue, 13 Mar 2018 16:10:01 GMT"
}
}
更新
自从发布后,我发现了->>
运算符。然而,这并没有多大帮助; TO_TIMESTAMP(ddata->'domain'->>'created_at', '"Dy, DD Mon YYYY HH24:MI:SS"')
返回所有值的0001-01-01 00:00:00+00 BC
。
1条答案
按热度按时间sigwle7e1#
对于
timestamp
:首先使用
->
将'domain'
对象的内容提取为JSON,然后使用->>
将'created_at'
对象的内容提取为text
。然后使用::date
强制转换转换为date
或timestamp(tz)
值。