How to Convert Timestamps into Numerical Values in Q

How to Convert Timestamps into Numerical Values in Q

Timestamps are common in survey data. Sometimes we need calculate the difference between two timestamps. To do so, we need to convert them from being text variables into numeric variables with the same time base (e.g., minutes). Q can assist you to use either JavaScript or R to do the conversion and subsequent calculations.

Converting timestamps into minutes in Q with JavaScript

The standard format for a timestamp is “Hours:Minutes:Seconds”. In most cases, minutes are a preferable unit of choice because they are an easily understandable unit of time. To convert a timestamp into a measurement of minutes, break the timestamp up into its parts, convert each into minutes, and then combine. You can do this by multiplying the hours by 60, leaving the minutes alone, and dividing the seconds by 60. Sum these numbers and you’ll have a singular numerical expression of the timestamp in minutes. Alternatively, convert your timestamp into hours by leaving the hours alone, dividing the minutes by 60, dividing the seconds by 3600, and summing all three figures.

To generate this output in Q using JavaScript, you will need to create a JavaScript variable. This feature can be accessed from the menu bar path Create > Variables and Questions > Variable(s) > JavaScript Formula > Numeric. You can name this new variable in the Name field and give the variable a long-form label in the Label field. Make sure you enter all the JavaScript you write into the Expression window. For ease of reference, the Variables section lists all of the variables within your study. In addition, commonly used functions are housed in the Common Methods section on the right (read more about Q’s native JavaScript functions here).

Process

First, we break up the timestamp into its parts and then we run the mathematical functions. To split the data, you can use JavaScript’s native split function. The syntax for this is:

var.split(separator)

where var is replaced with the variable name for your text variable. For a timestamp with the format “00:00:00”, we can use the following:

var.split(“:”)

Next, you will need to reference each of the split strings. To reference the individual elements, you’ll need to use an index number. The index number is used to recall a particular element in an array. It uses square brackets []. Indexes in JavaScript start at 0. You can then put these pieces into the corresponding equation to calculate a single numerical value.

var Timestamp = var;

var newStamp = Timestamp.split(“:”);

// Text would be split into “HH,MM,SS”

var hours = newStamp[0];

var minutes = newStamp[1];

var seconds = newStamp[2];

// Convert timestamp into minutes

var convertedmins = (hours * 60 ) + minutes + (seconds / 60 )

// Convert timestamp into hours

var convertedhrs = hours + (minutes / 60 )+ (seconds / 3600 )

You could repeat this process for a second timestamp variable if needed, and then calculating the difference would be a simple subtraction: convertedmins2 – convertedmins1

Converting timestamps into minutes in Q with R

To generate this output in Q using R, you will need to create an R variable. This feature can be accessed from the menu bar path Create > Variables and Questions > Variable(s) > R Variable. You can name this new variable in the Variable Base Name field and give the variable a long-form label in the Question Name field. Enter all the R code into the R CODE section. Remember to push the play button to execute it!

You can use the strsplit() function to separate the timestamp, and then use a function to combine the values.

x <- var ## where var is your timestamp text variable

y <- strsplit(x, “:”)

z <- do.call(rbind, y)

Next, you can set new variables for each of the split segments by using index numbers in brackets and converting them into numbers using the as.numeric() function. Indexes in R start at 1 (as opposed to JavaScript which start at 0. You can put these elements into the corresponding equation to calculate a single numerical value.

hours <- as.numeric(z[,1]);

minutes <- as.numeric(z[,2]);

seconds <- as.numeric(z[,3]);

# Convert timestamp into minutes

convertedmins <- (hours * 60) + minutes + (seconds / 60)

# Convert timestamp into hours

convertedhrs <- hours + (minutes / 60) + (seconds / 3600)

Click the Play button to generate a preview of the output in the window on the left. If you are happy with the results, click the Add R Variable button for Q to add variable to your project.

We hope you found this post helpful! Discover how to do more in Q by checking out Using Q

About Daren Jackson

Daren has worked in market research for over a decade, cutting his teeth at major firms like J.D. Power and Associates and Market Track. In the research supplier function, he has extensive experience in customized and in-depth reporting, working with some of the wold’s top retailers and automotive manufacturers. On the technical side, he has spent over 5 years building databases, providing support and training users on market research software. He holds a Bachelors of Science degree from the University of Southern California.