Re: day interval

On 10/12/19 9:34 AM, Abraham, Danny wrote:
> The problematic code is:
> select date(cm_curr_date) - date(CM_DATE) into diff_days from CMS_SYSPRM;
>
>
> The fix is:
> select date_part ('day', age( date(cm_curr_date), date(CM_DATE))) into diff_days from CMS_SYSPRM;
>
> The problem:
> How to recreate the problem. (You know - QA).

Upstream you said:

"This has been working for years on all PG community servers.

This happens on an EDB PG 9.6.3."

Which version of the EDB Postgres database are you using e.g. their
install of the community version or their modified(Advanced?) version?

When I search on CMS_SYSPRM it comes related to bmc.com, is that in the
mix also?

>
> Tried changing lc_time, timezone and datestyle .. but nothing seems to work
>
> Thanks
>
> Danny
>
> -----Original Message-----
> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Sent: Saturday, October 12, 2019 7:27 PM
> To: Abraham, Danny <danny_abraham(at)bmc(dot)com>; Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: [EXTERNAL] Re: day interval
>
> On 10/12/19 8: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';
>
> The code is?
>
>>
>> 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 <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
>> Sent: Saturday, October 12, 2019 6:26 PM
>> To: Abraham, Danny <danny_abraham(at)bmc(dot)com>
>> Cc: pgsql-general(at)postgresql(dot)org
>> Subject: [EXTERNAL] Re: day interval
>>
>>>>>>> "Abraham" == Abraham, Danny <danny_abraham(at)bmc(dot)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)
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com