Skip to main content
Mark06
Bronze ⭐️⭐️⭐️
Aug 30, 2022 1:33 PM UTC
Solved

Timestamp migration issue, PutDatabaseRecord drops milliseconds

Hi Everyone,

I have a pipeline that reads data from Postgres database and putts in to another Postgres database unchanged, I need the data to be identical, however Timestamps after being migrated missing microseconds.

2022-08-29 22:03:13.620961-05 -> Source
2022-08-29 22:03:13-05 -> Target

I have noticed that flow files contain correct format before reaching PutDatabaseRecord processor, but then on Target database microseconds are gone.

Can you please help me to understand how I can migrate timestamps with the microseconds?

rafy
rafyAnswer
Gold ⭐️
Sep 8, 2022 11:02 AM UTC

After fetching from the first DB, you can convert each record to json, the use evaluatejsonpath to extract the timestamp as attribute. then use updateAttribute processor to update timestamp attribute you extracted earlier: Let's say on evaluatejsonpath, the extracted timestamp attribute is "dTime". Then , on updateAttribute, you create same atttribute and set dTime value to ${dTime:toDate("yyyy-MM-dd hh:mm:ss"):format("yyyy-MM-dd hh:mm:ss.SSSSSS")}

1 reply

rafy
rafyAnswer
Gold ⭐️
Sep 8, 2022 11:02 AM UTC

After fetching from the first DB, you can convert each record to json, the use evaluatejsonpath to extract the timestamp as attribute. then use updateAttribute processor to update timestamp attribute you extracted earlier: Let's say on evaluatejsonpath, the extracted timestamp attribute is "dTime". Then , on updateAttribute, you create same atttribute and set dTime value to ${dTime:toDate("yyyy-MM-dd hh:mm:ss"):format("yyyy-MM-dd hh:mm:ss.SSSSSS")}

Translate