Migrating Oracle query to PostgreSQL

Question

Can you please help me with this? How can I convert below query to PostgreSQL.

The query below gives different output when executed in PostgreSQL than when executed in Oracle.

SELECT 
  to_char(to_date('01011970','ddmmyyyy') + 1/24/60/60 * 4304052,'dd-mon-yyyy hh24:mi:ss') 
from dual;

Try not to tag with irrelevant things. C++ is not a factor here. — tadman
– tadman, Commented May 30, 2019 at 15:48
Even in Oracle this is a bad query - adding a number to a date and then trying to convert it to a localized string. That number is calculated based on multiple integer division which result in 0s and then an integer multiplication that also results in 0 — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 30, 2019 at 16:04
What does this query try to do in the first place? Convert a Unix timestamp to an actual date? If so, why produce a string at the end? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 30, 2019 at 16:05
o/p postgresql : to_char ---------------------- 01-jan-1970 00:00:00 — ramya
– ramya, Commented May 30, 2019 at 16:07

Kaushik Nayak · Accepted Answer · 2019-05-30 16:38:46Z

Let's assume you want to use the same expression as in Oracle to compute the resulting value.

The reason it is not working when you simply remove from dual is because this expression is being evaluated to 0 as integer division truncates results towards 0.

select 1/24/60/60 * 4304052;
 ?column?
----------
        0
(1 row)

If I make one of them a decimal, it will give you the required result

select 1.0/24/60/60 * 4304052;
          ?column?
-----------------------------
 49.815416666666666347848000

Now, after changing this, your expression will return the same result you got in Oracle.

SELECT  to_char( to_date('01011970','ddmmyyyy')
 +  INTERVAL '1 DAY' *  (1.0/24/60/60 * 4304052) ,'dd-mon-yyyy hh24:mi:ss') ;
       to_char
----------------------
 19-feb-1970 19:34:12
(1 row)

Note that I had to add an interval expression, because unlike Oracle, a Postgres DATE does not store time component and simply adding a number to date will result in an error. Using an interval will ensure that it will be evaluated as timestamp.

knayak=# select pg_typeof( current_date);
 pg_typeof
-----------
 date
(1 row)

knayak=# select pg_typeof( current_date + INTERVAl '1 DAY');
          pg_typeof
-----------------------------
 timestamp without time zone
(1 row)

Gordon Linoff · Accepted Answer · 2019-05-30 16:37:11Z

0

I think you want:

select '1970-01-01'::date + 4304052 * interval '1 second';

You can use to_char() to convert this back to a string, if you really want:

select to_char('1970-01-01'::date + 4304052 * interval '1 second', 'YYYY-MM-SS HH24:MI:SS');

answered May 30, 2019 at 16:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Migrating Oracle query to PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related