Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84649members
353989posts

[Imported] Convert Local Time to UTC

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

sbeadle
Janeway Janeway
Janeway
0 Likes
0
582

[Imported] Convert Local Time to UTC

>>Message imported from previous forum - Category:Scripts and Tips<<
User: mchartrand, originally posted: 2018-10-23 17:18:01 Id:176
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

_________________

**_NIWTelemetry:
Hi
I am using the following script to populate a data table (user name, date and time) every time a user navigates to a mimic._**

_Sub Mimic_Load()
Dim SqlResult
Dim User
Dim SQL
Dim CurrentDTString
User = Server.UserName
CurrentDTString = CStr(DatePart("yyyy",Date))+"-"+Right("0"&DatePart("m",Date), 2)+"-"+Right("0"&DatePart("d",Date), 2)+" "+CStr(Time)
SQL = "INSERT INTO UseageTable VALUES ('"+ User +"',"+"timestamp'"+ CurrentDTString +"')"
Set SqlResult = Server.Query(SQL)
If SqlResult.Error Then
End If
End Sub_

**_Problem is everything works as expected but the time is always one hour ahead of local time. I need to convert Time to Local time and looking for some help please. Thanks._**

___________

hardin4019:
Hey Tony,

Pull up the help menu in CS and search "Script UTC to Local" and there is an example of how to change to local time you can put into an internal variable inside your script, then use that internal variable for the rest of your script.

___________

**_NIWTelemetry:
Hi Shawn
Thank you for the update. I made a slight error - I actually need to convert local time to UTC.
I have tried to use the 'LocalTimeToUTC' as below - but does not appear to work. Do you have any suggestions? Thanks._**

_Sub Mimic_Load()
Dim SqlResult
Dim User
Dim SQL
Dim CurrentDTString
User = Server.UserName
CurrentDTString = CStr(DatePart("yyyy",Date))+"-"+Right("0"&DatePart("m",Date), 2)+"-"+Right("0"&DatePart("d",Date), 2)+" "+CStr(LocalTimeToUTC(Time))
SQL = "INSERT INTO UseageTable VALUES ('"+ User +"',"+"timestamp'"+ CurrentDTString +"')"
Set SqlResult = Server.Query(SQL)
If SqlResult.Error Then
End If
End Sub_

___________________

bevanweiss:
Is there a reason that you're storing the DateTime as a string?
This would be considered bad database practise. The correct datatype for a DateTime value is of course a DateTime datatype... of which ClearSCADA datatables support this.
You should then be able to use an SQL builtin GETUTCTIME (I actually can't remember the name of the function...) to do the INSERT stuff.

_________________________


**_NIWTelemetry:
Hi
The DateTime is being picked up in a Crystal Report and I need it to be stored as a string for another purpose.
Interesting comment about GETUTCTIME - I will look into this. Thank you for your reply._**

_________________________

bevanweiss:
You can format a DateTime column as you like in Crystal Reports, no need to store it in the table as a string for that.
And your other purpose should be able to handle it as a DateTime format field also, best stored in UTC also (no Daylight Saving Time shifts).

GETUTCTIME was wrong, it should be {OPC 'NOW'}..

______________________

**_NIWTelemetry:
I've changed the code to use: LocalTimeToUTC(NOW) and its working just as i want. Thank you for your comments._**