How can I insert local time in database from Survey123 form?

Question

I have a survey that asks for the dat and time a sample was collected and incubated. This survey is linked to a Feature Service in my ArcGIS server environment which references data in my company's locally hosted database, SQL Server.

Whilst creating a report for the data entered from the survey I discovered that the timestamp is set in UTC time, which is a 6 hour difference from my time.

I set this calculated field in the 123 Connect form, however this doesn't seem to work as nothing is inserted into that column in the database when submitting a survey:

How can I overcome this? My time zone is UTC -6

Jim Moore · Answer

Hi Kristin

Would it be possible to provide your XLSForm so I can take a closer look? You can attach files to a GeoNet reply using the advanced editor.

Thanks,

Jim

KristinVernon1 · Answer

Hello thanks for the response. I updated the calculation but my problem is that when submitting a form the datetime value is not calculated and inserted in that field. Is there something else I should add?

Jim Moore · Answer

Hi Kristin

There are a couple of methods that might help.

While date/time fields in a feature service are stored in UTC, Survey123 (and other ArcGIS applications) will display the date/time in your local time zone. You can save this value (in local time zone) to a string using the format-date() function. For example, using a calculation similar to this in a text question:

format-date(${datetime1},'%d/%m/%Y %H:%M')

If you need the 'local' date/time to stay in date/time format, you could use something similar to your calculation above but with a slight correction. The pulldata("@property", 'utcoffset') expression returns the UTC offset in hours for your location. Multiply this offset by 1000 * 60 * 60 to get milliseconds (for Epoch time). So your calculation would look like:

${datetime1} + (pulldata("@property", 'utcoffset') * 1000 * 60 * 60)

Note that Epoch (UNIX) time calculations work well in the Survey123 field app but not in the web app; if you're using your survey in both the field app and the web app it'd be better to use decimal time for your calculations. There are a set of formulas for converting to decimal time.

See this blog for more detail and useful examples: https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis

And this help topic on dates and times in Survey123: Decimal date time—Survey123 for ArcGIS | Documentation

Hope this helps

Best,

Jim