DuckDB TsFile Extension

This guide is intended for data engineers, platform developers, and analytics developers who need to analyze or generate Apache TsFile data in DuckDB. The TsFile extension is available as a DuckDB Community Extension. It makes TsFile both a SQL data source and a COPY output format, providing a complete read-query-write workflow.

Remember two things: use read_tsfile(path, table_name) to read data, and use COPY ... TO ... (FORMAT tsfile) to write data. The current version supports local, Table-model TsFiles. Before writing, sort the data by the TAG columns and then by the time column.

Quick Start

Start DuckDB and Load the Extension

For regular use, install and load the DuckDB Community Extension:

INSTALL tsfile FROM community;

LOAD tsfile;

Install the Community Extension the first time you use it. After that, you only need to run LOAD tsfile; whenever you start DuckDB.

Read Your First TsFile

SELECT *
FROM read_tsfile('/data/measurements.tsfile', 'sensors');

The first argument to read_tsfile is a local file path, and the second is the name of the table stored in the TsFile. Each call reads one Table-model table from one file.

Reading and Querying

Select Columns

Select the columns you need just as you would from a regular DuckDB table. Projection is pushed down to the TsFile scan whenever possible, reducing the amount of data read:

SELECT time, device_id, temperature
FROM read_tsfile('/data/measurements.tsfile', 'sensors')
LIMIT 10;

Filter by Time

The global TsFile time axis is exposed in DuckDB as a BIGINT. The time unit is determined by the file or protocol; milliseconds are common. The following operators can be pushed down to the scanner: =, <, <=, >, >=, and BETWEEN.

SELECT time, device_id, temperature
FROM read_tsfile('/data/measurements.tsfile', 'sensors')
WHERE time BETWEEN 1700000000000 AND 1700003600000;

Filter by TAG

String TAG columns support equality and range comparisons, null checks, and combinations using AND and OR. Supported operations include =, !=, <, <=, >, >=, inclusive BETWEEN, IS NULL, and IS NOT NULL.

SELECT time, device_id, temperature
FROM read_tsfile('/data/measurements.tsfile', 'sensors')
WHERE (device_id = 'device-01' OR device_id = 'device-02')
  AND time BETWEEN 1700000000000 AND 1700003600000;

FIELD filters and TAG expressions that cannot be pushed down are still evaluated by DuckDB after the scan. A NOT (...) expression in any form is not pushed down.

Check Filter Pushdown

Use EXPLAIN to inspect the query plan. If pushdown succeeds, the READ_TSFILE node shows Time Range and TAG Filter:

EXPLAIN
SELECT time, device_id, temperature
FROM read_tsfile('/data/measurements.tsfile', 'sensors')
WHERE device_id = 'device-01'
  AND time >= 1700000000000;

Writing TsFile

Write with COPY

Writing uses DuckDB's standard COPY interface. Clean, cast, and sort the data in a subquery before writing it to TsFile:

COPY (
    SELECT time, device_id, temperature, humidity
    FROM measurements
    ORDER BY device_id, time
)
TO '/data/measurements.tsfile'
(
    FORMAT tsfile,
    TABLE_NAME sensors,
    TIME_COLUMN time,
    TAG_COLUMNS (device_id)
);

TIME_COLUMN identifies the time axis. Columns listed in TAG_COLUMNS are written as TAG columns, and all other columns are written as FIELD columns. Identifier syntax is recommended for TABLE_NAME and TIME_COLUMN; use double quotes when an identifier contains special characters.

Write Options

Option	Required	Description
FORMAT tsfile	Yes	Selects the TsFile COPY writer.
TABLE_NAME	No	The local table name in the output file. The default is default_table.
TIME_COLUMN	No	The input column used as the time axis. The default is time, and its type must be BIGINT.
TAG_COLUMNS	No	A list of TAG column names. If omitted, all non-time columns are written as FIELD columns.
OVERWRITE true	No	Use this option to replace an existing target. Keep the default temporary-file handling enabled.

TAG_COLUMNS Constraints

TAG_COLUMNS accepts a list of column names, not a list of values:

TAG_COLUMNS (device_id, region)

• Each column must exist in the input query.

• Each column must have the VARCHAR type.

• A TAG column cannot also be the TIME_COLUMN.

• Column-name matching is case-insensitive.

If you do not need TAG columns, you can write only the time and FIELD columns:

COPY (
    SELECT time, temperature, humidity
    FROM measurements
    ORDER BY time
)
TO '/data/field-only.tsfile'
(
    FORMAT tsfile,
    TABLE_NAME sensors,
    TIME_COLUMN time
);

Type Mapping and NULL Values

DuckDB Type	TsFile Type	Notes
BOOLEAN	BOOLEAN	FIELD NULL values are preserved.
INTEGER	INT32	FIELD NULL values are preserved.
BIGINT	INT64	FIELD NULL values are preserved; this type is also used for the time axis.
FLOAT	FLOAT	FIELD NULL values are preserved.
DOUBLE	DOUBLE	FIELD NULL values are preserved.
VARCHAR	STRING	FIELD NULL values are preserved.
BLOB	BLOB	FIELD NULL values are preserved.
TIMESTAMP_NS	TIMESTAMP	Read as DuckDB TIMESTAMP_NS.

End-to-End Example

The following workflow reads a time range for device a from an existing TsFile, renames columns, writes a new TsFile, and then reads it back for verification:

LOAD tsfile;

COPY (
    SELECT time,
           s0 AS device_id,
           s2 AS value,
           CAST(s8 AS VARCHAR) AS day
    FROM read_tsfile('test/data/simple_table_t1.tsfile', 'test')
    WHERE s0 = 'a'
      AND time BETWEEN 1760106022000 AND 1760106024000
    ORDER BY device_id, time
)
TO '/tmp/tsfile_subset.tsfile'
(
    FORMAT tsfile,
    TABLE_NAME subset,
    TIME_COLUMN time,
    TAG_COLUMNS (device_id)
);

SELECT time, device_id, value, day
FROM read_tsfile('/tmp/tsfile_subset.tsfile', 'subset')
ORDER BY device_id, time;

The query returns three rows with timestamps 1760106022000, 1760106023000, and 1760106024000.

Current Limitations and Troubleshooting

Pre-Write Checklist

1. Confirm that TIME_COLUMN exists and has the BIGINT type.

2. Confirm that the TIME column contains no NULL values.

3. Confirm that the input is sorted by all TAG columns and then by the time column.

4. If the target file already exists, keep temporary-file handling enabled and set OVERWRITE true.

Known Limitations

• The current version supports one Table-model table in one local TsFile.

• FIELD filters are not pushed down. A NOT (...) TAG expression in any form is not pushed down.

• Writing DATE FIELD columns is temporarily disabled to avoid conversions that depend on the local time zone. DATE columns in existing TsFiles can still be read. To write such a column, first cast it to VARCHAR.

• When writing directly with USE_TMP_FILE false, the target path must not already exist.

Quick Reference

Task	Entry Point
Read TsFile	read_tsfile('/path/file.tsfile', 'table')
Inspect columns and data	SELECT ... FROM read_tsfile(...)
Push down time and TAG conditions	Use supported comparisons and logical combinations in WHERE
Inspect pushdown	EXPLAIN SELECT ...
Write TsFile	COPY (...) TO 'file.tsfile' (FORMAT tsfile, ...)
Replace an existing file	Default temporary-file handling + OVERWRITE true