vbScript "Now" time is correct in debug or MsgBox but incorrect when saved

[dprett]

Hi there,

I have been encountering an issue with vbScript time handling that I can't quite explain. When calling the "Now" function in vbScript to grab the current datetime, it appears correct in both debug mode variable inspection and MsgBox manual verification - but when I save that time to the database it appears 4 hours behind.

Our server times are all correct, actively setup with NTP time sync (EST time zone in my case). The workstation I am testing the script on is also time sync'd and displaying the correct time. 

I have a button on a mimic that calls the script below. The script's purpose is to create a new record on a Data Table object and pre-populate the record with some fields.

sub StartNewP2P()
	Dim p2pDataTableObj
	Set p2pDataTableObj = Server.LookupObject(P2P_DATA_TABLE_OBJECTID)
	recordId = p2pDataTableObj.Interface.AddRecord
	p2pDataTableObj.Interface.SetValue "CONTROLLER", recordId, Server.UserName
	p2pDataTableObj.Interface.SetValue "SYSTEM", recordId, Mimic.SystemName
	p2pDataTableObj.Interface.SetValue "WORKSTATION", recordId, App.WorkstationName
	currentTime = Now
	MsgBox currentTime
	p2pDataTableObj.Interface.SetValue "DATETIME", recordId, currentTime
	RefreshListBoxQuery()
end sub

When calling the script above (with a breakpoint at the MsgBox call) I see the value of currentTime is the correct value (in EST for my case). When the MsgBox displays, it is also the correct time in EST. However, when I write the value of currentTime to the Data Table it is always exactly 4 hours behind. I have also seen this same behavior when trying to insert custom events via *.Interface.LogInterfaceEvent calls. 

The workaround is simple, I add: 

eventTime = DateAdd("h", 4, Now)

and the time is correct when written to a data table or elsewhere.

Clearly something is off, but I don't know what. I don't see any options to tweak in Server Config or anything incorrect with our System/Workstation Windows time configuration.

Any help fixing this is appreciated - it's not affecting me in anyway meaningful way, but I really want to know why it's happening.

System version: GeoSCADA 2023 November 2024 Update

[AndrewScott · Accepted solution]

This is a time zone issue.

The Now function in VBScript returns the current local time (in Eastern Time in your case).

The Geo SCADA database stores all times in UTC (which is currently 4 hours behind Eastern Time).

You therefore need to convert currentTime from local time to UTC before writing it to the data table.

currentTime = LocalTimeToUTC(Now)

See help: LocalTimeToUTC 

[dprett]

Andrew,

Thanks for the reply. I don't know why my brain forgot that all timestamps in the database were stored in UTC.

Cheers!