Date subtraction returns the integer data type; timestamp subtraction
returns the interval datatype.
postgres@haggis:~$ psql test
psql (9.6.15)
Type "help" for help.
test=# select date('20191001') - date('20190923');
?column?
----------
8
(1 row)
test=# select date('2019-10-01') - date('2019-09-23');
?column?
----------
8
(1 row)
test=# select cast('2019-10-01 00:00:00.000' as timestamp) -
cast('2019-09-23 00:00:00.000' as timestamp);
?column?
----------
8 days
(1 row)
test=# select pg_typeof(cast('2019-10-01 00:00:00.000' as timestamp) -
cast('2019-09-23 00:00:00.000' as timestamp));
pg_typeof
-----------
*interval*
(1 row)
On 10/12/19 10:37 AM, Abraham, Danny wrote:
Thanks Andrew.
My code fails since the expression (In a PG/PG SQL function) which assumes
integer result
Now produces the string '8 day';
This has been working for years on all PG community servers.
This happens on an EDB PG 9.6.3.
I know the fix, but I need the ability to create the bug in my server, and I do
not know how.
Thanks
Danny
-----Original Message-----
From: Andrew Gierth <and...@tao11.riddles.org.uk>
Sent: Saturday, October 12, 2019 6:26 PM
To: Abraham, Danny <danny_abra...@bmc.com>
Cc: pgsql-gene...@postgresql.org
Subject: [EXTERNAL] Re: day interval
"Abraham" == Abraham, Danny <danny_abra...@bmc.com> writes:
Abraham> Hi
Abraham> A question on day interval
Abraham> select date('20191001') - date('20190923');
Abraham> Will provide sometimes '8' - an integer , but sometimes '8 Abraham>
day' - a string
No, it'll always return an integer. You will only get an interval result if you
subtract timestamps rather than dates, for example if one of the operands is
actually an expression returning a timestamp.
Give an example of an actual expression you used that returned an interval
instead, and we may be able to tell you how to fix it.
--
Andrew (irc:RhodiumToad)
--
Angular momentum makes the world go 'round.
