Hi all,
I am currently configuring timescale to create data chunks every 24 hours, using chunk_time_interval => interval ‘1 day’.
It looks like it’s always chunking using GMT time-zone, i.e. The cut off time is at 00:00 GMT+0. Is there a way I can configure the cut-off time to a different time-zone? It makes sense because I want every data chunk to cover a whole local business day.
Thank you.
Best regards,
Tony Cheung
Hello @asiayeah, yes. You can override the partition_func.
CREATE TYPE report AS (reported timestamp with time zone, contents jsonb);
CREATE FUNCTION report_reported(report)
RETURNS timestamptz
LANGUAGE SQL
IMMUTABLE AS
'SELECT $1.reported';
SELECT create_hypertable('measurements', by_range('report', partition_func => 'report_reported'));
Do you have any performance issues querying on 2 chunks?
Thank you, @jonatasdp for the quick reply.
Your suggestion makes sense.
For example, I override the partition_func as below so that TimeScale chunks at 05:00.
> create table sample_table (time timestamptz);
> create or replace function convert_timestamp(timestamptz) returns timestamp as $$ select ($1)::timestamp - interval '5 hours' $$ language sql immutable;
> select create_hypertable('sample_table', by_range('time', interval '1 day', 'convert_timestamp'));
> insert into sample_table values('2024-01-13 04:58:00.000000');
> insert into sample_table values('2024-01-13 04:59:59.999999');
> insert into sample_table values('2024-01-13 05:00:00.000000');
> insert into sample_table values('2024-01-13 05:00:01.000000');
That works perfectly fine. I can confirm the first 2 rows are put into 1 chunk whereas the other 2 are put into a separate one.
However, the range_start and range_end from timescaledb_information.chunks are showing a cut-off time at 08:00:00+08 instead of 05:00:00+08 (GMT +8 is my postgres timezone) as shown below:
> select * from timescaledb_information.chunks where hypertable_name='sample_table';
hypertable_schema | hypertable_name | chunk_schema | chunk_name | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compr
essed | chunk_tablespace | data_nodes | chunk_creation_time
-------------------+-----------------+-----------------------+---------------------+-------------------+--------------------------+------------------------+------------------------+---------------------+-------------------+---------
------+------------------+------------+-------------------------------
public | sample_table | _timescaledb_internal | _hyper_81_161_chunk | time | timestamp with time zone | 2024-01-12 08:00:00+08 | 2024-01-13 08:00:00+08 | | | f
| | | 2024-01-19 14:36:13.859024+08
public | sample_table | _timescaledb_internal | _hyper_81_162_chunk | time | timestamp with time zone | 2024-01-13 08:00:00+08 | 2024-01-14 08:00:00+08 | | | f
| | | 2024-01-19 14:36:13.864631+08
(2 rows)
Query seems to work fine, however. Will there be any other side effect? Thank you.
Hi @asiayeah , thanks for sharing! I don’t see any other side effect, let us know if you see anything else.
If you judge it’s not working correctly, please open an issue with your reproducible example and bring the issue with the range. Maybe something can be inconsistent. Also, by default you’re not casting your time to any timezone, can you try to just insert data on your timezone to see if it will behave as expected?