Store datetime value as "local" time

When reading data sources, it is possible to check a box that tells Dataiku to understand the date as "local" time.  "Local" being local to the server time zone running the job.  But if I'm going to use these dates to make predictions (in my case, I predict a time delta, then add that delta to the original timestamp, which provides me with the predicted date and time that an event will occur on) then ultimately I need the prediction to be localized in the same manor the original date was.  Presently, I have to manually increment that predicted date by the time zone offset, and because this is occurring in a time zone that shifts for daylight savings, the offset value changes periodically.  It would be a really nice feature to simply be able to designate that the output time is timezoneless (and in doing so, is automatically shifted back from Zulu time to the server's local time zone).

I know I can handle all of this with some prepare recipes and scenario variables, but it would be a lot smoother (and more easily understood by someone who may need to update the project later) to have this handled natively.

4 Comments

I would love to have this option as well. Thanks for suggesting it @Jason.

We are migrating our data storage to Snowflake and find that the Snowflake timestamp without time zone data type is the best option all around for our purposes - except that DSS treats it as a string rather than a date for managed datasets.

It'd be great to have the option to tell DSS to treat TIMESTAMPNTZ types in managed datasets as date column just like one can when defining an external dataset on a Snowflake table. Ideally this could be done at the instance level as we'd just turn it on for our use company wide. 

Note that there is a workaround which is to use the dataset overrides functionality. One can specify params.readColsWithUnknownTzAsDates in the path box and true in the value box to have DSS treat the column as a date.  

Thanks,

Marlan

I would love to have this option as well. Thanks for suggesting it @Jason.

We are migrating our data storage to Snowflake and find that the Snowflake timestamp without time zone data type is the best option all around for our purposes - except that DSS treats it as a string rather than a date for managed datasets.

It'd be great to have the option to tell DSS to treat TIMESTAMPNTZ types in managed datasets as date column just like one can when defining an external dataset on a Snowflake table. Ideally this could be done at the instance level as we'd just turn it on for our use company wide. 

Note that there is a workaround which is to use the dataset overrides functionality. One can specify params.readColsWithUnknownTzAsDates in the path box and true in the value box to have DSS treat the column as a date.  

Thanks,

Marlan

ktgross15
Dataiker

Hi @Jason ,

Thanks for your feedback, our dev team is investigating the best resolution here and we will keep you updated on this topic.

Best,

Katie

Status changed to: In the Backlog

Hi @Jason ,

Thanks for your feedback, our dev team is investigating the best resolution here and we will keep you updated on this topic.

Best,

Katie

vcapodanno
Level 2

We recently wrote a custom app that has similar functionality. We stores as GMT then have a drop down to select the local time zone for users. This allows someone in any time zone, for example California (PST), to review data that was generated in Singapore (SGT) against local repositories/records and see it as it appeared in the original time zone.  

We recently wrote a custom app that has similar functionality. We stores as GMT then have a drop down to select the local time zone for users. This allows someone in any time zone, for example California (PST), to review data that was generated in Singapore (SGT) against local repositories/records and see it as it appeared in the original time zone.  

Iโ€™ve also found the strict adherence to UTC to be challenging. Particularly around the understanding of non-experts.  Here is posts that show up in the community on this topic.

https://community.dataiku.com/t5/Using-Dataiku/Resolving-confusions-about-using-Dates-and-Times-for/...

There are others.  

--Tom

Iโ€™ve also found the strict adherence to UTC to be challenging. Particularly around the understanding of non-experts.  Here is posts that show up in the community on this topic.

https://community.dataiku.com/t5/Using-Dataiku/Resolving-confusions-about-using-Dates-and-Times-for/...

There are others.