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

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/ta-p/4516

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.

leeIdoine

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,