http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> How do I store a `DATETIME` with mi...
# questions-and-troubleshooting
d
@Rocky How do I store a 
DATETIME
with millisecond precision?
r
To store a 
DATETIME
with millisecond precision in StarRocks, you can use the fractional seconds precision syntax. This feature is fully supported starting from StarRocks version 3.3.5. 1. Column Definition To define a column with millisecond precision (3 digits after the second), use 
DATETIME(3)
in your 
CREATE TABLE
statement:
Copy code
sql
CREATE TABLE example_table (
    id INT,
    event_time DATETIME(3) -- Stores time with 3-digit millisecond precision
) 
ENGINE=OLAP
DUPLICATE KEY(id)
DISTRIBUTED BY HASH(id);
* DATETIME(3): Accurate to milliseconds (e.g., 
2024-05-01 10:00:00.123
). * DATETIME(6): Accurate to microseconds (e.g., 
2024-05-01 10:00:00.123456
). * DATETIME: Defaults to second precision if no parameter is provided. 2. Inserting Data You can insert data using strings with fractional seconds or functions that support precision:
Copy code
sql
-- Inserting a string literal
INSERT INTO example_table VALUES (1, '2024-05-01 12:30:45.123');

-- Using the now() function with precision (since v3.1.6)
INSERT INTO example_table VALUES (2, NOW(3));
3. Key Functions for Precision Several functions allow you to work with high-precision time: * 
NOW(p)
: Returns the current time with precision 
p
(1-6). For milliseconds, use 
NOW(3)
. * 
CURRENT_TIMESTAMP(p)
: Synonym for 
NOW(p)
. * 
DATE_TRUNC(fmt, datetime)
: You can truncate to 
millisecond
or 
microsecond
(since v3.1.7).
Copy code
sql
    SELECT DATE_TRUNC('millisecond', NOW(6));
Summary of Version Support * v3.1.6+: 
NOW(p)
and 
CURRENT_TIMESTAMP(p)
functions support precision. * v3.1.7+: 
DATE_TRUNC
and 
TIME_SLICE
support 
millisecond
and 
microsecond
units. * v3.3.5+: Full support for storing millisecond/microsecond precision in 
DATETIME
table columns. References • sql-reference/data-types/date-types/DATETIME.md • release_notes/release-3.3.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/monitoring/metrics.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/data-types/date-types/DATE.md • sql-reference/sql-functions/date-time-functions/now.md
d
@Rocky When i create this table I get an error CREATE TABLE IF NOT EXISTS 
test
( -> 
live_code
VARCHAR(255) NOT NULL, -> 
tenor_name
VARCHAR(255) NOT NULL, -> 
generated_on
DATETIME(3) NOT NULL, -> 
tenor_code
VARCHAR(255) NOT NULL, -> 
tenor_type
VARCHAR(255), -> 
price
DECIMAL(38, 16), -> 
factors
JSON, -> 
visible
BOOLEAN, -> 
processed_by
VARCHAR(255), -> 
processed_on
DATETIME(3), -> 
metadata
JSON -> ) -> DUPLICATE KEY ( -> 
live_code
, -> 
tenor_name
, -> 
generated_on
-> ) -> PARTITION BY date_trunc('day', 
generated_on
) -> DISTRIBUTED BY HASH(
live_code
) BUCKETS 10 -> ORDER BY (
live_code
, 
tenor_name
) -> PROPERTIES ("replication_num" = "1"); ERROR 1064 (HY000): Getting syntax error at line 4, column 37. Detail message: Unexpected input '3', the most similar input is {'ARRAY', 'BIGINT', 'BINARY', 'BITMAP', 'BOOLEAN'}.
r
The error you're seeing, 
Unexpected input '3'
, indicates that the StarRocks version you are currently running does not support the fractional seconds precision syntax for the 
DATETIME
data type. Why this is happening * Version Requirement: Support for storing fractional seconds (e.g., 
DATETIME(3)
or 
DATETIME(6)
) in table columns was introduced in StarRocks version 3.3.0 and stabilized in 3.3.5. * Older Versions: In versions prior to 3.3 (such as 2.5, 3.1, or 3.2), the 
DATETIME
type is fixed to second-level precision and does not accept a precision parameter in parentheses. How to resolve this Option 1: Upgrade to StarRocks 3.3+ (Recommended) If you need native millisecond support and precision in your 
DATETIME
columns, upgrading to the latest 3.3.x release is the best solution. This will allow your current SQL to work exactly as written. Option 2: Workaround for Older Versions If you cannot upgrade immediately, you can use one of these common workarounds to store millisecond data: 1. Store as BIGINT (Unix Timestamp): Store the time as a Unix timestamp in milliseconds.
Copy code
sql
    `generated_on` BIGINT NOT NULL -- e.g., 1714561245123
