Resolving confusions about using Dates and Times for Visualization

Options
tgb417
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron

In this thread:

https://community.dataiku.com/t5/Knowledge-Base/How-to-remove-scientific-notation-in-a-column/tac-p/11907

I asked:

"Can one use this approach to change the time zone of a parsed date time to the local time zone? In the Western Hemisphere always showing parsed date time in GMT causes challenges for analysts and those consuming the insights created by analysts."

The approach in that post was about using the format command to cause a column not to show scientific notation. Something like

format("%.5f", my_column_name)

@jfyuen
kindly commented.

"As a rule of thumb, you never want to manipulate date using string formatting but date functions. You will run into daylight saving time issues, calendar problems, ...

To avoid such problems, DSS only displays dates in UTC.

For your problem, you can use the format date processor and select a static timezone (or a timezone column) to format the date.

However, values will be converted to strings and won't be dates anymore. Be careful when using them afterwards other than for display."

I did not understand so I further asked

"Can one use these time zone corrected strings in charts as dates with the real date features of the charting system?"

I meant something like

2010-06-21T07:00:00.000PST Or 2010-06-21T07:00:00.000-700

Then @jfyuen
you kindly responded.

"Unfortunately no, it will only be treated as string.

You can however "cheat" by reconverting the parsed date to a new date with a standard format date to the UTC timezone. It will be displayed with the "good" time while staying UTC under the hood.

However, only use that column for display and not computation as it will differ from your real data."

Which also left me confused.

So I go to playing.

Here is what I've worked out so far.

Step 1: Parse Dates as usual.

Parse to GMT.jpg

Step 2: Then Format the GMT time to Local time zone, Say Pacific Time. However in this case store the data as a string, not a date. (If you store this as a date it will revert to UTC)

Format as Date back in local time Zone.jpg

Step 3: Go and make sure that the type of the column to String not Date. (This is important for making this work around work.)

Step 4: Now when I'm on a chart, I can actually build a chart that works with Local times.

Graph using Local Dates.jpg

@jfyuen
Is this what you were trying to suggest? Or have I missed your point? Is this a good "cheating" workaround?

To the rest of the community. Is this useful? Am I missing something here? It feels like a lot of work to get the built-in Charting to work.

Answers

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options
  • jfyuen
    jfyuen Dataiker, Registered Posts: 12 Dataiker
    Options

    Thanks a lot for the detailed screenshots. In your example, I think the data in the chart would still be displayed in UTC. With what I had in mind, I added one extra step to cast the string back to a UTC date set to local time.

    Edit: actually, you can just parse the "reg_time" as UTC (but that would be in local time) by setting the "Default timezone" as UTC. It will directly create a UTC date with the local time, so having the same effect as the first suggestion. As before, this column should only be used for display as it won't reflect the real time with timezone and DST.

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    When I have a few minutes I’ll create a manual dataset and see if I can move dates and times around a single day.

    if some one else wants to give this a try before I have a moment to do this I would love to see your results.

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,595 Neuron
    Options

    @jfyuen
    ,

    In the past I have used the approach of leaving datetime column in the GMT (Z) time zone. I think this would only work for cases where the reg_time comes from a single time zone. If multiple time zones were involved this would likely not work out. In addition, for myself or those who know how to read the time zone this can be a bit confusing as well.

  • leeIdoine
    leeIdoine Registered Posts: 8
    Options

    Hi,

    I was wondering if you had any insight into this. I have data that is coming in as UTC, and I am trying to convert it to EST. However, Whenever I set Locale to en_US, and set the timezone to America/New York, The data is returning as such.

    A UTC time stamp of 8AM UTC is being converted to 12PM EST. However, East coast time is 4 hours behind UTC, not 4 Hours ahead of UTC. So a conversion of 8AM UTC should be 4AM EST.

    Can you think of any work arounds for this issue as it seems that DSS is not capable of accurately changing the time zones?

    Thanks,

Setup Info
    Tags
      Help me…