CREATE EXTENSION IF NOT EXISTS timescaledb;

-- All time values are in milliseconds

-- Create the hypertable to enable TimescaleDB on the sqlth_1_data table, store data in chunks of 1 day, and migrate existing data
SELECT * FROM create_hypertable('sqlth_1_data','t_stamp', if_not_exists => True, chunk_time_interval => 86400000, migrate_data => True);

-- Define how the data is stored by timestamp and how to group related tag data
ALTER TABLE sqlth_1_data SET (timescaledb.compress, timescaledb.compress_orderby = 't_stamp DESC', timescaledb.compress_segmentby = 'tagid'); 

-- Configure the unix_now() function to return epoch time in milliseconds for the sqlth_1_data table
CREATE OR REPLACE FUNCTION unix_now() returns BIGINT LANGUAGE SQL STABLE as $$ SELECT (extract(epoch from now())*1000)::bigint $$;
SELECT set_integer_now_func('sqlth_1_data', 'unix_now');

-- Add a compression policy to compress data after 7 days (1 week)
SELECT add_compression_policy('sqlth_1_data', 604800000);

-- Remove comment from another retention policy and add comment to the 10 year policy if a shorter retention policy is wanted (or modify integer value appropriately)
-- All time values are in milliseconds

-- Purge data after 10 years
SELECT add_retention_policy('sqlth_1_data', 315360000000);

-- Purge data after 1 year
-- SELECT add_retention_policy('sqlth_1_data', 31536000000);

-- Purge data after 2 years
-- SELECT add_retention_policy('sqlth_1_data', 3072000000);

-- Purge data after 5 years
-- SELECT add_retention_policy('sqlth_1_data', 157680000000);