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.
Comments
-
Is anyone able to help out with this request?
0 -
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.
0 -
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.
0 -
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.
0 -
@GrantSmith Here it is same statement and mentioned that daylight saving will be adjusted.
0
