Have an idea?

Visit Sawtooth Software Feedback to share your ideas on how we can improve our products.

Date data format explanation

I was wondering how the date data was best interpreted?

Example from one of our surveys ...

The time the interview began when viewing this survey through the admin module is: 26 - Aug - 2014  23:05:57 GMT.

When I view the data via the advanced menu option (by downloading the Data1 table) I get: 1409094357.

I know Excel and other software can convert date/time data to an integer of some sort (like the number of days since January 1 1900) but I couldn't quite work out what's happening here?

Any assistance is appreciated. Regards.
asked Sep 12, 2014 by Paul Moon Platinum (98,670 points)

2 Answers

+1 vote
Just to clarify, the sys_StartTime value represents the time the survey was started. The sys_EndTime value represents the time the survey was ended. The sys_ElapsedTime value represents the total number of seconds the person took to complete the survey.

These variables, created by a survey's server and stored along with a respondent's answers, are special timestamps that represent the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z). This timestamp is also known as the Unix epoch, Unix time, POSIX time, or Unix timestamp. We use this number so that if a survey was simultaneously started by individuals in New York City, and Munich, and Lagos, the same number will be stored, regardless of the respondent's time zone.

If you are examining the data in Microsoft Excel®, here is a handy formula to convert this code to a date and time value set to Greenwich Mean Time (GMT/UTC): =(A1 / 86400) + 25569. Don't forget to format the cell to display the date/time in the style you desire.
answered Sep 12, 2014 by Nathan Bryce Silver (9,505 points)
Thanks Nathan. I'll take a closer look at this next week and get back to you with any additional questions (hopefully none). I'll do some tests on some of the data I have recently captured. Regards.
0 votes
I usually export the SysEndTime and in SPSS it shows me the data and time stamp just as seen through the admin module.
answered Sep 12, 2014 by Jay Rutherford Platinum (50,145 points)
epoch time conversion