Has anyone figured out how to convert date/time fi...
# generalm
michoel
09/16/2020, 11:12 PMHas anyone figured out how to convert date/time fields to local timezone in SuiteAnalytic Workbooks? I've found a
SESSIONTIMEZONEb
battk
09/17/2020, 3:14 AMwhat are you doing to get a datetime (or is it a timestamp variant) in server time?
m
michoel
09/17/2020, 3:17 AMIt's a Server Script Log Dataset. When I add the Date column directly I just get date without time. If I create a formula field with
{date}michoel
09/17/2020, 3:17 AMmessage has been deleted
michoel
09/17/2020, 3:18 AMmessage has been deleted
b
battk
09/17/2020, 3:32 AMlooks like a Date, and it looks like formulas in workbooks dont let you use cast
m
michoel
09/17/2020, 5:42 AMthanks
b
battk
09/17/2020, 12:01 PMclosest i got while looking minimally dumb is
NEW_TIME({date}, CASE TZ_OFFSET('AMERICA/LOS_ANGELES') WHEN '-07:00' THEN 'PDT' ELSE 'PST' END, TO_CHAR(CURRENT_TIMESTAMP, 'TZD'))battk
09/17/2020, 12:01 PMI couldnt find a pretty way to get PST vs PDT, so there is an ugly case statement in the middle
battk
09/17/2020, 12:02 PMits unlikely you want this since NEW_TIME has limited timezone support
battk
09/17/2020, 12:02 PMthe more universal ugly solution is
{date} - TO_NUMBER(SUBSTR(TZ_OFFSET('AMERICA/LOS_ANGELES'), 1, 3)) / 24 + TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3)) / 24 + TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 5, 2)) / (24 * 60)battk
09/17/2020, 12:03 PMwhich essentially subtracts and adds timezone offsets
battk
09/17/2020, 12:04 PMboth solution look dumb when compared to using Datetime Expressions, which evidently the formulas in workbooks dont support
m
michoel
09/21/2020, 11:30 PMthanks @battk, works perfectly for what I need
b
battk
09/21/2020, 11:36 PMi didnt take into account the sign of the offset for the minutes in the formula that adds offsets
m
michoel
09/21/2020, 11:38 PMthanks, i'm happy to harcode this one to my timezone so first formula works well enough for me
29 Views