From: | Christoph Moench-Tegeder <cmt(at)burggraben(dot)net> |
---|---|
To: | Rajesh S <rajesh(dot)s(at)fincuro(dot)com> |
Cc: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: - operator overloading not giving expected result |
Date: | 2022-07-08 12:46:54 |
Message-ID: | YsgnPr6HCEXVsScF@elch.exwg.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
## Rajesh S (rajesh(dot)s(at)fincuro(dot)com):
> We are migrating our database from Oracle to Postgresql. In oracle we
> have used this syntax "SELECT ('1999-12-30'::DATE) -
> ('1999-12-11'::DATE)" to get difference between two dates as a integer
> output (ex: 19). But in Postgres the same query returns result as "19
> days".
There's something fishy going on, as (date) - (date) returns integer
since a very long time (even the version 8.0 docs have that).
On the other hand, (timestamp) - (timestamp) gives an interval, so
first make sure you really got the data types right.
> CREATE OR REPLACE FUNCTION public.dt_minus_dt(
> dt1 timestamp without time zone,
> dt2 timestamp without time zone)
See? There's TIMESTAMP, not DATE.
> LANGUAGE 'edbspl'
Is this really PostgreSQL or is it that fork - and if it's the fork,
does it behave the same way as stock PostgreSQL does? (I would be
surprised if that deviates in this place, but...).
> SELECT DATE_PART('day', dt1::timestamp - dt2::timestamp)::integer
And TIMESTAMP again.
Regards,
Christoph
--
Spare Space
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-07-08 14:13:31 | Re: - operator overloading not giving expected result |
Previous Message | Francisco Olarte | 2022-07-08 11:38:39 | Re: - operator overloading not giving expected result |