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, May 30 '19 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, May 30 '19 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, May 30 '19 at 16:05
o/p postgresql : to_char ---------------------- 01-jan-1970 00:00:00 — ramya, May 30 '19 at 16:07
What output are you getting in oracle, and what in postgres? Can you explain what the query is intended to do? — jmelesky, May 30 '19 at 16:08
@rekha the problem is the original query itself. If you want to convert a Unix timestamp to a date the *easy* way is to just add the seconds to the epoch [as shown in this possibly duplicate question](https://stackoverflow.com/questions/35613644/convert-unix-timestamp-to-date-and-datetime-sql-oracle): `to_date('01011970','ddmmyyyy') + numtodsinterval(1456342438,'SECOND')` — Panagiotis Kanavos, May 30 '19 at 16:09
I need postgresql query.As I am trying to Convert from time_t to Postgresql UTC DATE string in c++ code — ramya, May 30 '19 at 16:10
In oracle EXEC SQL SELECT to_char( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :dbTimestamp, 'dd-mon-yyyy hh24:mi:ss' ) into :dbReceiptTimestamp from dual; — ramya, May 30 '19 at 16:12
@rekha C++ has nothing to do with Oracle, PostgreSQL or epochs. Besides, even in C++ you *wouldn't* just add seconds, you'd use the appropriate function to convert a timestamp to a date. The query you posted is the *wrong* way to work with Oracle to begin with. If you search for `PostgreSQL convert unix timestamp to date` you'll find many duplicate SO questions — Panagiotis Kanavos, May 30 '19 at 16:13
Possible duplicate of [PostgreSQL: how to convert from Unix epoch to date?](https://stackoverflow.com/questions/16609722/postgresql-how-to-convert-from-unix-epoch-to-date) — Panagiotis Kanavos, May 30 '19 at 16:13
As the duplicate shows, the `to_timestamp` converts unix timestamps to dates. ` select to_timestamp( 4304052);` returns `1970-02-19T19:34:12Z`. That's a date value that can be formatted as text if needed — Panagiotis Kanavos, May 30 '19 at 16:16
I got to_timestamp --------------------------- 1970-02-20 01:04:12+05:30 — ramya, May 30 '19 at 16:20

Kaushik Nayak · Answer 1 · 2019-05-30T16:38:46.853

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)

score 0 · Answer 2 · answered May 30 '19 at 16:37

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 '19 at 16:37

Gordon Linoff

1,242,037
58
646
786

Migrating Oracle query to PostgreSQL

2 Answers2