SQL Server store datetime object with time zone

Ignition
1

Hello, I am trying to store a datetime value in sql server with a time zone. I have a named query as follows:

INSERT INTO {table} (cell, cycle_time, date_time, op, target_time) 
VALUES (:deviceName, :cycle_time, :date_time, :opName, :target_time)

Where date_time is a parameter that is passed in as “DateTime” type.

When passing the params in to the named query, it looks like this:

	currentTime = datetime.now()
	params ={
	'table':'myTable',
	'deviceName': deviceName,
	'opName': opName,
	'cycle_time':cycleTime,
	'date_time':currentTime,
	'target_time':targetCycleTime,
	}

As you can see, I am using datetime.now() to get my current date, time, and timezone, and when I print the result of currentTime I get the following:

Without a print statement:

datetime.datetime(2021, 7, 28, 14, 19, 26, 327000)

With a print statement:

2021-07-28 14:19:26.327000

As you can see, there is no time zone.

When I store that value in SQL server, it ends up as:

2021-07-28 10:19:26.327000

I’ve been somewhat successful at getting a datetime object with a timezone using the following code:

from datetime import tzinfo, timedelta, datetime
import system

timeZone={
'EST':-5,
'CST':-6,
'MST':-7,
'PST':-8,
'AKST':-9,
'HST':-10,
}

class FixedOffset(tzinfo):
    def __init__(self, offset):
        self.__offset = timedelta(hours=offset)
        self.__dst = timedelta(hours=offset-1)
        self.__name = ''

    def utcoffset(self, dt):
        return self.__offset

    def tzname(self, dt):
        return self.__name

    def dst(self, dt):
        return self.__dst

def convertTimeString(timeString, targetTz):
	if targetTz in timeZone:
		return timeString.now(FixedOffset(timeZone[targetTz]))
	else:
		print 'Invalid Time Zone %s' % str(targetTz)
		
def checkDST():
	currentTime = datetime.now()
	date = system.date.getDate(currentTime.year, currentTime.month, currentTime.day)
	return system.date.isDaylightTime(date)

And then getting the datetime object via:

	targetTz='CST'
	dst = tz.checkDST()
	time = tz.convertTimeString(datetime.now(),targetTz)
	try:
		if dst:
			time = time + timedelta(hours=1)
	except:
		time = datetime.now()

Which returns something like this:

2021-07-28 13:28:55.577000-06:00

Which is the correct time and time zone that I selected, but when I store that in sql server it ends up as:

2021-07-28 22:28:55.577

Everything I look up for using timezones in jython/python2.7 uses the external library pytz but seeing as Ignition doesn’t come with that library, I was trying to figure out another method. Does anyone have any suggestions?

Thanks,

2

Two suggestions:

  1. Don’t use jython’s datetime. Use java.util.Date and related types–that’s what Ignition passes around.

  2. Set MS SQL Server to use UTC and the server it runs on to UTC. That is the only way to have flawless date/time/timezone support in Ignition.

Java’s datetime type is fundamentally built in milliseconds UTC (including serialization when passed over a network) with conversions to and from local time zones as needed. This allows clients and gateways to work with the same object in different time zones and get the right answers.

Microsoft SQL has a DATETIME column type that does not store time zone information. It also has a DATETIME2 column type that does store the origin timezone. Note that storing localtime+origin timezone means any instant in time is overspecified–there are multiple storable values that correspond to any given instant in time.

Java’s JDBC layer supports time zones and additional precision with java.sql.Timestamp, a subclass of java.util.Date. It is fundamentally UTC nanoseconds. Note that there is no reversible transform from DATETIME2, and DATETIME2 is therefore unsupported in JDBC. Hence my recommendation if you are stuck with SQL Server to use the DATETIME column type (which Ignition does) in a pure UTC environment (for the SQL Server–Ignition can be any localtime).

1 Like