# Daylight Savings Time

**URL:** <https://forum.inductiveautomation.com/t/daylight-savings-time/612>\
**Category:** Archived Topics\
**Created:** [March 19, 2008, 7:19pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612 "2008-03-19T19:19:03Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [March 19, 2008, 7:19pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/1 "2008-03-19T19:19:03Z")

</div>

Do you guys have some recommendations on how to properly handle Daylight Savings time?

How is the 1 hour “overlap” data in the fall handled by FactorySQL and FactoryPMI?

I believe the t\_stamp value is obtained from the DB host and is not stored in Zulu/Greenwich Mean Time. Is this correct?

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [March 19, 2008, 10:56pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/2 "2008-03-19T22:56:47Z")

</div>

FactorySQL’s internal times are all UTC, so there should be little consequence to its internal operations… however, yes, all of its queries that involve times use the database’s CURRENT\_TIMESTAMP (or similar) field. So, if that’s affected by DST, then there maybe be some data oddities/‘repeat’ timestamps.

One thing to keep in mind is that FSQL tables are required to have a unique index column. In normally creates these itself to be an auto-incrementing value. Therefore, even if your timestamps go back in time an hour and you get “repeat” dates, sorting the data by the index will give you a real, time-ordered view of the events.

By the way, if you’d prefer that the queries use a different timestamp function provided by the database, you can specify it in the db translator file. These files are under the ‘database’ folder of the install directory.

Hope that answers your question,

---

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [March 20, 2008, 1:40pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/3 "2008-03-20T13:40:30Z")

</div>

I’m in a bit of a pickle. I’m hoping you guys can give me some good suggestions on how to handle the situation.

The workstations I’m working with don’t have Internet access. Thus the machine OSs haven’t been updated with the latest patches and don’t recognize the new, early DST shift. (We’re planning to update the OSs during the next shutdown, which is next week.)

As a sort of temporary work-around, I’ve unchecked the “Adjust for Daylight Savings Time” check box in the time zone configuration dialog and set the time to the local DST time on all the machines, including the database server.

Data shown in an Easy Chart is shown as I would expect, using the DB server’s local time. However, the current time arrow on the Easy Chart time-line is an hour ahead. Additionally, the current time I show on a titlebar with the expression dateFormat(now(1000), ‘HH:mm:ss’) is an hour ahead.

I’m suspecting that the Java VM is compensating for DST regardless of the Windows OS time zone configuration.

Any suggestions on how to get all time displays to be consistent?

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [March 20, 2008, 3:48pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/4 "2008-03-20T15:48:22Z")

</div>

Yeah, the VM does have its own timezone information. One thing to understand, is that the time and timezone is consistent between windows and Java, its just how it is converted to human-readable format that changes.

What I mean, is, Windows says the timezone is PST. Java then knows that the timezone is PST. The problem is that Java and Windows both have their own set of rules as to how a GMT time gets converted into local time. Now, you’d expect these rules to match up, but then congress goes and changes them…

What version of the VM are you using? This page [java.sun.com/javase/tzupdater\_README.html](http://java.sun.com/javase/tzupdater_README.html) discusses how the VMs timezone information is updated.

Also, what version of FactoryPMI are you using? There was a feature in 3.1.6 that helps when the client is on a different timezone than the gateway, but it doesn’t sound like thats your issue.

---

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [March 20, 2008, 5:41pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/5 "2008-03-20T17:41:05Z")

</div>

We’ve got Java V1.5.0\_12 (build 1.5.0\_12-b04).

FPMI is V3.0.2 (build 1441)

We’re also planning to update FPMI and FSQL to the latest.

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [March 20, 2008, 5:58pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/6 "2008-03-20T17:58:27Z")

</div>

Yeah, 5\_u12 has the new timezone data in it, so thats where that date is coming from.

---

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [August 24, 2009, 1:59pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/7 "2009-08-24T13:59:12Z")

</div>

I’d like to revisit the topic of Daylight Savings Time, UTC & FSQL.

I’m collecting data that will be used for environmental compliance reporting and I need to make sure that I’m properly accounting for DST.

We’re currently using SQL Server 2005 for the RDBMS which has the GETUTCDATE() function.

If I edit the SQLServer2005.xml file and change CURRENT\_TIMESTAMP to GETUTCDATE(), would the t\_stamp column for standard group tables contain the UTC time?

Should we consider upgrading to SQL Server 2008 which has the SYSDATETIMEOFFSET() function and would it work with the current version of FSQL?

Thanks!

---

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [August 25, 2009, 12:50am UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/8 "2009-08-25T00:50:12Z")

</div>

I’ve been reading up on the subject of DST with regards to MS SQL Server and found a very good blog on the new datetimeoffset data type now available in SQL Server 2008 ([http://blogs.msdn.com/bartd/archive/2009/03/31/the-death-of-datetime.aspx](http://blogs.msdn.com/bartd/archive/2009/03/31/the-death-of-datetime.aspx)).

Since we’re not using 2008 but 2005, it’s not available to me. However, it gave me the inspiration to home brew my own datetimeoffset of sorts. Here’s what I’ve done:

I created two action items in my FactorySQL standard group. The first one I called ‘utc\_stamp’ and set its expression to the following:

```auto
CurrentUTCDateTime()
```

The second I called ‘datetimeoffset’ and set its expression to the following:

```auto
DateDiff(CurrentUTCDateTime(), CurrentDateTime(), "minutes")
```

I had the option to uncheck the Store time/date stamp option on the action tab of the FactorySQL Frontend but I left it checked because I saw some merit in having the ‘classic’ t\_stamp, namely the ability to have FSQL automatically delete data over a certain age.

At this point I have 3 time related columns in my table: t\_stamp, utc\_stamp, and datetimeoffset.

That covers the FactorySQL side.

With FactoryPMI, I now have the ability to change the XVAL\_COL\_NAMEs in the pens dataset of an Easy Chart component, effectively switching between UTC and local time on the horizontal (i.e., x axis) of the Easy Chart. However, I needed to compensate for the time shift that naturally results when changing the XVAL\_COL\_NAMEs. After experimenting, I’ve settled on the following scripts, which I have assigned to two buttons, to switch the time basis back and forth:

`# Switch to local time
import fpmi
chart = event.source.parent.getComponent('Easy Chart')
pens = chart.pens
changes = {"XVAL_COL_NAME":"t_stamp"}
for row in range(pens.getRowCount()):
	pens = fpmi.dataset.updateRow(pens, row, changes)
startDate = fpmi.db.dateFormat(chart.startDate, "yyyyMMdd HH:mm:ss")
endDate = fpmi.db.dateFormat(chart.endDate, "yyyyMMdd HH:mm:ss")
query = "SELECT COALESCE(MIN(t_stamp), CAST('%s' AS datetime)) AS StartTime, COALESCE(MAX(t_stamp), CAST('%s' AS datetime)) As EndTime FROM StandardGroupTable WHERE StandardGroupTable.utc_stamp BETWEEN '%s' AND '%s'" % (startDate, endDate, startDate, endDate)
times = fpmi.db.runQuery(query, "DataSource")
chart.startDate = times[0][0]
chart.endDate = times[0][1]
chart.pens = pens
``# Switch to UTC time
import fpmi
chart = event.source.parent.getComponent('Easy Chart')
pens = chart.pens
changes = {"XVAL_COL_NAME":"utc_stamp"}
for row in range(pens.getRowCount()):
	pens = fpmi.dataset.updateRow(pens, row, changes)
startDate = fpmi.db.dateFormat(chart.startDate, "yyyyMMdd HH:mm:ss")
endDate = fpmi.db.dateFormat(chart.endDate, "yyyyMMdd HH:mm:ss")
query = "SELECT COALESCE(MIN(utc_stamp), CAST('%s' AS datetime)) AS StartTime, COALESCE(MAX(utc_stamp), CAST('%s' AS datetime)) As EndTime FROM StandardGroupTable WHERE StandardGroupTable.t_stamp BETWEEN '%s' AND '%s'" % (startDate, endDate, startDate, endDate)
times = fpmi.db.runQuery(query, "DataSource")
chart.startDate = times[0][0]
chart.endDate = times[0][1]
chart.pens = pens`  
The COALESE() functions in the SQL query covers the case where there may be no data within the specified time span. In this case, I have it default to the same start and end times but I could do something else like determine the current time difference between local and UTC time and make the shift accordingly.

I bound the buttons’ Enable property to the following expressions so that they are only enabled when appropriate:`{Root Container.Easy Chart.pens}[3]="t_stamp"`  
for the “UTC Time” button, and `{Root Container.Easy Chart.pens}[3]="utc_stamp"` for the “Local Time” button.

This approach provides a lot of flexibility and is deterministic in the same way as the datetimeoffset datatype provided by SQL Server 2008. The data is just split between two columns.

Hope this helps someone. Of course, any comments or suggestions are welcomed.

---

<div class="post-metadata">

**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\
**Post date:** [August 25, 2009, 3:21pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/9 "2009-08-25T15:21:09Z")

</div>

Hmm, I thought I had replied yesterday, but something must have happened…

Ultimately, the solution you came up with is probably the best. For the sake of completeness, I wanted to mention that it IS possible to change the translator, but that you’re definitely going to have to restart the service, as that function is also used for SQLTags, data caching, and redundancy.

At any rate, like I said, I like the way you’re currently doing it better than changing the translator.

Thanks for the thorough follow up, I’m sure it’ll help a lot of people.

Regards,

---

<div class="post-metadata">

**Author:** ![Kyle\_Chase](https://avatars.discourse-cdn.com/v4/letter/k/9de0a6/32.png) [@Kyle\_Chase](https://forum.inductiveautomation.com/u/Kyle_Chase)\
**Post date:** [August 28, 2009, 9:52pm UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/10 "2009-08-28T21:52:06Z")

</div>

For MySQL, you can use the convert\_tz() command. It uses the timezone information of the server to convert the times. For most of our data, we insert using UTC time, then display using something like convert\_tz(datetime,‘UTC’,‘Canada/Mountain’) The list of available zones is located in mysql.time\_zone\_name

---

<div class="post-metadata">

**Author:** ![MickeyBob](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MickeyBob](https://forum.inductiveautomation.com/u/MickeyBob)\
**Post date:** [August 31, 2009, 1:32am UTC](https://forum.inductiveautomation.com/t/daylight-savings-time/612/11 "2009-08-31T01:32:29Z")

</div>

Kyle,

Thanks for the feedback on using MySQL.

I realize that storing the offset between the local time and UTC seems a bit redundant or even overkill, but Bart Duncan makes the argument for it in the “Problem 3” section of the blog referenced above.

Regards