Geek Answers Handbook

SUBSTR does not work with the timestamp type in Postgres 8.3

I have problem with request 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 request in an older version of postgres, and it worked 100%. Now with the new posgres version this gives me errors as 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 suppose it has something to do with data types, that data is a time zone and that the substring only supports row data types, now my question is what can I do with my query so that my results appear?

+5
source share
5 answers

An explicit solution to your problem is to pass the date and time to a string.

..., SUBSTRING (l.createdate :: VARCHAR, ...

.

, datetime, formatting, extract() to_char()

, ,

l.createdate::DATE >= '2009-06-07'::DATE 
AND l.createdate::DATE < '2009-07-08'::DATE;

( .)

+6
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
+5

:

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
+1

, , "" , .

extract() to_char().

- to_char() ( ) - extract(). , , .

:

# select to_char( now(), 'YYYY-MM-DD');
  to_char
------------
 2009-07-07
(1 row)

, , , 8 :

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

Postgresql, :

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

substr ( )

:

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

More articles:


All Articles