How to add time Zones — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to add time Zones

Received Response
11
Views
4
Comments
Chaitanya
Chaitanya Rank 4 - Community Specialist

Hi All,

How can i add time Zones?

For Ex: i have current timestamp (30-11-2016 12:42:36 PM).

I selected GMT timezone for Rangoon (GMT +6:30)

Now i want the result like , this GMT +6:30 should add to my Current timestamp 12:42:36.

Can anybody help me to achieve this?

Regards,

Chaitanya

Message was edited by: Chaitanya

Answers

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    where are u using  the timestamp. if it is in column or variables you can use sql_tsi_hour along with timestampadd function.

  • Chaitanya
    Chaitanya Rank 4 - Community Specialist

    Hi Asim,

    Thanks for the reply. I got Hours separately. But my requirement is , i should get the result along with minutes and seconds.

    Ex: Current time is     14:20:00 PM

    GMT time is     GMT (+6:30)

    total hours =              20:50:00

    Regards,

    chaitanya.P

  • asim cholas
    asim cholas Rank 6 - Analytics Lead

    CURRENT_TIME function is available in OBIEE. add it in the column formula and go to properties then choose custom as date format . keep HH:mm:ss as format and select Rangoon from time zone.

  • Chris Arnold
    Chris Arnold Rank 5 - Community Champion

    Adding to what asim cholas said...

    1) If you are getting your time value from a date column from your database and you are wanting to convert it to your timezone to display on your report, then you can use the code below in your column formula:

    TIMESTAMPADD(SQL_TSI_MINUTE, 30, TIMESTAMPADD(SQL_TSI_HOUR, 6, <your_date_column>))

    2) If you are just wanting to display the value of current_timestamp or NOW() in your timezone, then use this:

    TIMESTAMPADD(SQL_TSI_MINUTE, 30, TIMESTAMPADD(SQL_TSI_HOUR, 6, current_timestamp))

    or this

    TIMESTAMPADD(SQL_TSI_MINUTE, 30, TIMESTAMPADD(SQL_TSI_HOUR, 6, NOW()))

    *The reason I'm using the SQL_TSI_MINUTE function is because that is the only way I know how to add the extra 30 minutes. There may be a better way to do that.