[IGN-6614]What is the best way to format a date (milliseconds) for a specified timezone with Ignition's python scripting

What is the best/simplest way to format a date (milliseconds) to a string equivalent conforming to a specified timezone using python scripting?

The system.date.format() function does not appear to provide capability to specify the timezone you want the output to conform to.

I’m expecting that I am overlooking some simple build-in function.


I’m looking for a general answer but my specific use case is that I’m attempting to return the results of a system.tag.queryTagHistory request with added columns so I can return the block start/end dates formatted as either UTC time or a specified timezone (e.g. ‘US.Central’) or both. I can use Custom Tag History Aggregate functions to retrieve the block start/end dates (timestamps) as millisecond values.

You can construct a SimpleDateFormat object with your desired Locale, which will then use the default timezone from that locale for formatting. Locales can be obtained in a bunch of different ways, but the easiest might be LocaleUtils.parseLocale().

I use the below function to pass it the long value of the t_stamp column returned and the string TZ reference…

gmt = 'America/New_York'
def	dtToLocal(dt):
	conv = LocalDateTime.ofInstant(java.time.Instant.ofEpochMilli(system.date.toMillis(dt)), ZoneId.of(gmt))
	try:
		conv = system.date.format(system.date.parse(conv,"yyyy-MM-dd'T'HH:mm:ss"),"MM/dd/yyyy HH:mm:ss")
	except:
		conv = system.date.format(system.date.parse(conv,"yyyy-MM-dd'T'HH:mm"),"MM/dd/yyyy HH:mm:ss")
	return conv

After calling system.tagQueryTagHistory, I convert the t_stamp column to a list.
Then I use map calling that function to convert them to the desired timezone.
Then I remove the t_stamp column from the original dataset, and merge in the updated column to the final dataset for use.

I have to do this since we do history queries using WEBDEV and people from all different timezones use it and it gathers data from locations all with different timezones as well.

I had also put in a suggestion for an additional parameter for queryTagHistory to be able to specify TZ for the returned dataset as well.
With the use of WEBDEV so prevalent this would be extremely helpful for companies using the gateway network with historian splitters and remote historians in different timezones.

With WebDev the TZ of the gateway it is running on is what is returned.

Can’t do that. Datasets carry java.util.Date values or the subclass java.sql.Timestamp, neither of which carry a timezone. TZ has to be applied at the point where you convert to string.

2 Likes

@PGriffith thanks for your response. Forgive my potential ignorance here but as far as I know (and was able confirm with brief investigation) a locale is not directly relatable to a time zone, it just helps determine how string representations of time strings are to be interpreted/presented based on language standards. For example the java supported locales for all of the United stated is en-US and es-US (English or Spanish).
However, I am happy to be corrected if you could provide a code example of outputting a date (milliseconds) to a string conforming to the ‘US/Central’ time-zone.
Thanks.

Ah, I was overthinking things.

SimpleDateFormat extends DateFormat. DateFormat defines setTimeZone().

TimeZone is an instance of java.util.TimeZone, and easiest obtained from an ID.

from java.util import TimeZone
from java.text import SimpleDateFormat

#for tz in TimeZone.getAvailableIDs():
#	print tz

now = system.date.now()

eastern = TimeZone.getTimeZone("America/New_York")
pacific = TimeZone.getTimeZone("America/Los_Angeles")

sdf = SimpleDateFormat("yyyy-MM-dd HH:mm:ss ZZZ")
print sdf.getTimeZone().getID()
print sdf.format(now)

sdf.setTimeZone(eastern)
print sdf.getTimeZone().getID()
print sdf.format(now)

sdf.setTimeZone(pacific)
print sdf.getTimeZone().getID()
print sdf.format(now)

Here in California, that gets me:

>>> 
America/Los_Angeles
2022-09-14 11:43:14 -0700
America/New_York
2022-09-14 14:43:14 -0400
America/Los_Angeles
2022-09-14 11:43:14 -0700 
1 Like

@PGriffith, thanks that is exactly what I needed.
For practice/familiarization I converted it into two functions, one compact-ish, one with lots of debug info:

from java.util import TimeZone         # https://docs.oracle.com/javase/8/docs/api/java/util/TimeZone.html
from java.text import SimpleDateFormat # https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html

def date2String(paramDate, paramTimeZoneID=None, paramPattern=None):

	# if the 'date' parameter is not the expected type ('java.util.date') then assume it is a date expressed as milliseconds and convert it
	myDate = paramDate if str(type(paramDate)) == "<type 'java.util.Date'>" else system.date.fromMillis(paramDate)

	# If the 'Pattern' parameter was provided then create a SimpleDateFormat object using that pattern, if not assume a detailed pattern
	myPattern = paramPattern if (paramPattern is not None) else 'yyyy-MM-dd HH:mm:ss.SSSZZZ zzzz'

	# Create a SimpleDateFormat object using the provided pattern
	mySdf = SimpleDateFormat(myPattern)
	
	# if the 'TimeZoneID' parameter was provided then adjust the SimpleDateFormat object's timezone, if not provided then leave at system default
	myTimeZone = TimeZone.getTimeZone(paramTimeZoneID) if (paramTimeZoneID is not None) else mySdf.getTimeZone()

	mySdf.setTimeZone(myTimeZone)
	
	return mySdf.format(myDate)
  
  
