Daylight Savings Time

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?

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,

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?

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

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.

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

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!

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

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:

CurrentUTCDateTime()

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

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.

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,

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

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