ActiveUsers not working if grouping is larger than hour

[janrenz]

Describe the bug
I have setup a very basic MVP to determine ActiveUsers based on the docs. It only works if grouping is set to hour.

To Reproduce
Project setup: PostgreSQL 11.5
Event Schema:

cube(`Events`, {
  sql: `SELECT * FROM events.events`,
  
  joins: {
    
  },
  
  measures: {

    monthlyActiveUsers: {
      sql: `uid`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `30 day`,
        offset: `start`
      }
    },

    weeklyActiveUsers: {
      sql: `uid`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `1 week`,
        offset: `start`
      }
    },

    dailyActiveUsers: {
      sql: `uid`,
      type: `countDistinct`,
      rollingWindow: {
        trailing: `1 day`,
        offset: `start`
      }
    }
  },
  
  dimensions: {
    url: {
      sql: `url`,
      type: `string`
    },
    
    uid: {
      sql: `uid`,
      type: `string`
    },
    
    timestamp: {
      sql: `timestamp`,
      type: `time`
    }
  }
});

Adding some events to the database. Select metric and choose date range and group level hour. Works fine. Select day as grouping -> no results
Expected behavior
Works with all available time grouping options.

Screenshots

[paveltiunov]

@janrenz Hey Jan! Thanks for posting this! Very interesting. Seems like timezone issue. Could you please share what is returned back in /cubejs-api/v1/load response?

[janrenz]

Sure :) So, its seems to be only a UI problem in the playground ...

http://localhost:4000/cubejs-api/v1/load?query=%7B%22measures%22%3A%5B%22Events.dailyActiveUsers%22%5D%2C%22timeDimensions%22%3A%5B%7B%22dimension%22%3A%22Events.timestamp%22%2C%22granularity%22%3A%22day%22%2C%22dateRange%22%3A%22Last%207%20days%22%7D%5D%2C%22filters%22%3A%5B%5D%7D

{"query":{"measures":["Events.dailyActiveUsers"],"timeDimensions":[{"dimension":"Events.timestamp","granularity":"day","dateRange":["2019-09-17","2019-09-24"]}],"filters":[],"timezone":"UTC","dimensions":[]},"data":[{"Events.timestamp":"2019-09-17T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-18T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-19T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-20T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-21T02:00:00.000","Events.dailyActiveUsers":"1"},{"Events.timestamp":"2019-09-22T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-23T02:00:00.000","Events.dailyActiveUsers":"0"},{"Events.timestamp":"2019-09-24T02:00:00.000","Events.dailyActiveUsers":"0"}],"annotation":{"measures":{"Events.dailyActiveUsers":{"title":"Events Daily Active Users","shortTitle":"Daily Active Users","type":"number"}},"dimensions":{},"segments":{},"timeDimensions":{"Events.timestamp":{"title":"Events Timestamp","shortTitle":"Timestamp","type":"time"}}}}

[paveltiunov]

Yeah. I see. It's timezone issue.

[janrenz]

So something i can fix in my data or in my schema? Or something in cube.js..?

[paveltiunov]

@janrenz In Cube.js. I think there's a problem with this line: https://github.com/cube-js/cube.js/blob/e26ad42bb0c876c5c7c9490c91efbcf9089a7441/packages/cubejs-api-gateway/index.js#L66

[janrenz]

I can confirm this. The input value if grouping is set to day or larger is an instance of Date, but
moment("2019-09-16T02:00:00.000") producing a local time like Mon Sep 16 2019 02:00:00 GMT+0200 (note the timezone offset) instead of Mon Sep 16 2019 02:00:00 GMT+0000 wihich is the result if the utc method is used.
This then seems to break the UI.

[paveltiunov]

@janrenz What's the type of value in your case? Is it string?

[janrenz]

its a Date. if i change the code to:
return (value instanceof Date ? moment.utc(value) : moment.utc(value)).format(moment.HTML5_FMT.DATETIME_LOCAL_MS);
it works, also showing that value instanceof Date ? is true in my case.

[janrenz]

As the docs state: Cube.js always expects timestamp with timezone
so lets close this, as this is related to a wrong date format.

[paveltiunov]

@janrenz Actually it's a bug. Generated SQL for time series contains incorrect date type. Just fixed.

[janrenz]

Great. Thx!