Daylight saving time and datalog in FTV-SE (SQL)

crawler009

Member
Join Date
Feb 2012
Location
Planet Earth
Posts
237
Hello guys

i have a big problem with the datalog in FTV-SE (v10).
How do you guys manage the daylight saving time (summer time) change?
For the alarm and diagnostic log its no problem, because they are always saved in utc. But the datalog appearently uses local time.
I have attached 4 screenshots of what happens when the time changes to daylight saving time (i didnt change the clocks' time, only enable/disable daylight saving time, to simulate what will happen in march and october.

Basically the time in the datalog, trends and the application is correct only after a restart of Windows (after the the time change).

Depending if i already restartet the runtime or windows after the change to Daylight saving time on/off, i have different times shown in the FTV, and also on the timestamp of the data.
This inconsistency will make the customer not very happy i guess, specially for audit and trending data and reports.

But i think i'm not the only one with this problem, how do you guys manage the time change in FTV?

1. Before Time change.jpg 2. After change to summer time.jpg 3. After restart of runtime.jpg 4. After restart of Windows.jpg
 
I had similar problem in the past with FTView 7.00 we end up turning off daylight saving time on all servers and set all time to UTC.

Not sure If it will work but you may try to update your time via vba script. Personally have not tried it might be worth to give it a try.
 
I had similar problem in the past with FTView 7.00 we end up turning off daylight saving time on all servers and set all time to UTC.

Not sure If it will work but you may try to update your time via vba script. Personally have not tried it might be worth to give it a try.

Ok, turning off daylight saving time would be a posiblity, but (still) not my favorite option. I'll see if i can do something with updating the system time manually.
Or i have to tell the customer, turn off the plant on the evening before the time change, and start after 3 am. ;)
 
For continuous plants where downtime is £10,000/min+ I set the server to UTC+00:00, regardless of the local timezone. My faith in programmers of industrial automation software isn't such that I would bet they test all of their functions for daylight savings and timezone issues, with the reward if I win being that my operators don't have to add a number of hours to any data they get.

I have seen in Australia part of a system that didn't have the microsoft patch (Government changed the daylight savings dates in 2009), and it did not play well with his friends.
 
For continuous plants where downtime is £10,000/min+ I set the server to UTC+00:00, regardless of the local timezone. My faith in programmers of industrial automation software isn't such that I would bet they test all of their functions for daylight savings and timezone issues, with the reward if I win being that my operators don't have to add a number of hours to any data they get.

I have seen in Australia part of a system that didn't have the microsoft patch (Government changed the daylight savings dates in 2009), and it did not play well with his friends.

Ok, maybe thats the best option. How do you manage the fact that the times on the trends and in the audit and alarms are then also UTC+0 and not local time? Normaly the customer want the logs and trends (and reports) in local time.

Just as a side note. I tried to updating the time manually (per vba). I could write to system\hour and system\minute, and on the display and trend the time were updated (to the new time), but the real data stored in the sql were still with the old time o_O until the reboot of windows.
 
Again, had similar problems with customers not liked the idea of time being shifted by one hour as it was confusing however we managed to explain the difficulties to achieve time shift and eventually they accepted it.

What you could do wit sql is have another column called local_time_zone and then write a stored procedure or a function that would shift your original timestamp by one hour. Then when you call your data for reporting or trending you could use that new local_time_zone time. It is probably not the best solution but it might help a bit.
Having sad that there if you dig deeply in sql settings you can change server time settings to locale. It is not the server operating system time zone it is the sql settings. It might then do the job without having to write a procedures or rebooting the server.
 
Again, had similar problems with customers not liked the idea of time being shifted by one hour as it was confusing however we managed to explain the difficulties to achieve time shift and eventually they accepted it.

What you could do wit sql is have another column called local_time_zone and then write a stored procedure or a function that would shift your original timestamp by one hour. Then when you call your data for reporting or trending you could use that new local_time_zone time. It is probably not the best solution but it might help a bit.
Having sad that there if you dig deeply in sql settings you can change server time settings to locale. It is not the server operating system time zone it is the sql settings. It might then do the job without having to write a procedures or rebooting the server.

I understand. I'm affraid that the customer will also complain about, lets see.

Right now i have this colum with local time for Alarms, events and diagnostics. But for the trend data its not so easy, because depending if the runtime or Windows already restartet after the time shift, the offset to UTC is different.
But thanks for for the interesting ideas, i have to find a way (around Rockwell FTV *$*£*).
I'll check the posibility with the sql server locale time settings. I'm really not a expert of SQL, but i have some basic knowledge about it and also sql scripts (learned during this project :) ).
Do you know, is it posible to add timestamps from the sql server the moment when new data is insertet? Because right now the timestamps come from FTV. I'll dig in that direction.

Thanks a lot and best regards
crawler009
 
