http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> n StarRocks 3.3.x, when reading dat...
# questions-and-troubleshooting
r
@Rocky n StarRocks 3.3.x, when reading data from a Hive catalog and exporting it using INSERT INTO FILES in Parquet format, I would like to know if the starrocks applies any timezone adjustments or automatically converts the data to UTC if the timestamp property has no value. Additionally, I would like to know how the Iceberg catalog behaves under the exact same scenario.
r
In StarRocks 3.3.x, the behavior for timezone adjustments when using 
INSERT INTO FILES
to export data to Parquet format depends on how the source catalog (Hive or Iceberg) is read and the Parquet writer's semantics. 1. Hive Catalog Behavior * Reading: When StarRocks reads from a Hive catalog, it maps Hive's 
TIMESTAMP
(which is typically timezone-naive) to its internal 
DATETIME
type. This conversion is influenced by the session variable 
time_zone
 (default is 
Asia/Shanghai
). * Exporting (
INSERT INTO FILES
): For the unloading (export) part, StarRocks 3.3.x documentation notes that the session variable 
time_zone
is "To be supported" for the unloading process. This means that changing the session 
time_zone
before running 
INSERT INTO FILES
may not adjust the exported values relative to UTC during the write phase. * UTC Conversion: If the Parquet metadata property 
isAdjustedToUTC
is not explicitly set (or has no value), StarRocks generally follows Local Semantics (
isAdjustedToUTC = false
). It writes the year, month, day, and hour components exactly as they appear in StarRocks' 
DATETIME
field into the Parquet file without any automatic conversion to UTC. 2. Iceberg Catalog Behavior * Reading: Iceberg handles timestamps more strictly than Hive: * 
TIMESTAMP
(without timezone): Treated similarly to Hive; it is read as a naive local time. * 
TIMESTAMPTZ
(with timezone): Iceberg stores these values in UTC. When StarRocks reads a 
TIMESTAMPTZ
column, it automatically converts the UTC value to the current session's 
time_zone
to display/process it as a 
DATETIME
. * Exporting: The export behavior is the same as Hive. The result of your 
SELECT
query (which has already applied the session 
time_zone
offset during the read phase for 
TIMESTAMPTZ
) is what gets passed to the 
FILES()
writer. * Summary of behavior: In the exact same scenario as Hive, if the source is an Iceberg 
TIMESTAMPTZ
column, the data is adjusted to your session's timezone during the read. However, when writing to the target Parquet file via 
INSERT INTO FILES
, no additional UTC conversion is applied by the writer unless specifically configured, resulting in "local" time values in the output. Summary Table (StarRocks 3.3.x) Feature | Hive Catalog | Iceberg Catalog Read Logic | Interprets as 
DATETIME
based on session 
time_zone
. | 
TIMESTAMP
is naive; 
TIMESTAMPTZ
is converted from UTC to session 
time_zone
. Export Logic | Writes local components of 
DATETIME
. | Writes local components of 
DATETIME
. Session 
time_zone
support | Supported for reading. | Supported for reading. Unloading 
time_zone
 | To be supported (effectively uses machine/system time). | To be supported (effectively uses machine/system time). Parquet 
isAdjustedToUTC
 | Defaults to 
