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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
