Обсуждение: day interval

Hi

A question on day interval

select date('20191001') - date('20190923');

Will provide sometimes '8' - an integer , but sometimes '8 day' - a string

How can I control it to return integer always?

Thanks

Danny

>>>>> "Abraham" == Abraham, Danny <danny_abraham@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)

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 <andrew@tao11.riddles.org.uk>
Sent: Saturday, October 12, 2019 6:26 PM
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: day interval

>>>>> "Abraham" == Abraham, Danny <danny_abraham@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,
forexample 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
tofix it. 

--
Andrew (irc:RhodiumToad)

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 <andrew@tao11.riddles.org.uk> 
Sent: Saturday, October 12, 2019 6:26 PM
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: day interval

"Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:

Abraham> HiAbraham> 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)

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@tao11.riddles.org.uk>
> Sent: Saturday, October 12, 2019 6:26 PM
> To: Abraham, Danny <danny_abraham@bmc.com>
> Cc: pgsql-general@postgresql.org
> Subject: [EXTERNAL] Re: day interval
> 
>>>>>> "Abraham" == Abraham, Danny <danny_abraham@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
howto fix it.
 
> 
> --
> Andrew (irc:RhodiumToad)
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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@aklaver.com> 
Sent: Saturday, October 12, 2019 7:27 PM
To: Abraham, Danny <danny_abraham@bmc.com>; Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-general@postgresql.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@tao11.riddles.org.uk>
> Sent: Saturday, October 12, 2019 6:26 PM
> To: Abraham, Danny <danny_abraham@bmc.com>
> Cc: pgsql-general@postgresql.org
> Subject: [EXTERNAL] Re: day interval
> 
>>>>>> "Abraham" == Abraham, Danny <danny_abraham@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
howto fix it.
 
> 
> --
> Andrew (irc:RhodiumToad)
> 
> 
> 


--
Adrian Klaver
adrian.klaver@aklaver.com

>>>>> "Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:

 Abraham> The problematic code is:
 Abraham> select date(cm_curr_date) - date(CM_DATE) into diff_days from CMS_SYSPRM;

This will always return an integer, unless either the date() cast or the
-(date,date) operator have been redefined or modified.

 Abraham> The fix is:
 Abraham> select date_part ('day', age( date(cm_curr_date), date(CM_DATE))) into diff_days from CMS_SYSPRM;

This doesn't do the same thing, it will give a different result if the
dates differ by a month or more.

 Abraham> The problem:
 Abraham> How to recreate the problem.  (You know - QA).

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

None of these things can affect data types.

-- 
Andrew (irc:RhodiumToad)

Thanks for the clarification.

The problem is still this:
select date('20191001') - date('20190101') ;
in my servers it is always '273'.
In the customer's DB it is '273 days';

Thanks

Danny

-----Original Message-----
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
Sent: Saturday, October 12, 2019 7:53 PM
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: day interval

>>>>> "Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:

 Abraham> The problematic code is:
 Abraham> select date(cm_curr_date) - date(CM_DATE) into diff_days from CMS_SYSPRM;

This will always return an integer, unless either the date() cast or the
-(date,date) operator have been redefined or modified.

 Abraham> The fix is:
 Abraham> select date_part ('day', age( date(cm_curr_date), date(CM_DATE))) into diff_days from CMS_SYSPRM;

This doesn't do the same thing, it will give a different result if the dates differ by a month or more.

 Abraham> The problem:
 Abraham> How to recreate the problem.  (You know - QA).

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

None of these things can affect data types.

--
Andrew (irc:RhodiumToad)

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.klaver@aklaver.com>
> Sent: Saturday, October 12, 2019 7:27 PM
> To: Abraham, Danny <danny_abraham@bmc.com>; Andrew Gierth <andrew@tao11.riddles.org.uk>
> Cc: pgsql-general@postgresql.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@tao11.riddles.org.uk>
>> Sent: Saturday, October 12, 2019 6:26 PM
>> To: Abraham, Danny <danny_abraham@bmc.com>
>> Cc: pgsql-general@postgresql.org
>> Subject: [EXTERNAL] Re: day interval
>>
>>>>>>> "Abraham" == Abraham, Danny <danny_abraham@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
howto fix it.
 
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>>
>>
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

