difference between implicit/explicit cast of integer to interval

Поиск
Список
Период
Сортировка
От raf
Тема difference between implicit/explicit cast of integer to interval
Дата
Msg-id 20120702042555.GA10319@raf.org
обсуждение исходный текст
Ответы Re: difference between implicit/explicit cast of integer to interval  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
hi,

postgresql-9.1.3

the difference between two dates is an integral
number of days as demonstrated by:

    select select date '2012-08-03' - date '2012-08-01';
     ?column?
    ----------
            2

i just noticed that i had some (buggy) code that
stored the difference between two dates into an
interval variable rather than integer variable.
the result was that is was storing the interval
2 seconds rather than the integer 2 (to represent
days). so when i added that interval to another date,
it had no effect.

strangely, i tried to explicitly cast such an integral
number of days into an interval and it failed to allow it.
the following code demonstrates this. the first two notices
are output but an error happens before the third.

    do $$
    declare
        huh1 interval;
        huh2 integer;
    begin
        huh1 := date '2012-08-03' - date '2012-08-01';
        raise notice '%', huh1;
        huh2 := date '2012-08-03' - date '2012-08-01';
        raise notice '%', huh2;
        huh1 := cast(date '2012-08-03' - date '2012-08-01' as interval);
        raise notice '%', huh1;
    end
    $$;

    DO
    00:00:02
    2
    ERROR:  cannot cast type integer to interval
    LINE 1: SELECT cast(date '2012-08-03' - date '2012-08-01' as interva...
                   ^
    QUERY:  SELECT cast(date '2012-08-03' - date '2012-08-01' as interval)

i supposed my questions are:

Q1) if an integer cannot be explicitly cast into an interval, what is happening
when a date difference is stored in an interval variable to allow it to happen?

Q2) when it happens, why is the number of days stored in the interval as seconds
rather than days?

Q3) is a bug in plpgsql?

cheers,
raf


В списке pgsql-general по дате отправления:

Предыдущее
От: David Fetter
Дата:
Сообщение: Test, please ignore.
Следующее
От: Tom Lane
Дата:
Сообщение: Re: difference between implicit/explicit cast of integer to interval