Search Postgresql Archives

RE: Re: day interval

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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).

Tried changing lc_time, timezone and datestyle .. but nothing seems to work

Thanks

Danny

-----Original Message-----
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 
Sent: Saturday, October 12, 2019 7:27 PM
To: Abraham, Danny <danny_abraham@xxxxxxx>; Andrew Gierth <andrew@xxxxxxxxxxxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxx>
> Sent: Saturday, October 12, 2019 6:26 PM
> To: Abraham, Danny <danny_abraham@xxxxxxx>
> Cc: pgsql-general@xxxxxxxxxxxxxx
> Subject: [EXTERNAL] Re: day interval
> 
>>>>>> "Abraham" == Abraham, Danny <danny_abraham@xxxxxxx> 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.klaver@xxxxxxxxxxx




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux