Need to determine TZ offset based on sys_user's cmn_location

[Ahmed10]

As part of an employee departure (i.e. leaving the company) business process that will be triggered from a Scripted API, I'm trying to determine a user's local 5:00 PM based on their location's time zone.

• I get a payload with a user's employee ID and an effective departure date, and I must use this information to create and populate a "Employee Departure" record (custom table).
• One of the fields is the Effective Date of the departure (this will eventually trigger a flow at the specified time)
• The company's employees can set their own time zones on the sys_user table, so for security reasons I must use the time zone set in the user's office location (sys_user.location references cmn_location table that has a time_zone field).
• The cmn_location.time_zone choice list specification (nav_to.do?uri=sys_dictionary.do?sys_id=d6b4a53bdbb21300b23e36723996196d) points to the sys_user.time_zone choices, so they are exactly the same.
• In a script, retrieving this data returns a string such as "US/Pacific":

  var locationSysId = '5929e80189990d10d4f2c99f8bbb3506';
  var location = new GlideRecord('cmn_location');
  location.get(locationSysId);
  gs.info(location.time_zone)
  
  *** Script: US/Pacific​

How do I take this value and use it to get an offset so I can populate the Effective Date properly?

Thanks!

[Ahmed10]

Hey Ravi,

How would global.rmConvertTimeZone(dateTime, fromTz, toTz) work if I don't want to be agnostic of the fromTz? I don't want to assume that this system will always be a certain time zone by default. The company is expanding globally and it's possible that in the future they change it from EST/EDT to GMT. I'm not really trying to convert time zones.

Thanks,

Ahmed

[Ravi9]

Hi Ahmed

If you have below SI in your instance - it might be helpful

GlideTimeZoneUtil - it has a function adjustTimetoTimeZone that should work for your need , let me know otherwise

[Ahmed10]

Ravi, this doesn't work for my case because GlideTimeZoneUtil is scoped global and is not accessible from all application scopes.

[Hitoshi Ozawa]

Following will return date/time in time zone specified in location.

var locationSysId = '<sys_id of record in cmn_location>';

var location = new GlideRecord('cmn_location');
location.get(locationSysId);

var gdt = new GlideDateTime();
var tz = Packages.java.util.TimeZone.getTimeZone(location.time_zone);
gdt.setTZ(tz);
gdt.setDisplayValue("2022-03-16 17:00:00"); // set date/time based on set time/zone


gs.info(gdt.getValue());  // show date/time in UTC

Execution. Location set to Germany

*** Script: Europe/Berlin
*** Script: 2022-03-16 16:00:00

[Ahmed10]

While this may work in the Global scope, it doesn't work in an application scope. 

Here's what I tried to do instead... I created a TimeZoneUtil Script Include in the Global scope and I made it accessible from All application scopes:

var TimeZoneUtil = Class.create();
TimeZoneUtil.prototype = {
    getGdtWithTZ: function(dateTimeString, tzString) {
		var gdt = new GlideDateTime();
		if (tzString) {
			var tz = Packages.java.util.TimeZone.getTimeZone(tzString);
			gdt.setTZ(tz);
		}
		gdt.setDisplayValue(dateTimeString);
		
		return gdt;
	},
    type: 'TimeZoneUtil'
};

Then to test, I ran this as a Background Script:

var tzUtil = new global.TimeZoneUtil();
var timeString = '2022-03-17 17:00:00';
var timeZone = 'Europe/Kiev';
var gdt = tzUtil.getGdtWithTZ(timeString);
var gdtWithTZ = tzUtil.getGdtWithTZ(timeString, timeZone);


gs.info('System Time Zone (EDT): ' + gdt + ' ' + gdt.getNumericValue() + ' ' + gdt.getTZOffset());
gs.info(timeZone + ': ' + gdtWithTZ + ' ' + gdtWithTZ.getNumericValue() + ' ' + gdtWithTZ.getTZOffset());

The output is different depending on whether I run it in the Global scope or if I run it in an application scope.

Global:

*** Script: System Time Zone (EDT): 2022-03-17 21:00:00 1647550800000 -14400000
*** Script: Europe/Kiev: 2022-03-17 15:00:00 1647529200000 7200000

Application:

x_*****_emp_core: System Time Zone (EDT): 2022-03-17 21:00:00 1647550800000 -14400000
x_*****_emp_core: Europe/Kiev: 2022-03-17 15:00:00 1647529200000 -14400000

The raw values are the same, but the scoped gdtWithTZ loses its time zone. However, when I assign the gdtWithTZ to a field on a form, it is the correct time. This method works (11:00 US EDT is 17:00 Eastern European Standard Time).