false
(Local Semantics). | Defaults to 
false
(Local Semantics). Important Recommendation If you need the exported data to be in UTC, you should explicitly use the 
convert_tz()
function in your 
SELECT
statement to adjust the time to UTC before it reaches the 
INSERT INTO FILES
command, for example:
Copy code
sql
INSERT INTO FILES(...)
SELECT convert_tz(timestamp_col, @@time_zone, '+00:00') FROM hive_catalog.db.table;
References • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-functions/table-functions/files.md • administration/management/FE_configuration.md • faq/loading/Broker_load_faq.md • loading/Spark-connector-starrocks.md
r
@Rocky is that possible put some timezone infomation when INSERT INTO FILES () for specific data column?
r
In StarRocks 3.3.x, it is not possible to specify timezone information (such as the 
isAdjustedToUTC
metadata flag) for a specific data column using the 
INSERT INTO FILES
syntax. The properties supported by the 
FILES()
table function for unloading are limited to file-level configurations and do not include column-level metadata overrides for timezones. Key Limitations in StarRocks 3.3.x * Session Variable `time_zone`: While you can set the session 
time_zone
for queries, the documentation for StarRocks 3.3.x specifically notes that support for this variable during the unloading process (writing to files) is "To be supported." This means the exporter usually writes the internal 
DATETIME
values as-is. * Parquet Metadata: StarRocks generally defaults to Local Semantics (
isAdjustedToUTC = false
) when exporting to Parquet. This writes the year, month, day, and hour components exactly as they appear in the StarRocks 
DATETIME
field. There is currently no parameter in the 
FILES()
function to force this to 
true
for specific columns. * Legacy Encoding: You can use 
"parquet.use_legacy_encoding" = "true"
to force 
INT96
encoding for 
DATETIME
columns, which some older tools (like Hive) interpret as UTC by convention, but this applies to all 
DATETIME
columns in the export, not a specific one. How to handle this (The Workaround) Since you cannot set metadata labels per column, you must transform the data values themselves in your 
SELECT
statement to ensure they represent the correct time in your target timezone (e.g., UTC). For Hive (Naive Timestamps) If you want to export a Hive column as if it were UTC:
Copy code
sql
INSERT INTO FILES(
    "path" = "<s3://my-bucket/export/>",
    "format" = "parquet"
)
SELECT 
    -- Manually convert the naive local time to UTC
    CONVERT_TZ(hive_timestamp_col, 'Asia/Shanghai', '+00:00') as utc_timestamp 
FROM hive_catalog.db.table;
For Iceberg (TIMESTAMPTZ) Iceberg 
TIMESTAMPTZ
values are already stored in UTC. When StarRocks reads them, it converts them to your session's 
time_zone
. To export them as UTC values into a file:
Copy code
sql
INSERT INTO FILES(...)
SELECT 
    -- Convert it back to UTC before the writer sees it
    CONVERT_TZ(iceberg_timestamptz_col, @@time_zone, '+00:00') 
