Basic timestamp concepts
Local date and time
A local date and time refers to the local time in a specific region. It is usually represented as a string in the format yyyy-MM-dd hh:mm:ss.SSS and contains no timezone information. Example: 2021-07-21 12:00:00.000.
Timezone
A timezone is the standard time for a geographic location on Earth. UTC (Coordinated Universal Time) is the international time standard. Other time zones are expressed as offsets from UTC, such as UTC+8, UTC-5, and UTC+0.
UTC timestamp
A UTC timestamp represents the number of milliseconds elapsed since the UNIX epoch (00:00:00 UTC on January 1, 1970). Example: the timestamp 1700000000000 maps to 2023-11-14 22:13:20 (UTC+0). When TDengine stores time-series data, it saves the data as UTC timestamps internally.
Timestamp handling during writes
RFC 3339 format
When using RFC 3339 format, TDengine correctly parses time strings that include timezone information into UTC timestamps. SQL example:
INSERT INTO d1001 VALUES ("2018-10-03T14:38:05.000+08:00", 10.3, 219, 0.31);
Non-RFC 3339 format
If a time string does not contain timezone information, TDengine uses the application’s timezone setting to convert the time to a UTC timestamp automatically. SQL example:
INSERT INTO d1001 VALUES ("2018-10-03 14:38:05", 10.3, 219, 0.31);
Numeric timestamps
You can use a numeric timestamp directly. Numeric timestamps are stored as UTC timestamps with no conversion needed. SQL example:
INSERT INTO d1001 VALUES (1538548685000, 10.3, 219, 0.31);
Timestamp display during queries
Automatic timezone conversion
When querying data, the TDengine client automatically converts stored UTC timestamps to local time based on the application’s current timezone setting.
Example
Suppose the database stores timestamp 1538548685000. When queried with the UTC+8 timezone, it displays as 2018-10-03 14:38:05. With the UTC+0 timezone, it displays as 2018-10-03 06:38:05. With the UTC-5 timezone (New York), it displays as 2018-10-03 01:38:05.
Time precision settings
Database-level precision
CREATE DATABASE power PRECISION 'ms' KEEP 3650 DURATION 10 BUFFER 16;
PRECISION 'ms' sets millisecond precision.
Supported precision levels
ms(milliseconds): 10^-3 secondsus(microseconds): 10^-6 secondsns(nanoseconds): 10^-9 seconds
Choosing the right precision
Millisecond precision works for most IoT scenarios. Microsecond precision suits cases that need higher time resolution, such as financial trading. Nanosecond precision fits scientific experiments, high-precision measurement, and similar use cases.
Time functions
The NOW function
NOW returns the current time as a UTC timestamp. SQL example:
INSERT INTO d1001 VALUES (NOW, 10.3, 219, 0.31);
Time arithmetic
SELECT * FROM meters WHERE ts >= NOW - 1h;
SELECT * FROM meters WHERE ts >= '2022-01-01 00:00:00' AND ts < '2022-01-01 01:00:00';
Timezone configuration best practices
Consistent timezone settings
When using INTERVAL statements, set the client and server timezone parameters to the same value. This avoids the performance overhead of cross-timezone conversions.
Configuration methods
On the server side, set the timezone in taos.cfg:
timezone UTC+8
On the client side, specify the timezone during connection or in the connection string.
Cross-timezone applications
Follow these principles: store data using UTC timestamps uniformly at the storage layer, convert to the user’s timezone at the display layer, and include explicit timezone information at the query layer.
Time formats in detail
Supported formats
String formats: "2018-10-03 14:38:05", "2018-10-03T14:38:05.000+08:00". Numeric format: 1538548685000.
Advantages of RFC 3339 format
RFC 3339 format includes timezone information explicitly, removes timezone ambiguity, follows an international standard, and simplifies cross-timezone data processing.
Common problems and solutions
Problem 1: Incorrect time display. Cause: inconsistent timezone settings between client and server. Solution: configure the same timezone on both sides.
Problem 2: Time range queries miss data. Cause: timezone conversion causes boundary issues. Solution: use RFC 3339 format to specify the timezone explicitly.
Problem 3: Time precision loss. Cause: database precision setting does not match the data. Solution: choose the right precision when creating the database.
Timestamp handling best practices
Storage guidelines: store data using UTC timestamps uniformly, select the right time precision, and avoid storing local time strings directly.
Query guidelines: use RFC 3339 format to specify time ranges, include timezone information explicitly, and use time functions to simplify queries.
Application development guidelines: use UTC time throughout application logic, convert timezones only at the presentation layer, and avoid mixing different timezones in business logic.
Timestamps and window queries
Timestamps are central to window queries. SQL example:
SELECT tbname, _wstart, _wend, avg(voltage)
FROM meters
WHERE ts >= "2022-01-01T00:00:00+08:00"
AND ts < "2022-01-01T00:05:00+08:00"
PARTITION BY tbname INTERVAL(1m, 5s);
Pseudo-columns in window queries: _wstart (window start time), _wend (window end time), _wduration (window duration).
Summary
TDengine handles timestamps through UTC storage, automatic timezone conversion, and support for multiple precision levels. Understanding timestamp behavior is essential when building cross-timezone IoT and industrial data platforms.
