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

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.

Find more posts tagged with

Comments

Chronos

Is anyone able to help out with this request?

D-Rab

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

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

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.

User2021

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