FROM iceberg_catalog.db.table;
Summary of Behavior Feature | Supported in 3.3.x? Column-specific timezone metadata | No Session 
time_zone
for export | To be supported (Experimental/Partial) Data transformation via SQL | Yes (Recommended) Parquet 
isAdjustedToUTC
toggle | No (Defaults to 
false
) If you need the Parquet file to explicitly contain 
isAdjustedToUTC=true
in its metadata so that downstream tools (like Spark) auto-convert it, StarRocks 3.3.x does not yet provide a way to trigger this flag via 
INSERT INTO FILES
. References • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md • sql-reference/sql-statements/loading_unloading/INSERT.md
r
@Rocky how to give TimeZone attribute when save parquet file using function (INSERT INTO FILES). for example I can Check datetime64 of dtype with UTC(datetime64[us,UTC] when i used 3.3.x. but there is only datetime64 on 3.5.x ups
r
In StarRocks 3.5.x, the behavior for Parquet exports using 
INSERT INTO FILES
has shifted toward Local Semantics by default, which is why you see 
datetime64
(naive) instead of 
datetime64[us, UTC]
. To restore the timezone attribute and ensure the resulting Parquet file contains the 
isAdjustedToUTC
flag, you can use specific properties in the 
FILES()
table function. 1. How to add the TimeZone attribute (isAdjustedToUTC) In StarRocks 3.4.6 and 3.5.x, you can control the Parquet timestamp metadata using the following property in your 
FILES()
function: * 
"parquet.is_adjusted_to_utc" = "true"
: This enables Instant Semantics. It tells the Parquet writer to set the 
isAdjustedToUTC
flag to 
true
in the file metadata. This will cause downstream tools (like Python/Pandas) to recognize the column as 
datetime64[us, UTC]
. Example:
Copy code
sql
INSERT INTO FILES (
    "path" = "<s3://my-bucket/export/>",
    "format" = "parquet",
    "parquet.is_adjusted_to_utc" = "true", -- Explicitly set UTC attribute
    "compression" = "lz4"
)
SELECT * FROM my_table;
2. Difference between 3.3.x and 3.5.x * StarRocks 3.3.x: Often defaulted to "Instant Semantics" for Parquet exports, or inherited the UTC property when reading from catalogs like Iceberg (which stores 
TIMESTAMPTZ
in UTC). * StarRocks 3.5.x: Defaults to Local Semantics. In this mode, the system writes the raw year, month, day, and hour components into the file without a timezone offset metadata flag. This improves compatibility with systems that do not handle timezone-aware Parquet files well but results in the "naive" 
datetime64
type you are seeing. 3. Alternative: Legacy Encoding If your downstream tool is older (e.g., an older version of Hive), you can use legacy encoding to force a specific behavior: * 
"parquet.use_legacy_encoding" = "true"
: This forces the use of the 
INT96
format for timestamps. While 
INT96
is technically deprecated in the Parquet spec, many tools interpret it as UTC by default. Important Note on Data Values Setting the metadata flag (
isAdjustedToUTC=true
) does not automatically convert your data values to UTC if they are already stored as local time in StarRocks. To ensure both the metadata and the data are correct, you should combine the property with a manual conversion in your SQL:
Copy code
sql
INSERT INTO FILES (
    "path" = "<s3://my-bucket/export/>",
    "format" = "parquet",
    "parquet.is_adjusted_to_utc" = "true"
)
SELECT 
    -- Convert local DATETIME to UTC before exporting
    CONVERT_TZ(my_timestamp, @@time_zone, '+00:00') as my_timestamp 
FROM my_table;
Summary of Properties for 
FILES()
(3.5.x) Property | Value | Effect 
parquet.is_adjusted_to_utc
| 
true
| Writes 
isAdjustedToUTC=true
(Shows as UTC in Pandas). 
parquet.is_adjusted_to_utc
| 
false
| Writes 
isAdjustedToUTC=false
(Shows as Naive in Pandas). 
parquet.use_legacy_encoding
| 
true
| Uses 
INT96
(Legacy format). 
parquet.version
| 
2.6
| Sets Parquet version (default is 2.6). References • faq/loading/Broker_load_faq.md • loading/Spark-connector-starrocks.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-functions/table-functions/files.md • administration/management/timezone.md • unloading/arrow_flight.md • integrations/loading_tools/DataX-starrocks-writer.md
r
@Rocky how to unload data with function insert into files as tz aware
r
To unload data as "timezone-aware" (specifically to ensure the Parquet file has the 
isAdjustedToUTC
flag set to 
true
), you need to use the 
parquet.is_adjusted_to_utc
property within the 
FILES()
function. In StarRocks 3.5.x, the default behavior for Parquet unloading is Local Semantics (naive timestamps), which is why you are seeing 
datetime64
instead of 
datetime64[us, UTC]
. How to export as TZ-Aware Add 
"parquet.is_adjusted_to_utc" = "true"
to your 
FILES()
parameters. This forces the Parquet writer to include the UTC adjustment metadata, making the column "timezone-aware" for downstream tools like Pandas or Spark. Example SQL:
Copy code
sql
INSERT INTO FILES (
    "path" = "<s3://my-bucket/export/data_>",
    "format" = "parquet",
    "parquet.is_adjusted_to_utc" = "true" -- This makes the file TZ-aware
)
SELECT 
    -- It is recommended to manually convert your data to UTC 
    -- to match the metadata flag you are setting above.
    CONVERT_TZ(your_timestamp_column, @@time_zone, '+00:00') as your_timestamp_column
FROM your_table;
Why you see 
datetime64
in 3.5.x * StarRocks 3.3.x: Earlier versions often defaulted to "Instant Semantics" for certain catalogs or configurations, which automatically tagged timestamps as UTC. * StarRocks 3.5.x: To improve compatibility with systems that don't support timezone-aware Parquet files, StarRocks 3.5.x defaults to "Local Semantics" (
isAdjustedToUTC = false
). In this mode, the data is written as a "naive" timestamp. Summary of properties for 
INSERT INTO FILES
(Parquet) Property | Value | Description 
parquet.is_adjusted_to_utc
 | 
"true"
| Sets the metadata flag to 
true
. Result: TZ-aware. 
parquet.is_adjusted_to_utc
 | 
"false"
| Sets the metadata flag to 
false
. Result: Naive (Local). 
parquet.use_legacy_encoding
 | 
"true"
| Uses 
INT96
format (Legacy). Often treated as UTC by Hive. 
parquet.version
 | 
