Timestamp value in OAC URL is getting converted automatically

Shaik Sunny-Oracle

I have created a data action to navigate from Canvas A to canvas B using URL navigation by using date as anchor data

URL looks like <base workbook URL>&p1n=BUCKET_START_DATE&p1v=${valuesForColumn:START_DAY_DATE}

the value for BUCKET_START_DATE gets automatically converted to a different value where as I want exact value to be assigned to parameter

example : <base workbook URL>&p1n=BUCKET_START_DATE&p1v=06/09/2024%2012:00:00.000%20am

"06/09/2024 12:00:00.000 am" this timestamp in URL changes to "08/06/2024 06:30:00.000 pm" on the canvas

Gianni Ceresa

Hi,

Are you maybe based in India or a region in the area or have set that as your date/time?

The 2 dates to me looks the same, once corrected of the time zone.

For example I believe India is at GMT+5:30, if you take midnight and remove 5h30 you get 6:30pm, and that's why you get a date in your URL that is 5h30 earlier than what you clicked. The URL is passing a GMT date, not a local time date.

There seem to be a recurrent theme about dates and times in Oracle Analytics being changed "on the fly" (without saying anything) to GMT. Or assuming any provided datetime is in GMT if no time zone component is included and then displaying it in the local time zone instead.

Maybe Oracle should consider investing some work in improving the datetime management, to allow to explicitly define for each column what time zone it is in or saying to not convert it to GMT?

This isn't a direct answer on how you can change the behavior you are seeing, but an explanation of what is going on…

Shaik Sunny-Oracle

Hi,

Thank you for your reply.

yes, I am based in India. [GMT+5:30].

I understood that the date component 12:00 am changes to 06:30 pm as the India is at GMT +5:30.
But the date 06/09/2024 [dd/mm/yyy] changed to 08/06/2024 instead it should have change to 05/09/2024

is there some configuration I can change to specify the date format and time zone without having Oracle analytics to change them on the fly.

Gianni Ceresa

"06/09/2024 12:00:00.000 am" this timestamp in URL changes to "08/06/2024 06:30:00.000 pm"

Let say it's right while being very much wrong…

06/09/2024 is dd/mm/yyyy, but the system took it as mm/dd/yyyy, and removed 1 day to it to get 06/08/2024 (in mm/dd/yyyy) and this is where 08/06/2024 (in dd/mm/yyyy) comes from.

I'm not saying it is right, I just see a "logic" (not a correct one) in that change of date.

And that's why I would call for a real, proper, clean, support of datetime in the whole DV: no messing around with implicit masks or time zone conversion: what you have is what you see if not asked the tool to do something else. Format masks, time zones, all should be customizable at the column level everywhere.

Right now my feeling is that there are many assumptions on defaults formats and time zone (US format and GMT time zone), and there is a lack of proper support for a data type that isn't as obvious as some developer maybe thought. And when the UI tries to be smart by converting things (that could even be a javascript thing messing around with data on rendering), it mostly generate issue.

Would be interesting to have an internal (Oracle) opinion on how datetime is currently managed in detail in DV and the various behaviors and impact of the settings (visual only, on the data, in queries etc.).

[Deleted User]

Not commenting on the why but this really has to be turned into a Service Request do there can be a proper root cause analysis and a bug opened for this.

@Shaik Sunny-Oracle please open an SR and send the SR number to Product Management once it's been opened.

Thank you.