SUBSTR does not work with datatype "timestamp" in Postgres 8.3

Question

I have a problem with the query below in 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

**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?

Answer 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()

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.)

Answer 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

Answer 3

A variation on the Quassnoi's answer:

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

Answer 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.

In most of the cases you should use extract() or to_char() functions.

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.

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:

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