关于TimescaleDB支持的时序数据类型及UNIX时间戳兼容性的咨询

Hey there! As someone who's been using TimescaleDB for a while, I'm glad you're digging into the details of its time column support. Let's break this down clearly:

First, it's important to remember that TimescaleDB is a PostgreSQL extension, so it inherits all of PostgreSQL's native date and time types as valid time columns for hypertables (its core time-series optimized tables). These include:

• TIMESTAMP (timestamp without timezone)
• TIMESTAMPTZ (timestamp with timezone — this is the recommended default for most use cases, as it handles timezone consistency seamlessly)
• DATE
• TIME
• TIMETZ

Key Question: Does TimescaleDB support UNIX timestamps as time series data? #

Absolutely! You can use integer types to store UNIX timestamps (whether in seconds, milliseconds, microseconds, or even nanoseconds) and use that column as the time partition key for your hypertable.

A few best practices here:

• Use BIGINT instead of INT to avoid overflow (UNIX timestamps in milliseconds will exceed the 32-bit integer limit by 2038, and even second-level timestamps will hit it eventually)
• Be consistent with your timestamp granularity (stick to seconds, ms, or us across your data to avoid confusion)

Example: Creating a hypertable with a UNIX timestamp (millisecond) column

-- Create a raw table with BIGINT time column
CREATE TABLE iot_sensor_readings (
    time_ms BIGINT NOT NULL,
    sensor_uuid UUID,
    humidity DECIMAL(5,2),
    pressure DECIMAL(6,2)
);

-- Convert to a hypertable, partitioning by the UNIX timestamp column
SELECT create_hypertable('iot_sensor_readings', 'time_ms', chunk_time_interval => 86400000);
-- Chunk interval set to 1 day (86400000 milliseconds)

While using integer-based UNIX timestamps works perfectly, keep in mind that using TIMESTAMPTZ offers better readability and full access to PostgreSQL's rich date/time functions (like date_trunc(), age(), or now()). If you need to convert between formats, you can use these handy functions:

-- Convert a second-level UNIX timestamp (BIGINT) to TIMESTAMPTZ
SELECT to_timestamp(1718000000) AT TIME ZONE 'UTC';

-- Convert TIMESTAMPTZ to a millisecond-level UNIX timestamp
SELECT (extract(epoch FROM now()) * 1000)::BIGINT;

To wrap up: TimescaleDB fully supports both PostgreSQL's native time types and integer-stored UNIX timestamps as valid time columns for time-series workloads. You can confidently use UNIX timestamps as your time dimension in hypertables.

内容的提问来源于stack exchange，提问作者Zach