Pros: High performance, supports exact precision. Cons: Requires conversion using 
FROM_UNIXTIME
or 
to_datetime(ts, 3)
for readability. 2. Use two columns: Store the second-level precision in a 
DATETIME
column and the remaining milliseconds in an 
INT
column.
Copy code
sql
    `generated_on` DATETIME NOT NULL,
    `generated_on_ms` INT NOT NULL
3. Store as VARCHAR: Store the timestamp as a string (e.g., 
"2024-05-01 12:00:00.123"
). Pros: Readable. Cons: Slower for range queries and occupies more disk space. Summary of Version Capabilities Feature | v3.1.x | v3.2.x | v3.3.x+ 
NOW(3)
function | ✅ Supported | ✅ Supported | ✅ Supported 
DATE_TRUNC
for ms | ✅ (3.1.7+) | ✅ Supported | ✅ Supported 
DATETIME(3)
column | ❌ No | ❌ No | ✅ Supported To check your current version, run:
Copy code
sql
SELECT aurora_version(), version();
References • sql-reference/data-types/date-types/DATETIME.md • sql-reference/sql-functions/date-time-functions/now.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/sql-functions/date-time-functions/to_datetime.md • sql-reference/sql-functions/date-time-functions/unix_timestamp.md • release_notes/release-3.3.md • sql-reference/sql-functions/date-time-functions/date_trunc.md
d
@Rocky I am using starrocks 4.1.0
r
In StarRocks version 3.3.5 and later (including your version 4.1.0), the 
DATETIME
data type was upgraded to support millisecond and microsecond precision automatically. However, unlike some other databases, StarRocks does not use the 
DATETIME(precision)
syntax in the 
CREATE TABLE
column definition. The error you are seeing occurs because the parser recognizes 
DATETIME
as a complete type and does not expect the parenthesized precision 
(3)
. The Fix To resolve the syntax error, simply remove the 
(3)
from your column definitions. The 
DATETIME
type in version 4.1.0 already has the internal capacity to store up to microsecond precision (6 digits). Here is the corrected DDL:
Copy code
sql
CREATE TABLE IF NOT EXISTS `test` (
    `live_code`     VARCHAR(255)   NOT NULL,
    `tenor_name`    VARCHAR(255)   NOT NULL,
    `generated_on`  DATETIME       NOT NULL, -- Remove (3)
    `tenor_code`    VARCHAR(255)   NOT NULL,
    `tenor_type`    VARCHAR(255),
    `price`         DECIMAL(38, 16),
    `factors`       JSON,
    `visible`       BOOLEAN,
    `processed_by`  VARCHAR(255),
    `processed_on`  DATETIME,                 -- Remove (3)
    `metadata`      JSON
)
DUPLICATE KEY (
    `live_code`,
    `tenor_name`,
    `generated_on`
)
PARTITION BY date_trunc('day', `generated_on`)
DISTRIBUTED BY HASH(`live_code`) BUCKETS 10
ORDER BY (`live_code`, `tenor_name`)
PROPERTIES ("replication_num" = "1");
How to manage precision in 4.1.0 Even though you define the column as just 
DATETIME
, you can still control and store millisecond precision: 1. Inserting Data: When you insert a string with milliseconds (e.g., 
'2024-05-01 12:00:00.123'
) or use a high-precision function, StarRocks will store the fractional parts. 2. Using Functions: Use functions that specify the precision you want: * `NOW(3)`: Returns the current time with 3-digit millisecond precision. * `to_datetime(timestamp_ms, 3)`: Converts a millisecond Unix timestamp to a 
DATETIME
value. 3. Truncating: If you want to ensure data is always stored at exactly millisecond precision, you can use the 
date_trunc
function during insertion:
Copy code
sql
    INSERT INTO test (generated_on, ...)
    VALUES (date_trunc('millisecond', NOW(6)), ...);
