http://starrocks.io logo
Join Slack
Powered by
``` -> `ingestion_timestamp` datetime(3) N...
# questions-and-troubleshooting
k
Copy code
->   `ingestion_timestamp` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ""
    -> ) ENGINE=OLAP
    -> DUPLICATE KEY(`event_type`, `service`, `event_time`)
    -> PARTITION BY date_trunc('day', `timestamp_dt`)
    -> DISTRIBUTED BY HASH(`entity_id`) BUCKETS 8
    -> PROPERTIES (
    ->   "compression" = "LZ4",
    ->   "fast_schema_evolution" = "true",
    ->   "partition_retention_condition" = "timestamp_dt >= CURRENT_DATE - INTERVAL 1 DAY AND timestamp_dt < CURRENT_DATE + INTERVAL 2 DAY",
    ->   "replicated_storage" = "true",
    ->   "replication_num" = "2"
    -> );
ERROR 1064 (HY000): Getting syntax error at line 13, column 26. Detail message: Unexpected input '3', the most similar input is {'ARRAY', 'BIGINT', 'BINARY', 'BITMAP', 'BOOLEAN'}.
StarRocks >
StarRocks >
@Rocky doesn't starrocks supports datetime to store milliseconds??
r
Yes, StarRocks does support millisecond and microsecond precision for the 
DATETIME
type, but this is a relatively new feature. The error you are seeing (
Unexpected input '3'
) indicates that your StarRocks version does not recognize the scale parameter in the 
DATETIME(3)
definition. 1. Version Requirements To use 
DATETIME(3)
or 
DATETIME(6)
, you must be on StarRocks version 3.3.5 or later. * Before v3.3.5: The 
DATETIME
type only supports second precision (e.g., 
YYYY-MM-DD HH:MM:SS
). Any attempt to add a scale like 
(3)
will result in a syntax error. * v3.3.5 and later: You can define columns as 
DATETIME(3)
for milliseconds or 
DATETIME(6)
for microseconds. 2. Another Potential Issue: 
partition_retention_condition
 In your SQL snippet, you are also using the property: 
"partition_retention_condition" = "..."
Please note that this property (Common Partition Expression TTL) was introduced in StarRocks v3.5.0. If your version is older than 3.5.0, you will encounter another error for this property even after fixing the 
DATETIME
syntax. How to fix this: Option A: Upgrade StarRocks (Recommended) If you need millisecond precision and the automated partition retention condition, you should upgrade your cluster to at least v3.5.x. Option B: Workaround for Older Versions (< 3.3.5) If you cannot upgrade immediately, you have two choices for storing milliseconds: 1. Use 
BIGINT
: Store the epoch timestamp in milliseconds (e.g., 
1713349400000
). 2. Use 
VARCHAR
: Store the time as a string. And change your DDL to use plain `DATETIME`:
Copy code
sql
`ingestion_timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
Option C: Fix for v3.3.5+ (but < 3.5.0) If you are on a version like 3.3.7, the 
DATETIME(3)
syntax will work, but the 
partition_retention_condition
property will not. You would need to remove that property and use Dynamic Partitioning (
dynamic_partition.history_partition_num
) instead for lifecycle management. To check your current version, run:
Copy code
sql
SELECT VERSION();
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 • sql-reference/sql-functions/date-time-functions/date_trunc.md
k
@Rocky
Copy code
StarRocks > SELECT current_version();
+-------------------+
| current_version() |
+-------------------+
| 3.5.7-d2da301     |
+-------------------+
1 row in set (0.01 sec)
r
Yes, StarRocks does support millisecond and microsecond precision for the 
DATETIME
type starting from v3.3.5, but the syntax you are using is slightly incorrect for StarRocks. The error 
Unexpected input '3'
occurs because, unlike MySQL, StarRocks does not use the 
DATETIME(p)
syntax in the table definition (the 
CREATE TABLE
statement). 1. The Fix In StarRocks 3.3.5 and later (including your version 3.5.7), the 
DATETIME
type has been upgraded to support up to 6 digits of fractional seconds (microsecond precision) automatically. You do not need to specify the precision in the column type definition. Simply change 
datetime(3)
to `datetime`:
Copy code
sql
-- Change this:
`ingestion_timestamp` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP

-- To this:
`ingestion_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
2. How to use precision Even though the column is defined as 
DATETIME
, StarRocks will store the fractional seconds if provided. * During Insertion: If you insert a value like 
'2024-01-01 12:00:00.123'
, it will be stored with millisecond precision. * Default Values: If you want the default value to specifically capture milliseconds or microseconds, you can pass the precision to the function:
Copy code
sql
    `ingestion_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
* Querying: You will see the fractional parts in your query results (e.g., 
2024-05-20 10:00:00.123000
). 3. Verification Your version 3.5.7 fully supports this. Once you remove the 
(3)
from the type definition, your 
CREATE TABLE
statement should work perfectly, including the 
partition_retention_condition
(TTL) which was introduced in v3.5.0. Corrected DDL Snippet:
Copy code
sql
CREATE TABLE your_table (
    ...
    `ingestion_timestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`event_type`, `service`, `event_time`)
PARTITION BY date_trunc('day', `timestamp_dt`)
DISTRIBUTED BY HASH(`entity_id`) BUCKETS 8
PROPERTIES (
  "compression" = "LZ4",
  "fast_schema_evolution" = "true",
  "partition_retention_condition" = "timestamp_dt >= CURRENT_DATE - INTERVAL 1 DAY AND timestamp_dt < CURRENT_DATE + INTERVAL 2 DAY",
  "replicated_storage" = "true",
  "replication_num" = "2"
);
Note: If you specifically need to truncate or handle millisecond data in queries, you can continue using functions like 
date_trunc('millisecond', column)
. References • sql-reference/data-types/date-types/DATETIME.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/sql-functions/date-time-functions/current_timestamp.md • sql-reference/data-types/date-types/DATE.md
✅ 1
Open in Slack
PreviousNext
Powered by