"Leave my timestamps alone!" option

We collect records from our restaurants across the USA and they all come to us in their local time with no marker for timezone / dst adjustments. If a sale happened at 11am, it goes in as 11/19/2015 11:00:00. Simple as that, and that is how we want it. When I do time analysis I just want to know sales based off that local time.

 

Now, I completely appreciate the current settings in Workbench / Domo. I could see how that would be very useful to people and I think it is a solid feature.

 

But for some of us we need a "don't do anything to my timestamp" option. I changed my timezone settings in the Domo admin because it was getting annoying to do the UTC conversion when I was trying to figure out when a dataset was scheduled. I didn't expect all of my timestamps to be adjusted. I've since converted all of my Workbench jobs to our local timezone and that matches the Domo settings but it would be nice to not have to go through all of that each time.

 

Thank you!

17
17 votes

· Last Updated

Comments

  • @alexpeay is the product manager for workbench.  He will take a look at this suggestion.  Thank you for submitting the idea.

  • Sean,

     

    Thanks for the comment. I understand the frustration that you are encountering. The issue at hand if there is not a common time zone for your data then it becomes impossible for us to provide a represenetation of that data to you. With Domo all times are assumed to be in UTC then when you make a request to visualize the data that time is transformed to the local time set by the user. This makes it so that you can compare (in this case) the transactions in context of the time. 

     

    If the time zone is of no concequence for you then you can cast the column in question as a string and the time zone shift will not happen. Alternatively, if time zone is not important you can set the Domo instance to have a time zone of UTC then when the data is visualized there will be no transformation since the data is already in the assuned timezone.

     

    Regards,

    Alex

    Alex Peay
    Product Manager
    Domo
  • The problem of casting the datetime as a string is you lose the ability to do things like graph the check times over the course of a day to use that field as the date control.

    The last part "Alternatively, if time zone is not important you can set the Domo instance to have a time zone of UTC then when the data is visualized there will be no transformation since the data is already in the assuned timezone" leaves out a very important consideration. The time zone might not be important in the data BUT it could be very important on things like looking at times when things were run. The reason I changed my time zone in my Domo preferences because I I hated converting the UTC times of when a dataflow kicked off back Central time.

     

    But since my data was uploaded as UTC, those changes reflected back in the data. I didn't realize my data uploaded as UTC because there really is no time zone data. I have sales data that was captured at 10am eastern and 10am central in the same set.

     

    And again, I totally get why a lot of people want this time zone adjustments you are doing. For those purposes, it is brilliant. If I had a 24/7 world wide KBIs I absolutely would want to convert that sales in Dubai to central time so I can see how we have done in the last 24 hours.

    I guess what I and others are asking for is a true date, datetime, and timestamp data types. (Coming from the MySQL paradigm). In MySQL datetime doesn't have timezone data. timestamp does. https://dev.mysql.com/doc/refman/5.5/en/datetime.html

     

    And I should say that now that I know to put the dataflow in central time and set my timezone to central time, we are basically fine -- for now. However if we had people in another time zone, this wouldn't work. If you looked at a card that showed volume of checks through a day it would show an 11am system wide open time instead of the 10am system wide open time.

  • Hi,

     

    We had the same frustrations here about three weeks ago. The problem is by the time we realize that all our timestamps were incorrect, it was too late because we were running this way since the beginning (6 months). Some financial KPI's were simply wrong puting big numbers a day before, which was in fact the month before because the timestamp was the first day of the month. The UTC conversion make them 4 hours back which resulted into changing the day of the timestamp.

     

    I opened a case to DomoSupport and it took almost two weeks to get a reply with the workaround of the Workbench Transform setting (which is really not intuitive by the way)... This workaround is nice, but to be honest, this is not the way it should work in my mind.

     

    My suggestion would be to keep the timestamps as they are stored, by default. And then, if we really need to convert everything to UTC or any other timezone, we could use the transform feature as it is right now, but please, by default, don't touch the timestamp!!

     

    Thanks!!

  • The amount of confusion/frustration this causes is insane. Even the work around doesn't really work because then you are limited when using functions in the beast mode calculations. Say my workbench upload is set to est and my company settings are in est. It will give me the result I am looking for (only for est data) however if I used any built in functions in beast mode like current time, it is then returned in UTC. The "leave my time zone alone" button would be great but there should also be built in functionally that the admin setting time zone is reflective of everything in domo and not just the data sets.

  • Great suggestions here. Better timezone support is definitely something we're looking into. 

  • I regular receive pings on this and honestly I've learned over the years to just deal with it. Today it bit me again.


    I've been adding the transform on all my workbench jobs that converts all of my timestamps to UTC. This means a time stamp from 11/4 at 10:01 I declare as having happened in Central Daylight Time (CDT).  I want it converted to UTC so it adds 5 hours and records it as 11/4 at 15:01.

     

    This also means that a time stamp from 11/5 at 10:01 I declare, via the transform, as having happened in Central Standard Time (CST). I want it converted to UTC so it adds 6 hours and records it as 11/5 at 16:01.

     

    When I look at those rows inside of the Redshift Data flow editor and I preview the output I see all the times as UTC. Because my settings on my instance have us all in Central Time, my cards will convert to Central Time.

     

    Thus when I build a card and look at that 11/5 16:01 UTC data, it shows that it happened at 11/5 10:01. This is fine, right? Well, what happens when I look at the 11/4 data, the one from Daylight savings time TODAY on 11/15 when I am back in standard time? It shows it happened at 11/4 at 9:01. Why? Because I am seeing all times at Central STANDARD times. That DID happen at 11/4 at 9:01 Central Standard Time. But on 11/4 we weren't in Central Standard time - we were in Central Daylight Time which is an hour ahead. In other words 9:01 CST = 10:01 CDT.

     

    So the data is right. The problems are the lack of labels AND that we humans don't look at things like that.

     

    If I want to look at all sales from the 10am hour, my data comes into DOMO ready to go. But by converting it to a time zone that doesn't have Daylight Savings Time attached, I then have to convert that data back out of daylight savings time with gnarly lookup tables OR I just send along another column that shows the sales hour and another column with the sales minute. Yuck. But even if you just look at time time, your brain has to go through an extra step. We aren't open at 9am. Yet here I see a sale at 9am. Why? Oh, because I'm in standard time and that happened in daylight time so I have to mentally shift forward an hour.

     

    Now, let's say I leave off the transform. I write the data as 10:01 and DOMO says "Oh, 10:01 UTC, sure." Now I can do transforms on it and get that 10am sales hour. But when we look at the card it says that happened at 4am. Come spring, it would say that happened at 5am.


    That's bad.

     

    If someone can help me understand what the fix would be, I'm listening. I still feel like there needs to be a setting on a job to just say "Look, this happened at 10:01 and this happened at 19:01. Forget about WHERE in the world it happened. All times local!"

     

    PS - Remember what they taught us in Austin Powers about how if you think too much about time you go cross eyed? This has happened to me.

     

     

  • I agree Sean...  We have about a 3 TB system up in DOMO and i am just realizing all our timestamps are jacked.  We are pulling from multiple internal sources and they are fine (because they dont care,,, it's a database) so when i started comparing "why cant i get the same results" i ran into your DOJO topic.  We have timestamps from all over the US in our tables, SOME are preset to local time, and some are preset to UTC, it is denoted to us (the user) in the column header (start_time_LDT) vs (start_time_UTC).  DOMO just jacks it all up and it is very frustrating. 

  • @JSharp

     

    Please have a look at this and provide an update.

    Thanks!
    Dani

    Dani aka "Mr.Dojo"

    Dojo Admin
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
    **You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
  • Thank you @DaniBoy - Just last month we had another "KHAAAAAN!" moment when it came to UTC date time manipulation.

  • @SeanPT  I use a timezone reference table with dates that reference the timestamp date within each row to counter any DST happenings. Also my DOMO server is set to UTC timezone so that I have full control of MY data. 

     

    Example:  Row1 is defined by an ID that is known to be in central time zone and participates in DST.  My reference table has the time zone and the from-to date listed.  So in this example, i have two rows of central and i join to it on 1) timezone and 2)date stamp of row column. This allows the time shift to ALWAYS be -5 because the datestamp from the row won't change.

     

    This table goes forward about 5 years currently so i will have to keep it updated (job security?) for it to continue to work, but so far i havent had any issues. 

  •  @Greg_B,

     

    That is a great suggestion! @JSharp is this a viable workaround? If so we should replicate this as an article in addition to a feature request.

    Thanks!

    Dani

    Dani aka "Mr.Dojo"

    Dojo Admin
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
    **You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
  • @Greg_B- I missed your reply all those years ago.
    I think that is a great work around. As we once again are about to get hit by this problem, it was definately a work around we considered. We are treating the hour and minute as seperate values and doing a whole lot of ugly math to get what we need. We are presenting the date/time as a string so we can't use a lot of the nice built in time functions. We probably could build a reference table like you said but we use the presentation layer in DOMO of 'yesterday' (etc) quite a bit and switching from Central Time to UTC would toss that off each evening.

     

    If we were still using timestamps in anything, come Monday things would break once again as DST falls off here in the USA.

     

     

This discussion has been closed.