postgresql SUBSTR 不适用于 Postgres 8.3 中的数据类型“时间戳”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1091924/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUBSTR does not work with datatype "timestamp" in Postgres 8.3
提问by Elitmiar
I have a problem with the query below in postgres
我在 postgres 下面的查询有问题
SELECT u.username,l.description,l.ip,SUBSTRING(l.createdate,0,11) as createdate,l.action
FROM n_logs AS l LEFT JOIN n_users AS u ON u.id = l.userid
WHERE SUBSTRING(l.createdate,0,11) >= '2009-06-07'
AND SUBSTRING(l.createdate,0,11) <= '2009-07-07';
I always used the above query in an older version of postgres and it worked 100%. Now with the new version of posgres it gives me errors like below
我总是在较旧版本的 postgres 中使用上述查询，并且它 100% 有效。现在使用新版本的 posgres 它给了我如下错误
**ERROR: function pg_catalog.substring(timestamp without time zone, integer, integer) does not exist
LINE 1: SELECT u.username,l.description,l.ip,SUBSTRING(l.createdate,...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.**
I assume it has something to do with datatypes, that the data is a time zone and that substring only support string datatypes, now my question is what can I do about my query so that my results would come up?
我认为它与数据类型有关，数据是一个时区，并且子字符串仅支持字符串数据类型，现在我的问题是我可以对我的查询做些什么以便我的结果出现？
回答by Vinko Vrsalovic
The explicit solution to your problemis to cast the datetime to string.
您的问题的显式解决方案是将日期时间转换为字符串。
...,SUBSTRING(l.createdate::varchar,...
...,SUBSTRING(l.createdate::varchar,...
Now, this isn't at all a good practice to use the result to compare dates.
现在，使用结果来比较日期根本不是一个好习惯。
So, the good solution to your needis to change your query using the explicit datetime manipulation, comparisonand formattingfunctions, like extract() and to_char()
因此，满足您需求的最佳解决方案是使用显式日期时间操作、比较和格式化函数（如 extract() 和 to_char()）更改查询
You'd have to change your query to have a clause like
你必须改变你的查询来有一个像
l.createdate::DATE >= '2009-06-07'::DATE
AND l.createdate::DATE < '2009-07-08'::DATE;
or one of the alternatives below (which you should really accept instead of this.)
或以下选择之一（您应该真正接受而不是这个。）
回答by Quassnoi
SELECT u.username, l.description, l.ip,
CAST(l.createdate AS DATE) as createdate,
l.action
FROM n_logs AS l
LEFT JOIN
n_users AS u
ON u.id = l.userid
WHERE l.createdate >= '2009-06-07'::TIMESTAMP
AND l.createdate < '2009-07-07'::TIMESTAMP + '1 DAY'::INTERVAL
回答by Quassnoi
I'm not sure what you want to achieve, but basically "substring" on date datatypes is not really well defined, as it depends on external format of said data.
我不确定您想要实现什么，但基本上日期数据类型上的“子字符串”并没有真正定义好，因为它取决于所述数据的外部格式。
In most of the cases you should use extract()or to_char()functions.
在大多数情况下，您应该使用extract()或to_char()函数。
Generally - for returning data you want to_char(), and for operations on it (including comparison) - extract(). There are some cases where this general rule does not apply, but these are usually signs of not really well thought data-structure.
通常 - 用于返回您想要的数据 to_char()，以及对其的操作（包括比较） - extract()。在某些情况下，这条一般规则不适用，但这些通常表明数据结构没有经过深思熟虑。
Example:
例子：
# select to_char( now(), 'YYYY-MM-DD');
to_char
------------
2009-07-07
(1 row)
For extract let's write a simple query that will list all objects created after 8pm:
对于提取，让我们编写一个简单的查询，列出晚上 8 点之后创建的所有对象：
select * from objects where extract(hour from created) >= 20;
回答by Milen A. Radev
A variation on the Quassnoi's answer:
Quassnoi 答案的变体：
SELECT
u.username,
l.description,
l.ip,
CAST(l.createdate AS DATE) as createdate,
l.action
FROM
n_logs AS l
LEFT JOIN
n_users AS u
ON
(u.id = l.userid)
WHERE
l.createdate::DATE BETWEEN '2009-06-07'::DATE AND '2009-07-07'::DATE
回答by Athlan
If you use Postgresql, you will receive:
如果您使用 Postgresql，您将收到：
select('SUBSTRING(offer.date_closed, 0, 11)')
function substr(timestamp without time zone integer integer) does not exist
函数 substr(timestamp without time zone integer integer) 不存在
Use:
用：
select('SUBSTRING(CONCAT(offer.date_closed, \'\'), 0, 11)')
