[Feature]: time_bucket start weekly bucket on Monday instead of Thursday for integer based time columns

[Vinay-Raghu]

What problem does the new feature solve?

Postgres 14.4
TimescaleDB 2.7.2
Timescale Cloud

To reproduce issue:

CREATE TABLE my_data_new (
  time BIGINT NOT NULL,
  device_id INTEGER NOT NULL,
  value1 INTEGER NOT NULL,
  value2 INTEGER NOT NULL,
  value3 INTEGER NOT NULL,
  site_id INTEGER NOT NULL
);
/*
 chunk time interval is 1 day (86400000 = 1000*60*60*24)
 */
SELECT create_hypertable(
    relation => 'my_data_new',
    time_column_name => 'time',
    create_default_indexes => false,
    chunk_time_interval => 86400000,
    partitioning_column => 'site_id',
    number_partitions => 1
  );

-- Generate timeserie with data all days between now and 2 years ago
INSERT INTO my_data_new (time, device_id, value1, value2, value3, site_id)
SELECT time,
  device_id,
  random() AS value1,
  random() * 100 AS value2,
  random() * 1000 AS value3,
  random() * 1000 AS value4
FROM generate_series(
    (extract(
      epoch
      from now()
    ) * 1000)::BIGINT - 86400000::BIGINT*365.25*2,
    (extract(
      epoch
      from now()
    ) * 1000)::BIGINT,
    (86400000)::BIGINT
  ) AS g1(time),
  generate_series(1, 4, 1) AS g2(device_id);```

CREATE MATERIALIZED VIEW data_new_cagg
WITH (timescaledb.continuous) AS
SELECT time_bucket('604800000', time) AS bucket,
   avg(value1) AS avg_value
FROM my_data_new
GROUP BY bucket;

SELECT * FROM data_new_cagg;

The result from querying the continuous aggregate from above results in:

The new feature would start this time bucket on Monday, instead of Thursday (since its based off epoch time). It's important to resolve because this will help align integer-based time columns with the timestamp based columns which are compliant with the ISO standard for Monday as the start of a week

What does the feature do?

The feature would allow the time_bucket for integer based time columns to be compliant with ISO standard for Monday as the start of the week (similar to time_bucket with timestamp).

Implementation challenges

I'm sorry I do not have any ideas. OFFSET accomplish this outside of a continuous aggregate but not sure of the internals o