Error operator does not exist interval integer

У меня есть запрос, который работает на Postgresql 7.4, но не на Postgresql 8.3 с той же базой данных .... Вопрос о: sql, postgresql.

Оператор не существует: интервал> целое число

У меня есть запрос, который работает на Postgresql 7.4, но не на Postgresql 8.3 с той же базой данных.

Запрос:

SELECT * FROM login_session WHERE (now()-modified) > timeout;

Выдает следующую ошибку:

ERROR:  operator does not exist: interval > integer
LINE 1: ...ELECT * FROM login_session WHERE (now()-modified) > timeout ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Колонка modified — это timestamp и timeout есть integer.

Есть ли какие-то настройки, которые мне нужно изменить на сервере?

Я устанавливаю приложение для клиента на новый сервер (ubuntu), поэтому я не могу изменять запросы в приложении.

create or replace function int2interval (x integer) returns interval as $$ select $1*'1 sec'::interval $$ language sql;
create cast (integer as interval) with function int2interval (integer) as implicit;

должен это сделать.

Создан 14 ноя.

Между 7.4 и 8.3 много изменений. Одними из самых радикальных было удаление некоторых автоматических слепков.

Полагаю, «тайм-аут» в секундах? Если да, вы можете изменить запрос на:

SELECT
    *
FROM
    login_session
WHERE
    (CURRENT_TIMESTAMP - modified) > (timeout * '1 sec'::interval);

Создан 14 ноя.

CREATE OR REPLACE FUNCTION intToInterval(arg integer)
  RETURNS interval AS
$BODY$
   BEGIN      
      return CAST( arg || ' seconds' AS interval ); 
   END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE CAST (integer AS interval)
WITH FUNCTION intToInterval ( integer )
AS IMPLICIT;

(Предполагая, что тайм-аут измеряется в секундах — в противном случае измените соответственно)

Создан 14 ноя.

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

sql
postgresql

or задайте свой вопрос.

Источник

Comments

@imsky

Are there plans to support the interval type in Postgres? It’s currently mapped to an unknown type, but it would be really useful to be able to compare it, filter by interval length, etc.

⬇️ Please click the 👍 reaction instead of leaving a +1 or update? comment

CzechJiri, amywtlin, chamini2, jonjomckay, sliverc, vikeri, joevandyk, snarfed, flamber, ijoseph, and 31 more reacted with thumbs up emoji

@camsaul

@imsky this one might be a little tricky since we’d have to introduce some new widgets for selecting time intervals (e.g. 5 minutes / 1 hour / 2 days)

@imsky

@camsaul could a MVP be something like direct entry of seconds? would be great to start using this with a feature flag. also, it would be nice to be able to group by rounded interval (e.g. group by number of hours, number of weeks, etc.)

@camsaul

@imsky do you know of the top of your head if Postgres lets you compare an interval field to a numeric value (e.g. WHERE my_interval > 1) ? If it does it would be pretty simple to just treat intervals as numbers for the time being.

@imsky

Not as far as I know. I get the following messages when trying that:

[42883] ERROR: operator does not exist: interval > integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

casting to integer also doesn’t work:

[42846] ERROR: cannot cast type interval to integer

What does work is using extract with intervals:

select extract(epoch from time_since_event) => (date_part) 1234.56

though this may not be useful in all cases.

@CzechJiri

small sample (tested in postgres 9.3) …

CREATE TABLE sample (duration interval );
INSERT INTO sample VALUES('3 days 4 hours');
COMMIT;
SELECT duration FROM sample WHERE duration > interval '1000 seconds';
SELECT duration FROM sample WHERE date_part('epoch', duration) >  1000;
SELECT duration FROM sample WHERE extract('epoch' from duration) >  1000;

I would personally pick the first option, its very clean and lets you extend the SQL in the future easily by adding days/hours/… if you decide to extend the UI

SELECT duration 
  FROM sample 
 WHERE duration > interval '0 years 0 months 0 days 0 hours 0 minutes 1000 seconds'

@imsky

@CzechJiri it does seem cleaner to just have seconds in the query and seconds/minutes/hours in the UI

@CzechJiri

inserted some sample data

INSERT INTO SAMPLE 
SELECT descr FROM (
SELECT generate_series(1,10000000) AS id, ('3 days 2 hours ' || (random() * 10000)::int::varchar || ' minutes')::interval AS descr
                ) bla;
COMMIT;

then ran few tests (the generation of sample data above is heavily skewed I admit), getting 10-30% better performance on the 1st option (ran tests with and without btree index).

All 3 options certainly work, extract will be slowest (it internally uses date_part ), using no function (1st option) will in general be fastest unless you introduce functional indexes.

@camsaul

I think for the time being we can just convert intervals to seconds or something like that so at the very least you can use the numeric widgets in the query builder instead of getting an error

@chamini2

I don’t know if this helps, but Postgres supports the ISO 8601 interval standard: P1Y2M10DT2H30M -> 1 year 2 months 10 days 2 hours 30 minutes.

@snarfed

i’d also love to see intervals in query results supported, eg visualizing them in charts. right now they seem to be interpreted as strings.

for now, i’ve ended up doing this with EXTRACT EPOCH, eg for hours:

EXTRACT(EPOCH from [INTERVAL]) / 60 / 60 AS num_hours

This was referenced

Mar 1, 2020

Источник

У меня есть запрос, который работает на Postgresql 7.4, но не на Postgresql 8.3 с той же базой данных.

Запрос:

SELECT * FROM login_session WHERE (now()-modified) > timeout;

Получает следующую ошибку:

ERROR:  operator does not exist: interval > integer
LINE 1: ...ELECT * FROM login_session WHERE (now()-modified) > timeout ...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Столбец modified — это timestamp, а timeout — это integer.

Есть ли какие-то настройки, которые мне нужно изменить на сервере?

Я устанавливаю приложение для клиента на новый сервер (ubuntu), поэтому я не могу изменять запросы в приложении.

3 ответа

Лучший ответ

create or replace function int2interval (x integer) returns interval as $$ select $1*'1 sec'::interval $$ language sql;
create cast (integer as interval) with function int2interval (integer) as implicit;

Должен это сделать.


3

geocar
14 Ноя 2008 в 18:39

Между 7.4 и 8.3 много изменений. Одними из самых радикальных было удаление некоторых автоматических слепков.

Полагаю, «тайм-аут» в секундах? Если да, вы можете изменить запрос на:

SELECT
    *
FROM
    login_session
WHERE
    (CURRENT_TIMESTAMP - modified) > (timeout * '1 sec'::interval);


11

Milen A. Radev
14 Ноя 2008 в 21:40 

CREATE OR REPLACE FUNCTION intToInterval(arg integer)
  RETURNS interval AS
$BODY$
   BEGIN      
      return CAST( arg || ' seconds' AS interval ); 
   END;
$BODY$
  LANGUAGE 'plpgsql';

CREATE CAST (integer AS interval)
WITH FUNCTION intToInterval ( integer )
AS IMPLICIT;

(Предполагая, что время ожидания измеряется в секундах — в противном случае измените соответственно)


1

alasdairg
14 Ноя 2008 в 18:47

Источник

Понравилась статья? Поделить с друзьями:
  • Error operator does not exist integer text
  • Error operator does not exist character varying bytea
  • Error operation failed active console session exists for this domain
  • Error openvz vps is not supported
  • Error opening zip file or jar manifest missing idea