Converting Current_Date into EST from UTC without using Company Time Zone Settings?

Options
D-Rab
D-Rab Member
in Archive

Trying to see if this is even possible? At 4 pm all of my formulas break that use 'Current_Date' formula. I cannot utilize the Company Time Zone Settings since not all data sources we use are in UTC time already. Considering this limitation is there a way to accomplish this? 

 

Here is an example beastmode that I'm using that breaks:

 

(CASE
when `date` = CURRENT_DATE() -1 then (CASE
when `Rate Type ID` = '5' then `Today's Video Billable Views`
when `Rate Type ID` = '6' then `Today's Video Billable Views`
when `Rate Type ID` = '4' then `Today's Clicks`
else `Today's Impressions`
end) else 0
end)

 

Once the current date becomes 1 greater than the actual date the rest of the formula breaks since the most recent date in the data set is from yesterday. Hence 2 days ahead minus 1 is not equal to any 'dates' in the data set and thus a 0 is returned.

Comments

  • Chronos
    Chronos Member

    Is anyone able to help out with this request?

  • D-Rab
    D-Rab Member

    Actually I was able to use a when statement with hour(now()) < 9 since we are pacific time. There doesn't seem to be any other way to do this beyond this work around. 

  • DamienSmith
    DamienSmith Member

    I had exactly the same problem except mine broke at 11am as I'm UTC +11. When you use now() it returns the time in UTC but the card is in the timezone set in Admin. I used addtime to bring the Beast Mode into my timezone by adding 11 hours.

     

    So instead of using now() I use this: ADDTIME(NOW(),39600))

     

    However, it doesn't take daylight savings into account so not ideal.

  • cwolman
    cwolman Contributor

    You can use the following:

    convert_tz(Now(), 'utc', 'us/pacific')

    Using the full time zone name with convert_tz will adjust for daylight savings time.


    -----------------
    Chris
  • User2021
    User2021 Member

    @GrantSmith Here it is same statement and mentioned that daylight saving will be adjusted.

Categories