How to Convert Timestamps into Numerical Values in Q
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.
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 Timestamp = var;
var newStamp = Timestamp.split(“:”);
// Text would be split into “HH,MM,SS”
var hours = newStamp;
var minutes = newStamp;
var seconds = newStamp;
// 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)
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.