From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: day interval |
Date: | 2019-10-12 15:50:46 |
Message-ID: | f742f6a4-985a-18b1-734f-fdb0bcbaca48@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Date subtraction returns the integer data type; timestamp subtraction
returns the interval datatype.
postgres(at)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(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)
>
>
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-10-12 16:26:51 | Re: day interval |
Previous Message | Abraham, Danny | 2019-10-12 15:37:33 | RE: Re: day interval |