Converting UTC to EST

leeIdoine

Wondering if anyone has found a fix to this issue. I am trying to convert a string time stamp from UTC to EST using the parse date recipe. However, The time stamp is being converted incorrectly. It is converting a UTC time stamp of 8AM to 12PM EST. This is the opposite of what it should be doing. I have the locale set to en_US and the timezone set to America/New York. Is there a way to convert a string UTC time stamp to a parsed Date while also converting it to east coast time?

Thanks,

Alexandru

Hi @leeIdoine
,

Parse date processor if you set EST, it is expected that the source timestamp is already EST and DSS converts to UTC, so what you see is expected behavior
https://doc.dataiku.com/dss/latest/preparation/dates.html#meanings-and-types

If you want to convert to UTC to EST, you can use UTC offset. You can use the format date to custom format processor and select EST time zone, this will calculate 4/5h offset based on DST

https://doc.dataiku.com/dss/latest/preparation/processors/date-formatter.html

yyyy-MM-dd'T'HH:mm:ss.SSSZ



Now if you parse that back to standard format :



Hope that helps!

leeIdoine

Great. Looks like this works like a charm. Appreciate the solution.

Brenda

Thanks a lots! it's a great help for me

leeIdoine

I have an additional question about this, @AlexT

So I am now trying to filter on my EST column. I am trying to filter data from 8AM-9PM. However, when I do this, its returning instances that have time stamps that are before 8AM and will only return values up to 4:59PM. However, in the analyze tool, it says the max time stamp in EST is 20:59.50. Which seems like it would be correct because the cut off should be at 9PM. But I can not see the data via export, nor in the table. I am concerned because I am getting different answers when I filter on the EST column I created, and when I do the same filter but use the UTC parsed time. These should be the same, but they are returning different numbers. And I believe its because the EST column is not capturing all the data for some reason.

tgb417

@leeIdoine
,

I do not have a specific answer for you. I do have a possible clue that may help you work out what is going on. Dataiku tends to handle dates and times with a bias to UTC. UTC does not do any crazy daylight saving stuff. UTC is also 4 or 5 hours ahead of us in EST time zone depending on the time of year.

One of the things I would check when dealing with Dataiku and regional time zones is do Daylight saving shifts or offsets from UTC have anything to do with the challenge I’m dealing with. Given that what you are trying to do is off by 4 hours and we are currently UTC -4 here on the east coast. Something may be going on there.

Just a thought hope it might help you a bit.

leeIdoine

Hey @tgb417
,

Yeah even though I am filtering on my EST column from 8AM until 9PM. Its returning values from 4AM to 5PM. Which to me seems like its still looking at the column in UTC in the background. I am able to extract date components from the EST column, make those values an integer, and then filter using those. (IE where year=2023, month=5, day=25 Hour >=8 and hour <21. But to me it seems like this should be an unnecessary work around.

tgb417

@leeIdoine
,

Agreed regarding unnecessary work around. It feel like this is a potential defect. I’d encourage you to submit a support ticket on this item. See what the support team can do to help out. I’d also suggest that you add a product idea here in the community. I also would like to see better more complete support of world wide time zones.

leeIdoine

@tgb417
@AlexT

I just realized that formatting the date to a particular time zone outside of UTC is not accurate either. Right now, UTC is 5 hours ahead of EST. However, when formatting a UTC time stamp to EST, it is only subtracting 4 hours. it seems that the best way to do this for me right now is using an inc formula, and subtracting 5 hours to create the create EST conversion time stamp column. However, I do not want to have to go in and update this formula every daylight savings time, and then when time converts back to standard time. Seems to me that this is a defect as @tgb417
implied. I will update the thread after I discuss with dataiku support.