[
https://issues.apache.org/jira/browse/NIFI-11470?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17848976#comment-17848976
]
Julien G. commented on NIFI-11470:
----------------------------------
The issue can't be solved because it's inherent to Java. The discussion around
it can be seen
[here|https://apachenifi.slack.com/archives/C0L9VCD47/p1716471923088829].
{quote}This could be more complicated than it seems because the general
TIMESTAMP type does not include a time zone offset.
In the process of converting between the database record value and the NiFi
field, a TIMESTAMP will be handled using the time zone of the NiFi node itself.
This is inherent behavior in Java, due to the nature of not having the time
zone offset in the database field.{quote}
It is recommended to force NiFi's time zone to UTC to solve the problem. It's
important to note that the same problem can occur with database servers that
are configured with a non-UTC time zone.
> SQL Record query TimeZone issue
> -------------------------------
>
> Key: NIFI-11470
> URL: https://issues.apache.org/jira/browse/NIFI-11470
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 2.0.0-M1, 1.19.1, 1.21.0, 1.24.0, 1.23.2, 1.25.0,
> 2.0.0-M2, 1.26.0, 2.0.0-M3
> Reporter: Julien G.
> Priority: Major
> Attachments: Example_Issue_Timestamp_SQL_Query_on_record.json,
> TIMEZONE_ISSUE.json
>
>
> In the case of a cluster with a timezone different from UTC (+0 hours) like
> CEST (+2 hours), in processors like QueryRecord or JoinEnrichment that use an
> SQL query to manipulate the record, the TIMESTAMP type field will be
> converted again and again to UTC.
> So, for example, if you have JSON with a field with the value 2023/04/19
> 18:04:00 +0200 and you say it's a TIMESTAMP field in the Avro schema and
> convert it to Avro, the field will be set to UTC (2023/04/19 16:04:00 +0000).
> But if you then use a QueryRecord you will have 2023/04/19 14:04:00 +0000 and
> if you put another QueryRecord you will have 2023/04/19 12:04:00 +0000, ...
> The field is reinterpreted as CEST time zone instead of UTC each time.
> Same issue with SQL join strategy in the JoinEnrichment.
> You can find a dataflow illustrating the point attached to the Jira.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
