Conversion of Oracle Query to PostgreSQL

Question

I am posting a part of my query which is in Oracle and it as follows:

cast(from_tz(cast((Select max(d.startdate) from Public.result_slalom d 
               where d.eventid = a.eventid 
               and d.modifydate = (Select max(e.modifydate) from result_slalom e 
                where e.eventid = d.eventid)) as timestamp), 'Asia/Calcutta') at Time Zone 'Europe/Berlin' as date) as OpenLastTime,

I want to run this query on PostgreSQL. So I wrote a query which looks like this for PostgreSQL:

(Select Cast(to_timestamp(max(d.startdate)) 
                      from Public.result_slalom d 
               where d.eventid = a.eventid 
               and d.modifydate = (Select max(e.modifydate) from Public.result_slalom e
                where e.eventid = d.eventid) as timestamp, 'Asia/Calcutta') at Time Zone 'Europe/Berlin' as date) as OpenLastTime,

I am getting a few errors here and there, which I believe are related to some bracket issues. Or due to the keyword 'timestamp' in the second select clause for e.modifydate.

Any help would be much appreciated. Thanks in advance. :)

This will not work in either database because `a` is not defined. I would suggest that you simplify your actual query and then rephrase the question or ask another. — Gordon Linoff, Sep 19 '17 at 12:17
What exactly are the "few errors here and there" you get? Sorry for asking, but my crystal ball is broken. — waka, Sep 19 '17 at 12:18

score 0 · Answer 1 · answered Sep 19 '17 at 14:49

In the future, please try to include a full reproducible example that we can actually run. I made an example fiddle that shows how you could do this.

from_tz is not a Postgres function. Also, the date type in Postgres does not have a time or timezone component - it's just year-month-day. Your startdate and modifydate columns will need to be of type timestamp.

Here's my conversion:

select ((Select max(d.startdate) from result_slalom d 
           where d.eventid = a.eventid and 
           d.modifydate = (Select max(e.modifydate) from result_slalom e 
             where e.eventid = d.eventid)) at time zone 'Asia/Calcutta') 
        at Time Zone 'Europe/Berlin' as OpenLastTime
from a;

Conversion of Oracle Query to PostgreSQL

1 Answers1