Alternate week granularity to allow for week starting either Sunday OR Monday and other granularities

[iktl]

Is your feature request related to a problem? Please describe. We work with a dataset where some customers consider a week differently, where a "week" is starting either Sunday or Monday. Cube hits most of our use cases, but the week granularity option conforms to the standard week observed by Postgres (our current adaptor). So, for some customers, we cannot use a week granularity and this breaks some reporting needs we have.

Describe the solution you'd like I'd like to be able to utilize a secondary week granularity option to opt out of the default behaviour, and roll up data to a week starting Sunday instead of a week starting Monday. I recognize that Postgres conforms to the ISO standard of a week starting on a Monday, and would want to maintain that as a the default behavior, hence adding week_sunday as an alternate option.

Describe alternatives you've considered I've been able to patch in support for a week starting Sunday by hotfixing the Postgres adaptor and where Cube validates the incoming query, but I suspect (though have not tested) this will break any option to pre-aggregate.

[paveltiunov]

@iktl Hey Isaac! Thanks for posting! This is really advanced issue. So indeed there's support only for iso week in cube.js. And it isn't only SQL generation but also some data normalization and rolling time series generation. So this code scattered across different components including frontend stuff.

To design solution I think we need to understand use case better: is there only one week granularity for your project which should be customized or should you support two of them simultaneously each for different reports?

[iktl]

Hi Pavel!

Some additional context is that we work with advertising data, some of which comes from Adwords for customers that we work with. We end up with a lot of reporting data stored at a day-level but then report on it at various granularities / timeframes. The issue creeps in where Adwords supports reporting at a week level with some variations as seen here https://developers.google.com/adwords/api/docs/guides/reporting:

• THIS_WEEK_SUN_TODAY
• THIS_WEEK_MON_TODAY
• LAST_WEEK_SUN_SAT