"2.6"
| Specifies the Parquet version (3.4.6+). Note: Simply setting the property to 
true
tells the Parquet file that the data is in UTC. You should ensure your 
SELECT
statement actually converts the data to UTC using 
CONVERT_TZ()
so that the metadata and the actual values match. References • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/Spark-connector-starrocks.md • sql-reference/sql-functions/table-functions/files.md • faq/loading/Broker_load_faq.md • data_source/feature-support-data-lake-analytics.md • administration/management/timezone.md • administration/management/BE_configuration.md
r
@Rocky is adjusted to utc parameter never works on starrocks 3.5.*+, as you said it should be set tz aware when save as parquet
r
If you are using StarRocks 3.5.x, you are correct that the 
parquet.is_adjusted_to_utc
parameter often does not produce the expected "TZ-aware" (e.g., 
datetime64[us, UTC]
) behavior in downstream tools like Pandas or Spark. This is due to a shift in how StarRocks handles Parquet timestamp metadata in recent versions. The Problem in 3.5.x In StarRocks 3.5.x, the system defaults to Local Semantics for Parquet unloading. In this mode: 1. Timestamps are written as raw values (year, month, day, etc.) without an offset. 2. The metadata flag 
isAdjustedToUTC
is typically set to 
false
. 3. Downstream tools interpret this as a naive 
datetime64
(no timezone). Even if you pass 
"parquet.is_adjusted_to_utc" = "true"
, the writer in 3.5.x may ignore this property if the underlying session isn't explicitly configured to treat the data as UTC, or it might set the flag but fail to shift the data, leading to "false" UTC information. How to achieve a TZ-Aware file in 3.5.x To ensure the resulting Parquet file is recognized as UTC-aware by other systems, follow these two steps: 1. Set the Session Variable Before running your 
INSERT
statement, you must tell the StarRocks engine to use Instant Semantics. This ensures the system recognizes the difference between local time and UTC during the write process.
Copy code
sql
-- This tells the system to use Instant Semantics for Parquet timestamps
SET session export_parquet_timestamp_with_timezone = true;
(Note: If this variable is not recognized in your specific sub-version of 3.5, it defaults to Local Semantics, which is why the parameter seems to "never work".) 2. Use the legacy encoding (The most reliable fix) If the standard UTC flag is being ignored by your specific downstream reader, the most reliable way to force a UTC-like behavior in StarRocks 3.5.x is to use Legacy Encoding. This uses the 
INT96
format, which many tools (Pandas, Hive, Spark) interpret as UTC by default.
Copy code
sql
INSERT INTO FILES (
    "path" = "<s3://my-bucket/export/>",
    "format" = "parquet",
    "parquet.use_legacy_encoding" = "true" -- Forces INT96, often interpreted as UTC
)
SELECT 
    -- Always manually convert to UTC to be safe
    CONVERT_TZ(your_column, @@time_zone, '+00:00') 
