# SQL Server store datetime object with time zone

**URL:** <https://forum.inductiveautomation.com/t/sql-server-store-datetime-object-with-time-zone/49319>\
**Category:** Ignition\
**Created:** [July 28, 2021, 6:31pm UTC](https://forum.inductiveautomation.com/t/sql-server-store-datetime-object-with-time-zone/49319 "2021-07-28T18:31:13Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Bronzebeard62](https://avatars.discourse-cdn.com/v4/letter/b/838e76/32.png) [@Bronzebeard62](https://forum.inductiveautomation.com/u/Bronzebeard62)\
**Post date:** [July 28, 2021, 6:31pm UTC](https://forum.inductiveautomation.com/t/sql-server-store-datetime-object-with-time-zone/49319/1 "2021-07-28T18:31:13Z")

</div>

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

```auto
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:

```auto
	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:

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

```

With a print statement:

```auto
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:

```auto
2021-07-28 10:19:26.327000

```

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

```auto
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:

```auto
	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:

```auto
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:

```auto
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,

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [July 28, 2021, 7:25pm UTC](https://forum.inductiveautomation.com/t/sql-server-store-datetime-object-with-time-zone/49319/2 "2021-07-28T19:25:52Z")

</div>

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).