Resolving confusions about using Dates and Times for Visualization

[tgb417]

In this thread:

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

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.

 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)

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.

@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.

[tgb417]

Here is a post in the knowledgebase that seems to suggest the same approach.

https://community.dataiku.com/t5/Knowledge-Base/How-Dataiku-DSS-Handles-and-Displays-Date-amp-Time/t...

[jfyuen]

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]

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]

@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.