DST is just a pain everywhere, and really should just go away.

With Ignition, I have an hour dead space in the spring, and an hour of doubled data in the fall, so I have it do nothing in the spring, and just disable logging in the fall. I suppose I could just have it create a new partition in the DB, or switch them, but honestly, I can afford to lose an hour of data.

It also helps a bit that I have a script in Ignition that updates the clocks in all of the PLC's on a regular basis, so it keeps them all in DST/ST Sync.
 
Do you know, is it posible to add timestamps from the sql server the moment when new data is insertet? Because right now the timestamps come from FTV.

crawler009

I am assuming you are using a FTView Datalog model to insert data to SQL. In that case no idea how to time stamp it wit SQL time apart from manipulating the data that is already inserted (like described above using stored procedure)
 
@crawler:
You can try just adding an auto-timestamp field to the FTView Data tables. Add a column like
DateCreated DATETIME NOT NULL DEFAULT(GETDATE())

I don't know if that will screw up View, so try it on a test table for datalogging..
 
DST is just a pain everywhere, and really should just go away.

Totally agree. Thats where rusia already is ahead of us :nodi:

@crawler:
You can try just adding an auto-timestamp field to the FTView Data tables. Add a column like
DateCreated DATETIME NOT NULL DEFAULT(GETDATE())

I don't know if that will screw up View, so try it on a test table for datalogging..

I'll try that, thanks for the idea. Maybe in that way i'm independend of the FTV service time.
 
Last edited:
Maybe in that way i'm independend of the FTV service time.

If you want to be independent of FTView datalog model you can write a VBA script to insert a data to SQL DB.
Then you can sample your datalogging as you like and you can use a PLC time as a timestamp If that is helpful.
 
If you want to be independent of FTView datalog model you can write a VBA script to insert a data to SQL DB.
Then you can sample your datalogging as you like and you can use a PLC time as a timestamp If that is helpful.

Yes, thats true. Maybe that will be at the end the best solution, so i know what the system does.
 
Just thinking, you could probably put a trigger for the data tables. Maybe put a trigger like this on the tables you are concerned about:

Code:
CREATE TRIGGER trig_FloatUpdate
on [FloatData]
INSTEAD OF INSERT
AS
   BEGIN
      INSERT IGNORE INTO FloatData
      SELECT GETDATE(),
             Millitm,
             TagIndex,
             Val,
             Status,
             Marker
      FROM Inserted
   END

I haven't tested that, but it SHOULD substitute the current SQL timestamp for the FTView provided one on every insertion into FloatData.
 
@crawler:
You can try just adding an auto-timestamp field to the FTView Data tables. Add a column like
DateCreated DATETIME NOT NULL DEFAULT(GETDATE())

I don't know if that will screw up View, so try it on a test table for datalogging..

hi guys
at the end i went this way and it works very good. Thanks for the input rdrast.
I only modified the getdate to GETUTCDATE.
Now all my timestamps are in utc. The alarms and diagnostics were already in UTC. Now theres no timejump in the datalog at the moment of the dst timechange.
For the printout i made a SQL query which convert the time back to local.

What i really dont understand, why Rockwell made a good job with diagnostics and alarms (both are stored in UTC, but shows local time in the alarms and diagnostic logs, and surprisingly it works really like it should, even if you are before or after a dst timechange) but with the datalog they screwed up again. The data in the SQL have the same timestamps between 2 and 3 am on the day of the timechange. In the chart they have 2 times the time between 2 and 3 am, but the data is mixed on 1 of them :sick: (see the picture). And: on the chart between 02:00 am and 02:00 am there are no data. Only the interpolation does make it like there is data.

I even am in contact with the support of Rockwell, but the guys there are dumber then their FactoryTalk View (which they also dont know very well, or they dont care about their customer who pay a lot of money for %%&&%&&%***). They send me different article from the rockwell knowledgebase which are NOT a solution for my problem. I really dont understand why the american customers still want to use FactoryTalk as their HMI/Scada plattform.

sql.jpg
 
Last edited:

Similar Topics

How to change Daylight saving time ON/OFF automatically in Crimson3.0&3.1/Redlion HMI How to change Daylight saving time ON/OFF automatically in...
Replies
5
Views
2,144
Hi, am trying to write to the system clock using UnityProS and M340 to change to NZ daylight saving automatically, I have done the same on Siemens...
Replies
10
Views
5,974
Everybody prepared for the Extended daylight saving here the news from rockwell http://www.ab.com/news/daylightsavings.html
Replies
0
Views
2,173
HI, I need to automatically update the RTC on an FX1 for daylight saving - has anyone any code that will do this simply? Nigel
Replies
0
Views
3,633
Now that the new energy bill has been signed and Daylight Saving Time has a new start and end next year, who will be having to revise programs...
Replies
26
Views
6,128
Back
Top Bottom