Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Oracle Analytics Date Problem of Data Sets

Received Response
11
Views
4
Comments
dilek
dilek Rank 6 - Analytics Lead
edited Aug 13, 2024 5:04PM in Oracle Analytics Cloud and Server

Hi,

I am a newbie on OAS 6.4. I created a data set from an sql connecting database and gets the data from that way. However, I aware that the results of sql (only date columns) on data visualizer not same as results of Toad (application we use as an sql editör) On data visualizer, date columns shows 1 day after.

For example;

Correct value: 31/08/2021

Dv Result: 01/09/2021

Do you have any idea about this issue? If you have, please help me.

Regards,

Dilek

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Isn't it a timezone thing?

    What you see as date is maybe a full date & time field, and DV does apply some timezone offset to translate it into your own timezone. If you were to see the time part of the field, it would be more obvious, but when seeing only the date you maybe see it 1 day later before it did add a bunch of hours.

    Dates & time should be managed in your query as much as possible, making sure to retrieve only a date if you need that and not a date & time.

  • dilek
    dilek Rank 6 - Analytics Lead

    Thank you for your answer @Gianni Ceresa

    I aware that, when I edit a column comes from sql statement on dv, it automatically changes the date value. Editing means can be followings;

    • adding a function (like cast)
    • renaming column name (for ex period -> my_period)

    When I remove these editings, date values are corrected.

    Thank you again.

    Have a good day.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    It all comes down to: What exactly is stored in that column in the table? 31/08/2021 00:00:00? And what is the exact physical data type in the table?

    Then the second thing is: What is the data type in DV? Are you using a normal data set or are you doing any transformations/using a data flow/etc?

    As Gianni indicated there is logic happening in DV and it will try to convert things according to timezone settings.

  • Gianni Ceresa
    Gianni Ceresa Mod

    If your source column has a time component, in your SQL query do a TRUNC() on it. It will remove the time component and DV will maybe stop doing timezone manipulatons on something not having a time.