Force a timezone with DATETIME_PARSE? How about NOW()?


#1

I’m working on a ‘currently open?’ routine in support of a user question. My formula takes today’s datestamp in the format 'YYYY-MM-DD'; appends ' ' plus an opening or closing time ('HHmm') based on day-of-the-week, taken from a table; and wraps the whole thing in DATETIME_PARSE().

My problem comes when I attempt a comparison with NOW(). As best I can tell, DATETIME_PARSE() always returns UTC time, while NOW() always returns browser time. I haven’t been able to find a way to force either to use the other’s timezone. (I can work around the issue by using DATEADD(NOW(),-7,'hours'), as I am 7 hours behind UTC — but I can’t figure out how to get the offset automatically. (Oh, wait:

VALUE(DATETIME_FORMAT(SET_TIMEZONE(NOW(),'America/Los_Angeles'),'ZZ'))/100

should probably do it.)

At the moment, is this workaround my best option?


#2

I think so…


#3

Yeah. The only function that seems to use SET_TIMEZONE() is DATETIME_FORMAT() — but that doesn’t do any good for getting two date values in the same timezone because the output of DATETIME_FORMAT() is a string; if you convert it back to a date using DATETIME_PARSE(), it once again defaults to UTC.

Actually, using that DATETIME_FORMAT() 'ZZ' qualifier formula I listed above does make things easier; you still have to provide the proper timezone, though.