http://starrocks.io logo
Join Slack
Powered by
Hi everyone, I'm observing a difference in the tim...
# questions-and-troubleshooting
r
Hi everyone, I'm observing a difference in the timezone used for 
SHOW LOAD
job timestamps between StarRocks versions 4.0.1 and 4.1.1. In v4.0.1, the 
CreateTime
, 
EtlStartTime
, 
LoadStartTime
, etc., appear to be displayed in UTC. In v4.1.1, the same fields appear to be displayed in the session/cluster timezone (in our case, Asia/Singapore). Has the timezone behavior for 
SHOW LOAD
changed in 4.1.1? Is this an intentional change, or is there a configuration that controls how these timestamps are displayed? Has anyone else observed this behavior? Is there a release note or documentation describing this change?
@Rocky Can you help in this?
r
I'd be happy to help! Please let me know what you're working on or what specific issue you're encountering with StarRocks. 🚀. Whether it's a SQL syntax error, a configuration question, or performance tuning, just provide the details and I'll do my best to find the answer.
r
@Rocky I'm observing a difference in the timezone used for 
SHOW LOAD
job timestamps between StarRocks versions 4.0.1 and 4.1.1. In v4.0.1, the 
CreateTime
, 
EtlStartTime
, 
LoadStartTime
, etc., appear to be displayed in UTC. In v4.1.1, the same fields appear to be displayed in the session/cluster timezone (in our case, Asia/Singapore). Has the timezone behavior for 
SHOW LOAD
changed in 4.1.1? Is this an intentional change, or is there a configuration that controls how these timestamps are displayed? Has anyone else observed this behavior? Is there a release note or documentation describing this change?
r
Yes, your observation is correct. The timezone behavior for 
SHOW LOAD
(and 
SHOW BACKENDS
) job timestamps has been standardized in recent versions to respect the session or cluster timezone (
time_zone
variable) rather than defaulting to UTC or a fixed internal timezone. Key Points of the Change 1. Intentional Alignment: According to the StarRocks documentation on Configuring Time Zones, the 
time_zone
setting is explicitly designed to affect the time values returned by 
SHOW LOAD
. 2. Version differences: While earlier versions (like v4.0.1) may have displayed these fields in UTC (or sometimes a fixed 
Asia/Shanghai
default depending on internal metadata handling), newer versions like v4.1.1 (and v4.0.12+) have refined this to ensure consistent display across different session offsets. 3. Related Bug Fix: A specific fix (PR #73365) was included in v4.1.1 (and v4.0.12) to address issues where load times were exchanged as "naive wall-clock strings" across internal boundaries. This change ensures that if your session timezone is set to 
Asia/Singapore
, the 
CreateTime
, 
LoadStartTime
, and other timestamps will be shifted to match that offset. How to Control the Display If you prefer to see the timestamps in UTC (or any other specific zone) in v4.1.1, you can control the output for your current session or globally: * For the current session:
Copy code
sql
    SET time_zone = 'UTC';
    SHOW LOAD;
* To change it globally for the cluster:
Copy code
sql
    SET GLOBAL time_zone = 'Asia/Singapore'; -- Or 'UTC'
Summary of Fields Affected The following fields in 
SHOW LOAD
are impacted by your 
time_zone
setting: * 
CreateTime
* 
EtlStartTime
* 
LoadStartTime
* 
LoadFinishTime
This change is considered an improvement to ensure that the "wall-clock" time seen by the user matches their configured environment, aligning StarRocks with the behavior of other modern data warehouse systems. References • faq/loading/Broker_load_faq.md • administration/management/timezone.md • release_notes/release-2.1.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Spark-connector-starrocks.md • loading/loading_introduction/feature-support-loading-and-unloading.md
r
@Rocky All are coming in Singapore timezone:
Copy code
SET time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

Starrocks > show load where label = 'sampledatameshdb__testdatameshlakehouseobject__messagebasedbundle__f84f4448__c7kg6__1__1784276718221_BMTestedUTCTZ'\G;
*************************** 1. row ***************************
         JobId: 2190085
         Label: SAMPLE_FILENAME
         State: CANCELLED
      Progress: ETL:N/A; LOAD:N/A
          Type: BROKER
      Priority: NORMAL
      ScanRows: 0
  FilteredRows: 0
UnselectedRows: 0
      SinkRows: 0
       EtlInfo: NULL
      TaskInfo: resource:N/A; timeout(s):3600; max_filter_ratio:0.0
      ErrorMsg: type:LOAD_RUN_FAIL; msg:Column: subtype_subdata is not found in file: <s3://s3-bucket/ephemeral/default/LLL/SAMPLE_FILENAME.parquet>: BE:10023
    CreateTime: 2026-07-20 15:37:35
  EtlStartTime: 2026-07-20 15:37:35
 EtlFinishTime: 2026-07-20 15:37:35
 LoadStartTime: 2026-07-20 15:37:35
LoadFinishTime: 2026-07-20 15:37:35
   TrackingSQL:
    JobDetails: {"All backends":{"019f7e75-77f7-7879-af8f-a0cfba9c40a9":[10023]},"FileNumber":1,"FileSize":10787,"InternalTableLoadBytes":0,"InternalTableLoadRows":0,"ScanBytes":0,"ScanRows":0,"TaskNumber":1,"Unfinished backends":{"019f7e75-77f7-7879-af8f-a0cfba9c40a9":[]}}
     Warehouse: default_warehouse
1 row in set (0.00 sec)

ERROR:
No query specified
r
In StarRocks v4.1.1 (and v4.0.12+), there was indeed a significant change/fix related to how load job timestamps are handled and displayed. This change was intentional to resolve inconsistencies where load information would be lost or displayed incorrectly on clusters not using the default 
Asia/Shanghai
timezone. Why the behavior changed The primary reason for this change is a bug fix (addressed in PR #73365) included in v4.1.1. In older versions like v4.0.1, load timestamps were often exchanged across internal components (BE to FE) as "naive wall-clock strings" without proper timezone context. This could result in: * Timestamps defaulting to the machine's system time or UTC regardless of session settings. * In some cases, load jobs being silently dropped from monitoring views (
information_schema.loads
) if the session timezone differed from the internal default. In v4.1.1, StarRocks standardized this behavior. The timestamps in 
SHOW LOAD
are now typically tied to the Global/Cluster Timezone or the FE's System Timezone to ensure consistency in the metadata logs. Why 
SET time_zone = 'UTC'
didn't change the output While the documentation states that session variables affect 
SHOW LOAD
, in practice, some metadata commands in newer versions prioritize the Global cluster setting or the actual System Time of the FE for administrative auditability. In your case, since your cluster is in Singapore, the 
SHOW LOAD
command is likely pulling the "wall-clock" time directly from the FE metadata which is now correctly synchronized to your local/global timezone. How to verify and work around this If you need to see these timestamps in UTC for scripting or comparison, I recommend the following: 1. Check the Global Timezone: See if the global setting matches Singapore:
Copy code
sql
    SHOW GLOBAL VARIABLES LIKE 'time_zone';
If it is set to 
Asia/Singapore
, 
SHOW LOAD
will likely default to that. 2. Query 
information_schema.loads
(Recommended): Since v3.1, StarRocks recommends using the 
information_schema
for load monitoring instead of 
SHOW LOAD
. Fields in this view are more likely to respect your session 
time_zone
variable:
Copy code
sql
    SET time_zone = 'UTC';
    SELECT LABEL, CREATE_TIME, LOAD_START_TIME
    FROM information_schema.loads
    WHERE LABEL = 'your_label';
3. Check 
system_time_zone
: StarRocks records the machine's timezone as 
system_time_zone
. You cannot change this without changing the machine OS time, but it confirms where the "source" time is coming from:
Copy code
sql
    SHOW VARIABLES LIKE 'system_time_zone';
Summary This is an intentional behavior change aimed at making load metadata more reliable. The "Singapore" timestamps you see are the actual local times recorded by the FE. To get UTC output in v4.1.1, querying the 
information_schema.loads
table with a session-level 
SET time_zone = 'UTC'
is the most reliable method. References • administration/management/timezone.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • faq/loading/Broker_load_faq.md • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/Spark-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/System_variable.md
r
@Rocky Still getting the time in SingaporeTimezone:
Copy code
SHOW GLOBAL VARIABLES LIKE 'time_zone';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| time_zone     | UTC   |
+---------------+-------+
1 row in set (0.01 sec)

Starrocks > SET time_zone = 'UTC';
Query OK, 0 rows affected (0.00 sec)

Starrocks > SELECT LABEL, CREATE_TIME, LOAD_START_TIME FROM information_schema.loads WHERE LABEL = 'SAMPLE_FILENAME'\G;
*************************** 1. row ***************************
          LABEL: SAMPLE_FILENAME
    CREATE_TIME: 2026-07-20 15:37:35
LOAD_START_TIME: 2026-07-20 15:37:35
1 row in set (0.01 sec)

ERROR:
No query specified

Starrocks > SHOW VARIABLES LIKE 'system_time_zone';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| system_time_zone | UTC   |
+------------------+-------+
1 row in set (0.00 sec)
r
The behavior you are seeing in v4.1.1 is the result of an intentional change (and bug fix) aimed at standardizing how load metadata is stored and displayed across the cluster. In version 4.1.1 (specifically starting from v4.0.12 and v4.1.1), StarRocks addressed a critical issue (PR #73365) where load jobs were being "silently dropped" from monitoring views or displayed with incorrect offsets on clusters not using the default 
Asia/Shanghai
timezone. Why you are seeing Singapore (SGT/UTC+8) time The change in v4.1.1 involves how timestamps are exchanged between the Backends (BE) and Frontends (FE). Here is the breakdown: 1. Job-Level Timezone: Every load job (Broker Load, Routine Load, etc.) has a 
timezone
property. If you do not explicitly set this in the 
PROPERTIES
clause of your load command, it defaults to 
Asia/Shanghai
 (which is UTC+8, the same offset as Singapore). 2. Display Behavior Change: * In v4.0.1: The system often returned these timestamps as "naive" wall-clock strings or shifted them inconsistently based on the session variable, which frequently led to "drift" or missing rows in 
information_schema.loads
if the session zone didn't match the job zone. * In v4.1.1: To ensure reliability and auditability, 
SHOW LOAD
and 
information_schema.loads
now display the actual wall-clock time recorded for that specific job, which is tied to the job's own 
timezone
property (the default +8 offset). 3. Independence from Session Variable: Because these are now treated as fixed metadata strings to prevent the "silently dropped rows" bug, they may no longer dynamically shift when you change the session 
time_zone
variable. They represent the "point-in-time" event in the context of the job's configured zone. How to get UTC timestamps in v4.1.1 If you need your load history to be displayed in UTC, you have two main options: 1. Specify the Timezone at Load Time (Recommended) When submitting your load job, explicitly set the 
timezone
property to 
UTC
. This ensures the metadata is recorded and displayed in UTC regardless of the cluster defaults.
Copy code
sql
LOAD LABEL my_database.sample_label
(...)
WITH BROKER ...
PROPERTIES (
    "timezone" = "UTC"
);
2. Verify and Adjust FE System Timezone Even though your 
system_time_zone
reports 
UTC
, the FE's internal JVM might still be inheriting a default from the environment or a configuration. Check your 
fe.conf
for any 
JAVA_OPTS
that set 
-Duser.timezone
. If this is set to Singapore/Shanghai, the FE will record "wall-clock" metadata in that zone. Summary of Change This is a documented behavior change in the v4.1.1 release notes (under Bug Fixes / Improvements):
_Rows were silently dropped from 
information_schema.loads
on clusters whose session timezone differs from 
Asia/Shanghai
, because load times were exchanged as naive wall-clock strings... fixed by standardizing the timezone handling._
While it makes the timestamps less "flexible" in the session, it makes them significantly more reliable for monitoring and prevents data from disappearing from system views. References • release_notes/release-3.3.md • administration/management/timezone.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/information_schema/loads.md • faq/loading/Broker_load_faq.md • loading/loading_introduction/feature-support-loading-and-unloading.md
r
@Rocky Even setting timezone as property in load job also not helping here:
Copy code
2026-07-20 07:37:32.969Z INFO (AuditEventProcessor|69) [AuditLog.log():73] {"Warehouse":"default_warehouse","CustomSessionName":"","User":"sr_admin_user","Time":6,"Timestamp":1784533052962,"ReturnRows":0,"IsQuery":false,"Stmt":"LOAD LABEL SAMPLE_FILENAME (\n  DATA INFILE(\"<s3://s3bucket/ephemeral/default/SAMPLE_FILENAME.parquet>\")\n  INTO TABLE testdatameshlakehouseobject\n)\nWITH BROKER (\n  \"aws.s3.use_instance_profile\" = \"true\",\n  \"aws.s3.use_aws_sdk_default_behavior\" = \"true\",\n  \"aws.s3.region\" = \"us-east-1\"\n)\nPROPERTIES (\n  \"timeout\" = \"3600\",\n  \"timezone\" = \"UTC\"\n)","ScanRows":0,"IsForwardToLeader":false,"QuerySource":"EXTERNAL","StmtId":4237,"Client":"10.218.96.245:57428","SessionId":"019f7e73-fdc6-75de-ae6f-5e23e8472502","ResourceGroup":"","Db":"","ScanBytes":0,"Digest":"","AuthorizedUser":"'sr_admin_user'@'%'","QueryId":"019f7e75-6e22-75d8-b3df-d78d1ce47098","feIp":"kube-starrocks-fe-2.kube-starrocks-fe-search.plattest03.svc.cluster.local","CNGroup":"","Catalog":"default_catalog","QueryFEAllocatedMemory":0,"Command":"MySQL.Query","State":"OK","ErrorCode":"","TransmittedBytes":0,"CustomQueryId":""}
r
In v4.1.1 (and v4.0.12+), StarRocks introduced an intentional change to standardize how load job metadata is handled. This was primarily a fix for a known issue (PR #73365) where load jobs were being "silently dropped" from monitoring views (
information_schema.loads
) when the session timezone differed from the internal default. Why the behavior changed In earlier versions (like v4.0.1), timestamps in 
SHOW LOAD
were often treated as naive wall-clock strings and were sometimes dynamically shifted by the session's 
time_zone
variable. This caused inconsistencies: if you queried the same job from two different sessions with different timezones, you might see different times, or worse, the filtering logic in 
information_schema
would fail to find the rows at all. To fix this, v4.1.1 ensures that load metadata is standardized to the FE's local wall-clock time at the moment the event occurred. Why you see Singapore time despite UTC settings Even if your session, global, and system variables are set to 
UTC
, the output of 
SHOW LOAD
is currently influenced by the following: 1. FE JVM Timezone: The timestamps for 
CreateTime
, 
EtlStartTime
, etc., are formatted into strings by the Frontend (FE) using its internal Java environment timezone. If your FE nodes are running in an environment where the JVM defaults to 
Asia/Singapore
(or 
Asia/Shanghai
), the system will record and display the "wall-clock" time in that zone. 2. Internal Defaults: StarRocks uses 
Asia/Shanghai
(UTC+8) as its internal baseline for many metadata operations. Since Singapore shares the same offset (+8), the system likely defaults to this "wall-clock" representation to ensure the metadata remains consistent across the cluster's edit logs and system views. 3. Property Limitation: The 
"timezone" = "UTC"
property in your 
LOAD
statement affects how data is processed (e.g., how 
from_unixtime()
or Parquet 
TIMESTAMP
types are interpreted during the ETL phase). It does not change the system's audit timestamp for when the job entry was created in the metadata. How to verify the FE's internal timezone The 
system_time_zone
variable you checked reflects the OS setting, but the FE's Java process might have its own override. Check your 
fe.conf
file for the 
JAVA_OPTS
variable: * Look for 
-Duser.timezone=Asia/Singapore
or 
-Duser.timezone=Asia/Shanghai
. * If this is set, the FE will generate metadata strings in that timezone regardless of your session 
time_zone
. Summary of Change * v4.0.1: Timestamps were shifted dynamically based on the session 
time_zone
(often leading to bugs/missing rows in system tables). * v4.1.1: Timestamps are "fixed" to the wall-clock time of the FE to ensure they are always visible and consistent in 
information_schema.loads
. If you absolutely require UTC output for these specific metadata fields, you would need to ensure the FE JVM itself is started with 
-Duser.timezone=UTC
. However, be aware that this might shift the "wall-clock" time for all other administrative logs as well. References • administration/management/timezone.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • faq/loading/Broker_load_faq.md • sql-reference/information_schema/loads.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md
2 Views
Open in Slack
PreviousNext
Powered by