@Rocky i'm struggling with datetime precision in starrocks, this seems like it should work but I don't see milliseconds or microseconds

SELECT *, str_to_date(m.received_at, '%Y-%m-%dT%H:%i:%s.%fZ'),str_to_date(m.received_at, '%Y-%m-%dT%H:%i:%s.%fZ')
from(
SELECT CAST(json_query(j.value, '$.id') AS bigINT) AS id, json_query(j.value->'properties', 'received_at') AS received_at
FROM events.blah
JOIN json_each(t.features) j
) m

I can see what's happening! There are three main reasons why you might be losing that sub-second precision in StarRocks: 1. Version Requirement Sub-second precision (milliseconds and microseconds) for the

DATETIME

data type was officially introduced in StarRocks v3.3.5. * If you are on a version earlier than 3.3.5: The

DATETIME

type only stores values accurate to the second. Even if you parse them correctly, the fractional part is discarded when stored or cast to

DATETIME

. * If you are on 3.3.5 or later: You can use

DATETIME(3)

or

DATETIME(6)

to preserve and display that precision. 2. JSON Extraction (

json_query

vs

->>

) You are using

json_query

to extract

received_at

. In StarRocks,

json_query

returns a JSON fragment, which means a string value will still contain double quotes (e.g.,

"2024-05-15T..."

). The

str_to_date

function will likely fail or return NULL if it encounters unexpected quotes. * Fix: Use the

->>

operator or

get_json_string()

to extract the raw string without quotes. 3. Using

str_to_jodatime

For ISO8601 strings (the ones with

T

and

Z

),

str_to_jodatime

is often more reliable than

str_to_date

. It uses Joda-style formatting where

SSS

handles milliseconds. Recommended Fix Try rewriting your query like this (assuming you are on v3.3.5+):

sql
SELECT 
    m.id,
    m.received_at_raw,
    -- Use str_to_jodatime for better ISO8601 support
    -- Cast to DATETIME(6) to ensure the output displays microseconds
    CAST(str_to_jodatime(m.received_at_raw, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") AS DATETIME(6)) AS received_at_dt
FROM (
    SELECT 
        CAST(j.value->'id' AS BIGINT) AS id, 
        j.value->'properties'->>'received_at' AS received_at_raw -- Use ->> to get string without quotes
    FROM events.blah,
    LATERAL json_each(features) j
) m

Quick Tips: * Literal Characters: In

str_to_jodatime

, literal characters like

T

and

Z

must be enclosed in single quotes within the format string:

"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"

. * Check Precision: Even on newer versions, some SQL clients might truncate the display. You can verify the precision is there by running

SELECT microsecond(your_datetime_column)

. * The

%f

format: In

str_to_date

,

%f

represents microseconds (6 digits). If your input only has 3 digits (milliseconds),

str_to_date

can sometimes be picky.

str_to_jodatime

is generally more flexible for varying fractional lengths. References • sql-reference/sql-functions/date-time-functions/to_tera_timestamp.md • sql-reference/sql-functions/date-time-functions/jodatime_format.md • sql-reference/sql-functions/date-time-functions/str_to_date.md • sql-reference/sql-functions/date-time-functions/date_trunc.md