Cube.js时间间隔最佳实践

Question

我有一个有工作时间间隔的表格。在cube.js中对此建模的最佳方式是什么，以便允许时间维度查询，如员工的总工作时间、日期之间、每天的员工总时间等。

谢谢!

该表如下所示：

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

和立方体：

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

    measures: {
      ???
    },

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

    },
  });

Answer 1

它可以定义为时间戳差异。假装是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`
    },

  },
});