Timezone issue when calculating dates

  • 1
  • Problem
  • Updated 6 months ago
  • Solved
Hello,

We have a UI only fromula field that is attempting to display a date one day prior to a given date:

YEAR({{Week_End__c}} - (1000*24*60*60))+'-'+
IF(LEN(MONTH({{Week_End__c}} - (1000*24*60*60)))=2,MONTH({{Week_End__c}} - (1000*24*60*60)),'0'+MONTH({{Week_End__c}} - (1000*24*60*60)))+'-'+
IF(LEN(DAY({{Week_End__c}} - (1000*24*60*60)))=2,DAY({{Week_End__c}} - (1000*24*60*60)),'0'+DAY({{Week_End__c}} - (1000*24*60*60)))

So when the Week_End__c is 5/11/2018, it should display 5/10/2018. This works as intended when the user's computer is set to the EST. However, for every other US time zone it calculates for 2 previous days, so it's showing as 5/9/2018. We have a field like this for the 6 prior days from Week_End__c, so there is an off by 1 error for all of them. 

Our org default is EST, so I'm assuming the discrepancy there is the problem. I've been playing around with those modifying values but nothing seems to be working that doesn't throw the dates off for the other timezones as well.
Photo of Joe Dobbels

Joe Dobbels

  • 546 Points 500 badge 2x thumb

Posted 6 months ago

  • 1
Photo of Mike Dwyer

Mike Dwyer

  • 3,390 Points 3k badge 2x thumb
I think you might save yourself a lot of grief by adding a formula field to your object:

Day_Before__c as "Week_End__C - 1"
Photo of Luzie Baumgart

Luzie Baumgart, Official Rep

  • 1,310 Points 1k badge 2x thumb
Hi Joe, was Mike's tip helpful for you?
Photo of Joe Dobbels

Joe Dobbels

  • 546 Points 500 badge 2x thumb
It was actually. I just tried it and this way is a lot simpler, thanks.

One of the things I tried in that formula was replacing those YEAR(), MONTH(), and DAY() functions with the YEAR_UTC() functions. But I didn't see one available for DAY(). There is a UTC version for all of the other date functions like that, which I think would bypass the timezone issue I was having. Is there a reason the DAY_UTC() function is missing?
(Edited)
Photo of Luzie Baumgart

Luzie Baumgart, Official Rep

  • 1,310 Points 1k badge 2x thumb
Mike, thank you for sharing your knowledge!
Happy to hear that it was helpful.

Joe, do you know the list of functions in our documentation?
https://docs.skuid.com/latest/en/skuid/models/ui-only-models-fields.html

You will find a function called “DAY_OF_WEEK_UTC”, is that what you are looking for?

One more general question, did you double check the selected timezone in the user settings?
Photo of Joe Dobbels

Joe Dobbels

  • 546 Points 500 badge 2x thumb
That was the document I was working off of. DAY_UTC isn't there. DAY_OF_WEEK is a different function than DAY, so that won't work either.

There is YEAR_UTC, MONTH_UTC, HOUR_UTC, MINUTE_UTC, and SECOND_UTC. It's weird that DAY_UTC is missing.

And I could just set my timezone to EST. But If I am physically in MST I don't want to have to change my timezone just so my skuid pages will work.