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

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 <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 how to fix it.

--
Andrew (irc:RhodiumToad)

--
Angular momentum makes the world go 'round.

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 how to 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 how to 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 how to 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