def date2String_debug(paramDate, paramTimeZoneID=None, paramPattern=None, paramDebug=True):

	if paramDebug:
		sdf = SimpleDateFormat()	
	
		print '-- System Defaults ---'
		print 'TimeZone ID: {} {}'.format(sdf.getTimeZone().getID(), type(sdf.getTimeZone().getID()))
		print 'TimeZone Object: {} {}'.format(sdf.getTimeZone(), type(sdf.getTimeZone()))	
		print 'Pattern: {} {}'.format(sdf.toPattern(), type(sdf.toPattern()))
		print 'Localized Pattern: {} {}'.format(sdf.toLocalizedPattern(), type(sdf.toLocalizedPattern()))
		print 'Date String: {} {}'.format(sdf.format(dt), type(sdf.format(dt))) 	

		print '\n-- Function Parameters (Raw) --'
		print 'Date: {} {}'.format(paramDate, type(paramDate))
		print 'TimeZone ID: {} {}'.format(paramTimeZoneID, type(paramTimeZoneID))
		print 'Pattern: {} {}'.format(paramPattern, type(paramPattern))
		
		sdf = None

	#================================================================================
	# Evaluate and apply function parameters
	#================================================================================
			
	# if the 'date' parameter is not the expected type ('java.util.date') then assume it is a date expressed as milliseconds and convert it
	if str(type(paramDate)) == "<type 'java.util.Date'>":
		myDate = paramDate
	else:
		myDate = system.date.fromMillis(paramDate)
		
	# If the 'Pattern' parameter was provided then create a SimpleDateFormat object using that pattern, if not assume a detailed pattern
	if (paramPattern is not None):
		myPattern = paramPattern
	else:
		myPattern = 'yyyy-MM-dd HH:mm:ss.SSSZZZ zzzz'
	
	# Create a SimpleDateFormat object using the provided pattern
	mySdf = SimpleDateFormat(myPattern)
	
	# if the 'TimeZoneID' parameter was provided then adjust the SimpleDateFormat object's timezone, if not provided then leave at system default
	if (paramTimeZoneID is not None):
		myTimeZone = TimeZone.getTimeZone(paramTimeZoneID)
	else:
		myTimeZone = mySdf.getTimeZone()	

	mySdf.setTimeZone(myTimeZone)

	#================================================================================
  	# Create Result (Return Value)
  	#================================================================================  
	myResult = mySdf.format(myDate)

	if paramDebug:
		print '-- Function Parameters (Corrected) --'
		print 'Date: {} {}'.format(myDate, type(myDate))
		print 'TimeZone ID: {} {}'.format(myTimeZone.getID(), type(myTimeZone.getID()))
		print 'Pattern: {} {}'.format(myPattern, type(myPattern))
			
		print '-- Results ---'
		print 'TimeZone ID: {} {}'.format(mySdf.getTimeZone().getID(), type(mySdf.getTimeZone().getID()))
		print 'TimeZone Object: {} {}'.format(mySdf.getTimeZone(), type(mySdf.getTimeZone()))	
		print 'Pattern: {} {}'.format(mySdf.toPattern(), type(mySdf.toPattern()))
		print 'Localized Pattern: {} {}'.format(mySdf.toLocalizedPattern(), type(mySdf.toLocalizedPattern()))
		print 'Date String: {} {}'.format(mySdf.format(myDate), type(mySdf.format(myDate))) 

	return myResult

Example Usage:

now = system.date.now()
print 'A: {}'.format(date2String(paramDate=now))
print 'B: {}'.format(date2String(paramDate=now, paramTimeZoneID='US/Central'))
print 'C: {}'.format(date2String(paramDate=now, paramTimeZoneID='Canada/Mountain', paramPattern='EEEE LLLL dd y G K:m:s.S a zzzz'))

Example Results:

>>> 
A: 2022-09-15 04:34:00.895+0000 Coordinated Universal Time
B: 2022-09-14 23:34:00.895-0500 Central Daylight Time
C: Wednesday September 14 2022 AD 10:34:0.895 PM Mountain Daylight Time

@PGriffith just a thought… any chance we could get the development team to extend system.date and add a setTimeZone() function…or change system.date.format and system.dataset.formatDates to allow specifying a timezone as a parameter?

5 Likes

Yeah, I can definitely get behind that.

Probably worth making the expression functions do this too.

4 Likes

are you trying to make my job easier or something?! :slight_smile: