SUBSTR不适用于Postgres 8.3中的数据类型“timestamp”

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?

我假设它与数据类型有关,数据是一个时区,而substring只支持字符串数据类型,现在我的问题是我可以对我的查询做些什么,以便我的结果出现?

5 个解决方案

#1

The explicit solution to your problem is to cast the datetime to string.

您的问题的显式解决方案是将日期时间转换为字符串。

...,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 need is to change your query using the explicit datetime manipulation, comparison and formatting functions, 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.)

或者下面的替代方案之一(你应该接受而不是这个。)

#2

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

#3

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

#4

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.

我不确定你想要实现什么,但是日期数据类型上的“substring”基本上没有很好地定义,因为它取决于所述数据的外部格式。

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:

对于extract,让我们编写一个简单的查询,列出在晚上8点之后创建的所有对象:

select * from objects where extract(hour from created) >= 20;

#5

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(没有时区整数整数的时间戳)不存在

Use:

select('SUBSTRING(CONCAT(offer.date_closed, \'\'), 0, 11)')

#1