>>>>> "Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:

 Abraham> Thanks for the clarification.
 Abraham> The problem is still this:
 Abraham> select date('20191001') - date('20190101') ;
 Abraham> in my servers it is always '273'.
 Abraham> In the customer's DB it is '273 days';

Then you need to establish why that is.

For example, try these in psql on the customer's db and show us the
outputs:

\dT *.date
\df *.date

select castsource::regtype,
       casttarget::regtype,
       castfunc::regprocedure,
       castcontext,
       castmethod
  from pg_cast c join pg_type t on (casttarget=t.oid)
 where typname='date';

select oprresult::regtype       
  from pg_operator
       join pg_type t1 on (t1.oid=oprleft)
       join pg_type t2 on (t2.oid=oprright)
 where oprname='-' and t1.typname='date' and t2.typname='date';

-- 
Andrew (irc:RhodiumToad)

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:
>  Abraham> The problem is still this:
>  Abraham> select date('20191001') - date('20190101') ;
>  Abraham> in my servers it is always '273'.
>  Abraham> In the customer's DB it is '273 days';

> Then you need to establish why that is.

I recall having heard that EDB installs some non-PG datetime operators
to make things act more similar to Oracle.

            regards, tom lane

Thanks everyone.
EDB installs oracle compatible parameters.
See below

C:\Program Files\edb\as9.6\bin>psql -p5488 postgres enterprisedb
Password for user enterprisedb:
psql (9.6.2.7)
...
### Oracle compatible mode
postgres=# select date('20191001') - date('20190101');
 ?column?
----------
 273 days
(1 row)

## Postgres compatible mode
postgres=# set edb_redwood_date=off;
SET

postgres=# select date('20191001') - date('20190101');
 ?column?
----------
      273
(1 row)

-----Original Message-----
From: Andrew Gierth <andrew@tao11.riddles.org.uk>
Sent: Saturday, October 12, 2019 8:48 PM
To: Abraham, Danny <danny_abraham@bmc.com>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: day interval

>>>>> "Abraham" == Abraham, Danny <danny_abraham@bmc.com> writes:

 Abraham> Thanks for the clarification.
 Abraham> The problem is still this:
 Abraham> select date('20191001') - date('20190101') ;  Abraham> in my servers it is always '273'.
 Abraham> In the customer's DB it is '273 days';

Then you need to establish why that is.

For example, try these in psql on the customer's db and show us the
outputs:

\dT *.date
\df *.date

select castsource::regtype,
       casttarget::regtype,
       castfunc::regprocedure,
       castcontext,
       castmethod
  from pg_cast c join pg_type t on (casttarget=t.oid)  where typname='date';

select oprresult::regtype
  from pg_operator
       join pg_type t1 on (t1.oid=oprleft)
       join pg_type t2 on (t2.oid=oprright)  where oprname='-' and t1.typname='date' and t2.typname='date';

--
Andrew (irc:RhodiumToad)

On 10/13/19 12:50 AM, Abraham, Danny wrote:
> 
> Thanks everyone.
> EDB installs oracle compatible parameters.

To be clear this is for their Postgres Advanced Server, not the one you 
would download from here:

https://www.postgresql.org/download/windows/


> See below
> 
> C:\Program Files\edb\as9.6\bin>psql -p5488 postgres enterprisedb
> Password for user enterprisedb:
> psql (9.6.2.7)
> ...
> ### Oracle compatible mode
> postgres=# select date('20191001') - date('20190101');
>   ?column?
> ----------
>   273 days
> (1 row)
> 
> ## Postgres compatible mode
> postgres=# set edb_redwood_date=off;
> SET
> 
> postgres=# select date('20191001') - date('20190101');
>   ?column?
> ----------
>        273
> (1 row)


-- 
Adrian Klaver
adrian.klaver@aklaver.com