CODEWITHSUNDEEP
sqlpostgresql
ramya
ramya

Reputation: 51

Migrating Oracle query to PostgreSQL

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;

Upvotes: 0

Views: 296

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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');

Upvotes: 0

Related Questions