4. Querying: By default, StarRocks will display fractional seconds in the format 
YYYY-MM-DD HH:MM:SS.ffffff
. If you only want to see 3 digits, you can use 
date_format(generated_on, '%Y-%m-%d %H:%i:%s.%f')
or similar formatting in your 
SELECT
SELECT` during your 
SELECT
. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/date-types/DATETIME.md • release_notes/release-3.3.md
d
@Rocky I am writing to starrocks from flink, could starrocks/flink be truncating my timestamps?
r
Yes, it is very likely that the timestamps are being truncated during the transfer from Flink to StarRocks. Since you are using StarRocks 4.1.0, the database itself is fully capable of storing millisecond precision in a 
DATETIME
column, so the bottleneck is almost certainly in the Flink job or the connector configuration. Here are the three most common reasons for truncation in this setup and how to fix them: 1. Flink SQL Type Definition (Most Likely) In Flink SQL, the 
TIMESTAMP
type has a default precision. If you define your column as just 
TIMESTAMP
or 
TIMESTAMP(0)
, Flink will truncate the fractional seconds before they even reach the connector. * Fix: Ensure your Flink DDL uses 
TIMESTAMP(3)
(for milliseconds) or 
TIMESTAMP(6)
(for microseconds).
Copy code
sql
CREATE TABLE flink_sink (
    live_code STRING,
    generated_on TIMESTAMP(3), -- Explicitly set precision to 3
    ...
) WITH (
    'connector' = 'starrocks',
    ...
);
2. Connector Format (CSV vs. JSON) By default, the StarRocks Flink connector uses CSV format to perform Stream Loads. In many versions of the connector, the CSV serializer uses a standard date-time formatter (
yyyy-MM-dd HH:mm:ss
) that lacks fractional seconds. * Fix: Switch the sink format to JSON. The JSON serializer in the Flink connector typically preserves the high-precision ISO-8601 string format.
Copy code
sql
CREATE TABLE flink_sink (
    ...
) WITH (
    'connector' = 'starrocks',
    'sink.properties.format' = 'json', -- Change format to JSON
    'sink.properties.strip_outer_array' = 'true',
    ...
);
3. StarRocks Stream Load Column Mapping If you are using CSV and have manually specified 
sink.properties.columns
, StarRocks might be parsing the incoming string as a standard 
DATETIME
. In StarRocks 4.1.0, the 
DATETIME
type handles milliseconds automatically, but if the incoming CSV string is literally 
2024-01-01 12:00:00
, the milliseconds are already gone. You can verify what StarRocks is receiving by checking the Load Error Log (if any rows fail) or by temporarily changing the StarRocks column to 
VARCHAR
to see exactly what string Flink is sending. Recommended Checklist: 1. Verify Flink Type: Change your Flink SQL source and sink types to 
TIMESTAMP(3)
. 2. Switch to JSON: Add 
'sink.properties.format' = 'json'
to your Flink sink properties. 3. Check StarRocks Column: Ensure the StarRocks table column is simply 
DATETIME
(do not use 
DATETIME(3)
as established earlier). How to verify the fix: After making these changes, run a query in StarRocks using 
DATE_FORMAT
to see the stored precision:
Copy code
sql
SELECT generated_on, DATE_FORMAT(generated_on, '%Y-%m-%d %H:%i:%s.%f') FROM test LIMIT 5;
``` If you see 
.000000
, the data is still being truncated before it hits the database. If you see values like 
.123000
, the millisecond precision is being preserved. successfully preserved millisecond precision is being preserved. ## References - [loading/Flink-connector-starrocks.md](https://docs.starrocks.io/docs/loading/Flink-connector-starrocks) - [loading/Flink_cdc_load.md](https://docs.starrocks.io/docs/loading/Flink_cdc_load) - [faq/loading/Flink_connector_faq.md](https://docs.starrocks.io/docs/faq/loading/Flink_connector_faq) - [loading/Kafka-connector-starrocks.md](https://docs.starrocks.io/docs/loading/Kafka-connector-starrocks) - [loading/Spark-connector-starrocks.md](https://docs.starrocks.io/docs/loading/Spark-connector-starrocks)
2 Views
Open in Slack
PreviousNext
Powered by