This basically means that we have a 50/50 split of customers who think about weeks (whether it's Sun-Sat or Mon-Sun) completely differently, and we need to be able to report in our systems the same way they report in Adwords. We currently use an in-house reporting solution which lets us define which week variant to use, where users can control which of the two "weeks" they want to use for weekly rollups.

So, it would be fair to say that while running Cube in front of our data, depending on the context for the request coming in we would probably use each week variant in almost equal measure.

[paveltiunov]

@iktl Yeah. That makes sense. So I believe we'd need to extract small package cubejs-time-granularity where we should put all these granularity definitions and then implement this new granularity across all the drivers.

[paveltiunov]

Makes sense to join also minute granularity discussion here as well.

[ifokeev]

@paveltiunov I use something like this to make custom granularity in my cases:

 SELECT
    *,
    COUNT(id) OVER w as trades,
    COUNT(id) FILTER (WHERE side = 'buy') OVER w as buys_count,
    COUNT(id) FILTER (WHERE side = 'sell') OVER w as sells_count,
    FIRST_VALUE(price) OVER w as open,
    LAST_VALUE(price) OVER w as close,
    (date_trunc('hour', to_timestamp( timestamp/1000 )) 
        + date_part('minute', to_timestamp( timestamp/1000 )) :: INT / {self.min_interval} * INTERVAL '{self.min_interval} min')
        as interval_start
FROM
    "{self.table_name}"

WINDOW w AS (
    PARTITION BY date_trunc('hour', to_timestamp( timestamp/1000 )) 
        + date_part('minute', to_timestamp( timestamp/1000 )) :: INT / {self.min_interval} * INTERVAL '{self.min_interval} min'

        ORDER BY timestamp
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

so it could be just a minutes property under granularity

[paveltiunov]

@ifokeev Do you mean to allow define SQL for granularity? Something like

timestamp: {
  sql: `timestamp`,
  type: `time`,
  granularities: {
    minute: {
      sql: `date_trunc('minute', ${CUBE}.timestamp)`
    }
  }
}

[ifokeev]

@paveltiunov sorry for the delay response.

I have to notice we have a time dimensions format https://cube.dev/docs/query-format#time-dimensions-format and it's kinda syntax sugar over just dimensions with type time (and daterange filter in playground). So my logic here just to allow user create own time dimensions with custom granularity property.

Your example is Ok, but maybe it's better to hide sql inside the drivers (but it will take more time to implement).

In conclusion: let's start with your example

[ifokeev]

UP. It's a very nice feature to have the compact view for dimensions with type time. Now I have to write:

    announcedDate: {
      sql: `${TABLE}."announcedDate"`,
      type: `time`
    },
    announcedDateMonth: {
      sql: `date_trunc('month', ${TABLE}."announcedDate")`,
      type: `time`
    },
    announcedDateWeek: {
      sql: `date_trunc('week', ${TABLE}."announcedDate")`,
      type: `time`
    },
    ...

I don't use timeDimensions because need to control granularity myself

[chicco785]

what about having ways to define the granularity "automatically", for example in grafana you can do that. when granularity is "automatic", grafana checks the time range under under consideration, for example if it is 1 day, it may set a granularity to minutes, if it is 1 week to 1 hour, if it is 1 year to 1 day, ...

[barakcoh]

+1 for having the quarter granularity as mentioned in #930 , very useful for business reports

[MattGson]

+1 for custom granularity (or window granularity options).

We currently bypass time dimensions in our queries so that we can do this.

An example dimension we use to get a rolling granularity rather than a fixed calendar granularity.

window30days: {
  type: `number`,
  sql: `DATEDIFF(NOW(), ${CUBE}.timestamp) DIV 30`
}

A custom granularity option could be:

timestamp: {
  sql: `timestamp`,
  type: `time`,
  granularities: {
    window30days: {
      sql: `DATEDIFF(NOW(), ${CUBE}.timestamp) DIV 30`
    }
  }
}

[TRManderson]

Over at Cubiko we're also very keen for a quarter granularity. Happy to contribute code, given a few pointers.

[sebastiancyndx]

We're also very keen on quarter. Can you provide some pointers on how to do this?

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[vine1993]

I think we may use the cube queryRewrite / queryTransformer to implement our own definition of week range, but the problem now is that cube gives the query already translated, and we need the original or a new column to figure out how to do it.

Example:

module.exports = {
    queryTransformer: function(query,context){
        if(query.timeDimensions){
            for(let i =0; i < query.timeDimensions.length; ++i){            
                if(query.timeDimensions[i].DateRange === 'This week'){
                     //here you can apply any logic you want to define what it's a week.
                      query.timeDimensions[i].dateRange = ['2022-01-01','2022-01-10']
                }
            }
        }

        return query;
    }
};

But for now, it's not possible because cube transform the query before that, so the DateRange it's no longer equals to This Week, now it's an array with the 2 real dates, because of that there is no way to know if it's this week, or last week or even a custom date range.

I think the ideal it's from cube applying a new property to timeDimensions with the real definition of the dateRange just to query writer, not for all the cube api.

timeDimensions: [
  {dateRange:['2020-01-01','2020-01-10'],dateRangeDefinition: 'This Week'},
  {dateRange:['2020-01-01','2020-01-10'],dateRangeDefinition: 'Custom'},
  {dateRange:['2020-01-01','2020-01-31'],dateRangeDefinition: 'This Month'}
]

For now, what I'm doing the fixing on the FRONT END with moment js.

export const fixWeekCondition = (chartQuery) => {
    if(chartQuery.timeDimensions)
    {
        const query = JSON.parse(JSON.stringify(chartQuery));
        query.timeDimensions.map((t)=>{
            if(typeof(t.dateRange) === 'string'&& t.dateRange.toLowerCase() === 'this week'){
                let start = moment.tz('UTC');
                let end = moment.tz('UTC');
                t.dateRange = [start.startOf('week').format(),end.endOf('week').format()]
            }

            if(typeof(t.dateRange) === 'string'&& t.dateRange.toLowerCase() === 'last week'){
                let start = moment.tz('UTC').add(-1,'week');
                let end = moment.tz('UTC').add(-1,'week');
                t.dateRange = [start.startOf('week').format(),end.endOf('week').format()]
            }

            return t
        })
        return query
    }

    return chartQuery
};

const queryWeek = fixWeekCondition('YOUR JSON QUERY HERE');
const { resultSet, isLoading=true, error } = useCubeQuery(queryWeek);

This code makes my query starts from Saturday, but if we allow users to do that on the backend we may solve all different scenarios since each user can implement his own definition through the query rewrite.

Thank you for the conversation @rpaik

[PieterVanZyl-Dev]

For anyone watching this thread

Quarterly support should be added within the next two releases after adding #3244 & #3285 Expect to see quarter in a playground near you soon @sebastiancyndx @TRManderson @barakcoh

[niramsellem]

This issue is a major problem for our project, since the week start for our customers is Sunday, it affects both the query (which as written here before can be manipulated from the frontend),but also the granularity - for example, range of month with granularity of weeks - the resulted weeks are calculated Monday to Sunday, which also causes problems with the data analysis and cannot be manipulated from the frontend. is there any other way to solve this?

[rashadksh]

I also need this feature.

[rpavan42]

Currently facing a similar problem statement of considering a custom 7 day date range as a week and wanted to know if there's a solution to this that's available now? Or what is the work around this situation? Thanks!

[anshjain18]

Hi team, Its been quite sometime since this feature has been requested. Are there plans to implement the same in near future?. I think if we can add support for +/- INTERVAL '1 day' to Cube sql API's date_trunc() function, can be a short term fix. Thanks

@paveltiunov @igorlukanin