Cube.js time interval best practice

Question

I have table with working time interval. what is the best way to model this in cube.js to allow time dimension queries like total worker working time, between dates, total worker time in day and so on.

Thanks!

the table looks like:

CREATE working_times test_timestamp (
    id INT AUTO_INCREMENT PRIMARY KEY,
    workerId VARCHAR(255) NOT NULL,
    from TIMESTAMP,
    to TIMESTAMP
);

and the cube:

cube(`WorkingTimes`, {
    sql: `SELECT * FROM db.working_times`,    

    measures: {
      ???
    },

    dimensions: {
      from: {
        sql: `from`,
        type: `time`
      },
      to: {
        sql: `to`,
        type: `time`
      },

    },
  });

Answer 1

It can be defined as a timestamp difference. Pretending it's MySQL:

cube(`WorkingTimes`, {
  sql: `SELECT * FROM db.working_times`,    

  measures: {
    workingMinutes: {
      sql: `TIMESTAMPDIFF(MINUTE, ${to}, ${from})`,
      type: `sum`
    }
  },

  dimensions: {
    from: {
      sql: `from`,
      type: `time`
    },
    to: {
      sql: `to`,
      type: `time`
    },

  },
});