[
https://issues.apache.org/jira/browse/NIFI-8749?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17390654#comment-17390654
]
Xavier Balayer commented on NIFI-8749:
--------------------------------------
I've been getting the same issue - with Nifi 1.12.1 when inserting JSON with
timestamps it was inserted as-is, but since upgrading directly to 1.14.0 the
readable timestamps have been changed to include +2 hours (we are GMT +2).
*Environment:*
We collect data from various sources in JSON format (e.g. via API's). Each of
these goes to different data sources for different projects, and the timestamps
are either in GMT or have our timezone included. We know which sources already
include our timezone so we manage it elsewhere. This happens when using
PutDatabaseRecord to MySQL, MS SQL and IBM Informix.
Nifi is running on Ubuntu 20.04 LTS.
*Example flow:*
Here's an example receiving JSON, inserting into MySQL and then extracting it
again. The data is not changed on the MySQL side a part from columns being
added to the output.
!image-2021-07-30-17-35-58-914.png!
The input data looks like this:
{code:java}
{
"t_stamp" : "30/07/2021 10:25:00",
"q2varh" : "0",
"sa" : "6340",
"sb" : "4900",
"qa" : "2000",
"sc" : "4920",
"qb" : "2380",
"qc" : "2180",
"date_posted" : "2021-07-30 10:51:14.209",
"ia" : "28.4",
"ib" : "21.2",
"ic" : "20.9",
"q3varh" : "0",
"vah" : "4.98777527906E8",
"ptot" : "14720",
"q4varh" : "96.42",
"impwh" : "4.68355305876E8",
"expwh" : "0",
"pfa" : "0.949",
"va" : "227.1",
"pfc" : "0.894",
"vb" : "234.4",
"pfb" : "0.873",
"vc" : "238.5",
"epoch_timestamp" : "1627633507",
"electricity_meter_id" : "3736",
"stot" : "16120",
"pa" : "6000",
"pb" : "4280",
"pc" : "4420",
"pftot" : "0.913",
"q1varh" : "1.59794322468E8",
"t_stamp2" : "2021-07-30 10:25:00.000",
"meter_id" : "48101542",
"qtot" : "6580"
}
{code}
and the output data looks like this:
{code:java}
[ {
"id" : 900265,
"version" : 0,
"date_posted" : "2021-07-30 12:51:09.0",
"meter_id" : "68116423",
"electricity_meter_id" : 8200,
"t_stamp" : "30/07/2021 10:25:00",
"t_stamp2" : "2021-07-30 12:25:00.0",
"epoch_timestamp" : 1627633508,
"impwh" : 8.7447629976E7,
"expwh" : 2542367.419,
"q1varh" : 3.1200010653E7,
"q2varh" : 498086.88,
"q3varh" : 4417.92,
"q4varh" : 140282.88,
"vah" : 9.7238243079E7,
"va" : 1.5,
"vb" : 237.2,
"vc" : 237.6,
"ia" : 287.7,
"ib" : 260.2,
"ic" : 210.5,
"pa" : -320.0,
"pb" : 55040.0,
"pc" : 41120.0,
"ptot" : 96000.0,
"qa" : -160.0,
"qb" : 26400.0,
"qc" : 27040.0,
"qtot" : 53120.0,
"sa" : 320.0,
"sb" : 61120.0,
"sc" : 49280.0,
"stot" : 109760.0,
"pfa" : 1.0,
"pfb" : 0.9,
"pfc" : 0.834,
"pftot" : 0.874,
"processed" : 0,
"error" : 0,
"derived" : 0,
"recalc" : 0
}]
{code}
Note how t_stamp and date_posted had their times changed, while t_stamp2 which
is in an odd format kept the correct time. Both t_stamp and t_stamp2 here
should be identical.
*Data types*:
* MySQL: t_stamp and date_posted are "datetime" while t_stamp2 is a varchar
* MS SQL: timestamps are all datetime2(7) - this is in a different flow
altogether, but same issue.
* Informix: t_stamp and datetime is "datetime year to fraction(5)"
*Expected behaviour*
The same as 1.12.1: timestamps are not changed from what the processor
receives. PutDatabaseRecord should just insert the data it received and not
change it. Alternatively, a field to toggle this could be added so that users
that want timestamps to change can.
The problem with the current way though is if the same data flows through
multiple databases with PutDatabaseRecord then each time it will add 2 hours.
> PutDatabaseRecord Insert Datetime Type Time Zone Issue
> ------------------------------------------------------
>
> Key: NIFI-8749
> URL: https://issues.apache.org/jira/browse/NIFI-8749
> Project: Apache NiFi
> Issue Type: Bug
> Affects Versions: 1.13.2
> Reporter: Billy Lin
> Priority: Critical
> Attachments: cc1.png, cc2.png, cc3.png, cc4.png,
> image-2021-07-01-15-17-38-035.png, image-2021-07-30-17-35-58-914.png
>
>
> when I query a record from Mysql to get an AVRO Flowfile, then use
> PutDatabaseRecord(version 1.13.2) insert to another table (with same table
> structure), the data of the 'datetime' type column will change. The original
> data of the first table I query from is '2021-06-28
> {color:#FF0000}13{color}:14:20.0'. and the data of the second table which I
> insert to becomes '2021-06-28 {color:#FF0000}06{color}:14:20.0'.The gap is
> {color:#FF0000}7 hours{color}.
> our local timezone is PDT ({color:#FF0000}-7:00{color}). It seems that Nifi
> has done some timezone conversion work. But the 'datetime' type in mysql
> should have no relation to timezone
>
> when we use version 1.7.1 ,it works fine.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
