Date/time calculations

Updated at February 20th, 2020

Protobi can help with converting date values into a desired format for analysis. In our Date/time values tutorial, we demonstrated a few basic ways to parse and analyze dates. Here, we will discuss some advanced date calculations that can be done in Protobi.

We can use Protobi's backend data process to perform calculations such as: 

  • Converting separate year/month/day values to date strings
  • Time interval calculations

Converting separate date values to strings 

Surveys may elicit dates, not as dates, but as separate questions (e.g. year, month, day). We can use data process to convert these into date strings that Protobi can recognize. 

Consider a question that asked for month and year separately: 

Note: The month values in q1_2 are actually numeric, and month names are the format.

Create the process

To convert month and year from the above example into date strings, create a data process:

  • Go to the data tab of your project 
  • Create a new process (or edit an existing process) 
  • Set the process as primary
  • Press "Edit/Run" to get to process pre-calculate
  • Paste in the code
  • Press run, and save.

Code: convert to date strings

The code starts with a simple return rows, which just returns an array containing your dataset. The program iterates over this array and creates a new data column for row.q1 that concatenates the month and year values into strings.

We used an existing key "row.q1" as the element that will contain the string values. However, you can make up a new key which will write a new element containing the date strings into existence. 

Paste the code into the process pre-calculate, and make any necessary changes. Press run, and save.

var rows = data.main;

rows.forEach( function(row) { 
       row.q1 = row.q1_1 + '-' + row.q1_2.padStart(2, '0') + '-01'+ 'T12:00:00' // create YYYY-MM-DD-T formatted string
    }
})

return rows;


For projects with many dates to convert, the code below may be easier to use and keep organized.

Alternative code option

For the date fields enter:

  • key: Parent group's key can be used or use a new key 
  • month: Month element's key
  • year: Year element's key
var rows = data.main;

rows.forEach(function(row){
    _.each(date_fields, function(entry) {
        if (row[entry.year]) {
            var date_str = row[entry.year] + '-' + row[entry.month].padStart(2, '0') + '-01' + 'T12:00:00'
            row[entry.key] = date_str//moment(date_str, 'YYYY-MM')
        }
    })

// Convert Month/Year pairs to date strings
var date_fields = [
  {key:  "q1",   month: "q1_1", year: "q1_2"}, // combine q1 month and year
  {key:  "q2",   month: "q2_1", year: "q2_2"}, // combine q2 month and year
  {key:  "q3",   month: "q3_1", year: "q3_2"}, // combine q3 month and year

]

return rows


Result: date strings

Open your project. If it's already open, refresh the page. Keys that didn't previously exist, but were written into existence in the code should be in fields. 

Once you find the element containing the date strings, in element properties change type from "empty" to "date" (seen here). 

Below, we see q1 is populated with the resulting date strings. The dates are not in ISO date format, but you can customize date formats in JSON.

You can hide, remove, or delete the separate month and year child elements . 


Transform dates

For groups with type "date" you can transform to a unit of time (e.g. year, quarter, etc.): 

Below, we transformed q1 to year and hid children:

Time intervals

We can also use process code to calculate time intervals. In the example below, we want to calculate the time between dates entered in q3 and q4. 

Code: times intervals

Set up the data process (see steps above) but paste in the time interval code seen below.

You can delete or add rows of code as necessary, depending on how many time intervals you need to calculate. 

For the time fields enter:

  • key: Create a key that can be easily found and identified (e.g. q3_q4)
  • start: Start time (e.g. q3)
  • end: End time (e.g. q4)
  • unit: Unit of measurement for the interval (e.g. month, year)

 Press run, and save. 

Expand to see time interval code

var rows = data.main;
function getAbsoluteMonths(dt) {
  var momentDate = moment(new Date(dt))
  var months = Number(momentDate.format("MM"));
  var years = Number(momentDate.format("YYYY"));
  return months + (years * 12);
}

function getMonthsBetween(end, start) {
    if (end && start) return  getAbsoluteMonths(end) - getAbsoluteMonths(start)
    else return null
}
// convert dates to intervals
rows.forEach(function(row) {
    
    _.each(date_intervals, function(entry) {
        if (row[entry.start] && row[entry.end]) {
            var start = moment(row[entry.start], 'YYYY-MM')
            var end = moment(row[entry.end],'YYYY-MM')
            row[entry.key] =  getMonthsBetween(end, start)
        }
    })
    
})

// Convert date strings to time intervals
var date_intervals = [
    {key: "q3_q4", start: "q3", end: "q4", unit: 'month'}, // q3 date to q4 date
    {key: "q4_q5", start: "q4", end: "q5", unit: 'month'}, // q4 date to q5 date

]

return rows


Result: time intervals

Open your project (or refresh the page). The interval elements should be in fields because they were written into existence in the code. You can use the tree to search for elements if you're having trouble locating them. 

In this tutorial, we discussed two common types of date/time calculations that can be done in Protobi. Your Protobi project may need different or more complex time calculations not yet written here. Contact support@protobi.com and we'll help you get setup for your case.

See related

Date/time values

Project data

Process data in Protobi

Was this article helpful?