Discussions

Modify the timezone of Insight Analysis

Pierre-Ibnou
Pierre-Ibnou Posts: 2 Green Ribbon

Hello,

Currently the timezone of Insight analysis is set on the Oracle server timezone (UTC-5) and cannot be modified. It generates confusion for the users when the activity is held in a different timezone.

It would be great if there was a feature to select the timezone of the Analysis, or just an option in the default settings. Is this enhancement feasible ?

Thank you.

Pierre-Ibnou

13 votes

Under Oracle Review · Last Updated

The idea is under consideration by Oracle

Comments

  • derek.bell
    derek.bell Posts: 73 Silver Medal

    Greetings from Sydney, Australia @Pierre-Ibnou This is a problem I've seen with US software companies for 20+ years. We use the expression the sun rises in New York and sets in L.A. :-) But for this specific case, the sun rises in New York and sets in New York.

    UTC-5 is US Eastern Standard Time - New York (I'm sure you knew that). Insight never reflects Summer or Daylight Saving Time.

    On the same subject - what's the date? "4/8/2022"? Is it 4th of August or the 8th of April? This one kills me.

    Why not display all dates as "4 Aug 2022" or the US could catch up to the rest of the planet and do DD/MM/YYYY.

    Anyhoo, I think you'll find this request will quietly slip away into oblivion. 😎

  • Krishna Maurya
    Krishna Maurya Posts: 4 Red Ribbon

    Hi @Pierre-Ibnou and @derek.bell,

    We are also facing the same issue with the time zone when we generate reports from insight.

    Regards,

    Krishna Maurya

  • It is not a solution to the actual problem but I have a workaround that I use quite often.

    When you open the insights report in Edit mode and click on the settings wheel of the date field you can do 'Edit Formula'.

    In this formula you will typically just see the name of the field. What you do here is just paste the following formula and replace the name of the field (everything between " ") with the fieldname on your screen:

    TIMESTAMPADD(SQL_TSI_HOUR, 6, “Email Clickthrough Attributes”.“Email Clickthrough Date/Time”)

    This will take the date field (3rd parameter) and add 6 hours to it. The original date will be between GMT-5 and GMT-4 depending on Daylight Saving Time. Depending on your timezone you can change the '6' to correct it to yours. You can also use negative values like -6 to subtract hours.

    It's not ideal but you can add this formula in all your reports and resolve the problem until Oracle has a fix.

    If you really want to go next-level and modify the format of the date you can also use other functions in the formula field to make your own, in some cases where you don't have 'month' etc available it can be useful to create your own (like 2022-01).

    Here is an example to get 'Week' from a date field get your started.

    TRIM(CAST(YEAR("Contact Custom Obj 1"."ColDate2") AS CHAR)) || '-W' || TRIM(CAST(WEEK("Contact Custom Obj 1"."ColDate2") AS CHAR))

    The result is: 2022-W10

    To strip it down it's quite easy:

    • Find the name of your date field (you get this when you click edit formula on the field)
    • Determine what you want to see (YEAR, DAY, WEEK, MONTH, YEAR) and put it in this formula (replace the name of the field): TRIM(CAST(YEAR("Contact Custom Obj 1"."ColDate2") AS CHAR))
    • Concatenate using || and put the next value in the same way
    • If you want to put text in between you just use || 'text' ||

    Final point to clean it up:

    • Changing the formula also changes the column header of the field, on the edit formula screen select 'Custom Headings' and enter your own name of the field