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.