Exporting data in different time zones

When you export your data, times in your data are converted to the local time zone. For example, if data was collected in Indian Standard Time (UTC+5:30), and if the time 17:30 is captured in a form, if that data is downloaded in Timbuktu (UTC+0:00) using either the server console or SurveyCTO Desktop, then that time value will appear as 12:00 in the downloaded data. This is done as a convenience, since if data is collected in more than one time zone, converting date and time values to a single relative time zone can be advantageous.

This conversion of time values applies to time, datetime, start, end, and SubmissionDate values. If you do not wish time and date values to be localized to your exporting time zone, there are a few strategies you can employ.

Users of the REST API will receive time and date values localized to UTC time (the system time of a SurveyCTO server), no matter what their local time and date is. Data published to datasets or externally (such as publishing to Google Sheets or with Zapier) will also be published in UTC time.

Export to a specific time zone

Method 1: Change computer's time zone

When you export using SurveyCTO Desktop, the time zone of your computer is used to determine which time zone the data should be converted to. If you change your computer's time zone, it will also change the conversion time zone for all fields in the data.

Method 2: Use a Stata command

If you are a Stata user, check out our new Stata command sctorezone to learn how to change the time zone of your time data.

Export actual time entered

Making sure all times are converted to the same time zone can be very helpful when coordinating and tracking activities across different time zones. But what if you would still like to know the original time that was entered, regardless of time zone?

Note: Both of these methods involve modifying your form design before data collection begins.

Method 1: Use the format-date-time() function

The easiest method is to include a calculate field that uses the format-date-time() function. For example, if the time type field is called "time", you can use this expression to store the time that was entered:

format-date-time(${time}, '%h:%M')

Here, %h will be replaced by the single-digit hour, and %M will be replaced by the double-digit minute.

Since that expression will be a text string instead of a time, it will not be converted during export, so you can use that field to see what the original time entered was. A major advantage of this method is that you get both the converted time in the original time type field, as well as the unconverted time in the calculate field.

Further reading: Using expressions in your forms

Method 2: Use select_one fields

Another method is to use select_one fields to capture the time. One field can be integers from 1-24 inclusive, and another can be integers from 0-59 inclusive. It is a good idea to give these fields the "minimal" appearance so they do not overwhelm the screen. The hour and minute fields can be on the same page using a field-list group. Since a select_one field cannot be converted based on time zone, the original time entered will be used.

Related: Constraining time fields in SurveyCTO

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.


Please sign in to leave a comment.