FROM your_table;
Summary of what changed * 3.3.x: Often defaulted to Instant Semantics (UTC) because it was largely designed around Iceberg/Lakehouse interoperability. * 3.5.x: Optimized for "Local Semantics" to prevent unexpected time shifts for users who don't want UTC conversion. This is why the specific "is adjusted" parameter often feels ignored—the system is now strictly separating "Local" and "Instant" logic. Recommendation: If you specifically need the metadata to show 
UTC
, use 
parquet.use_legacy_encoding
= 
true
and perform a 
CONVERT_TZ
to 
+00:00
in your query. This is currently the most compatible way to ensure the data is "TZ aware" when it leaves StarRocks 3.5. References • sql-reference/sql-functions/table-functions/files.md • loading/loading_introduction/feature-support-loading-and-unloading.md • administration/management/timezone.md • faq/loading/Broker_load_faq.md References • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/hdfs_load.md • loading/InsertInto.md • sql-reference/sql-functions/table-functions/files.md
r
@Rocky on starrocks 3.3.* version starrocks unload parquet files as is adjusted to utc true. is that convert timestamp data automaticly? (INSERT INTO FILES)
r
Yes, in StarRocks 3.3.*, when you use 
INSERT INTO FILES
to unload data to Parquet, it does convert the timestamp data to UTC automatically if the file is saved with 
isAdjustedToUTC = true
. Here is the breakdown of how this works in version 3.3.x: 1. Automatic UTC Conversion In StarRocks 3.3.x, the Parquet writer uses Instant Semantics by default (or as the primary behavior). * Source: StarRocks stores 
DATETIME
values as "naive" local time (relative to your session's 
time_zone
). * Process: When unloading to Parquet with the 
isAdjustedToUTC
flag set to 
true
, the system reads your session's 
time_zone
variable and converts the 
DATETIME
values from that local time into UTC before writing them to the file. * Result: Downstream tools (like Pandas or Spark) will see the column as a UTC-aware timestamp (e.g., 
datetime64[us, UTC]
). 2. Why it changed in 3.5.x As you noticed, StarRocks 3.5.x defaults to Local Semantics (
isAdjustedToUTC = false
). * In 3.5.x, the system unloads the "raw" wall-clock time (year, month, day, etc.) without any conversion. This was changed to improve compatibility with systems that do not handle timezone-aware Parquet files well. * This is why in 3.5.x you see a naive 
datetime64
instead of the UTC-aware type you saw in 3.3.x. 3. Important Note for 3.3.x Users If you are on an earlier sub-version of 3.3 (before v3.3.11), there was a known bug where the Parquet writer failed to perform this conversion correctly even when the flag was set (Fixed in PR #55194). If you find your data is still "naive" or shifted incorrectly in 3.3, ensure you are on v3.3.11 or later. Summary Comparison Feature | StarRocks 3.3.x | StarRocks 3.5.x Default Semantic | Instant (
isAdjustedToUTC=true
) | Local (
isAdjustedToUTC=false
) Data Value | Converted to UTC | Raw/Local (No conversion) Type in Pandas | 
datetime64[us, UTC]
| 
datetime64[ns]
(Naive) If you want to force the 3.3.x behavior in 3.5.x, you must explicitly set 
"parquet.is_adjusted_to_utc" = "true"
in the 
FILES()
properties and ensure your session's 
time_zone
is correctly set. References • loading/Spark-connector-starrocks.md • loading/loading_introduction/feature-support-loading-and-unloading.md • faq/loading/Broker_load_faq.md • sql-reference/sql-functions/table-functions/files.md • administration/management/timezone.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md
r
@Rocky why timestamp converted from parquet file when im using select from files with session timezone as asia/seoul? why insert into files never convert but select from files working with ? it works even though parquet ts isAjustedToUTC=false (tz naive)
r
The discrepancy you are seeing in StarRocks 3.5.x occurs because the Reader and the Writer for the 
FILES()
function handle timezones differently. 1. Why 
SELECT FROM FILES
works (Reading) When you run a 
SELECT
on a Parquet file, the StarRocks reader applies the session's 
time_zone
(e.g., 
Asia/Seoul
) to the values it reads. * Behavior: Even if 
isAdjustedToUTC
is 
false
(naive), the reader often treats these timestamps as relative to a "base" time and then shifts them into your session's 
time_zone
for display or ingestion into a 
DATETIME
column. * Result: You see the data "converted" or shifted because the query engine is actively applying your session's offset during the read process. 2. Why 
INSERT INTO FILES
never converts (Unloading) In StarRocks 3.5.x, the Parquet unloading process is currently "timezone-blind" by design for compatibility reasons. * Local Semantics: StarRocks 3.5.x defaults to Local Semantics for unloading. Since StarRocks 
DATETIME
is a naive type (it doesn't store a timezone), the writer simply takes the raw "wall-clock" numbers and writes them into the Parquet file as they are. * Missing Support: According to the StarRocks documentation for 3.5.x, the session variable 
time_zone
is listed as "To be supported" for 
INSERT INTO FILES
. This means that unlike the reader, the writer currently ignores your 
SET time_zone = 'Asia/Seoul'
command. It does not perform any automatic shift to UTC or any other zone when creating the file. Summary Comparison Operation | Session 
time_zone
Impact | Default Semantic SELECT FROM FILES | Applied (Converts/Shifts) | Reader applies session offset. INSERT INTO FILES | Ignored (No conversion) | Local Semantics (Raw write). How to fix it Because the writer in 3.5.x does not yet support automatic session-based conversion, you must manually shift the time in your 
SELECT
statement to ensure the data in the file matches your expectations:
Copy code
sql
INSERT INTO FILES (
    "path" = "<s3://my-bucket/unload/>",
    "format" = "parquet",
    "parquet.is_adjusted_to_utc" = "true" -- Metadata flag
)
SELECT 
    -- Manually convert to UTC since the writer won't do it automatically
    CONVERT_TZ(your_datetime_col, 'Asia/Seoul', '+00:00') 
FROM your_table;
By doing this, you are manually doing what the 
INSERT INTO FILES
command is not yet capable of doing automatically in version 3.5.x. References • loading/loading_introduction/feature-support-loading-and-unloading.md • administration/management/timezone.md • faq/loading/Broker_load_faq.md • integrations/loading_tools/DataX-starrocks-writer.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Spark-connector-starrocks.md • loading/InsertInto.md
Open in Slack
PreviousNext
Powered by