question Re: Issue with Time Shift During Data Migration from MSSQL to PostgreSQL Using NiFi in Support Questions

Issue with Time Shift During Data Migration from MSSQL to PostgreSQL Using NiFi

Ytch — Thu, 22 Aug 2024 11:03:09 GMT

I am currently migrating data from MSSQL to PostgreSQL. While fetching the data from MSSQL, I use the following query to ensure the time is in UTC:

SELECT "time column" AT TIME ZONE 'UTC' AS "time column"

This gives me the correct result in UTC. However, when I ingest the data into PostgreSQL using NiFi's PutDatabaseprocessor, I notice that the time is being increased by 5 hours. The destination column in PostgreSQL is of type timestamp with time zone.

when I manually run an INSERT command in PostgreSQL with the same time value, the time remains unchanged. The time shift only occurs during the NiFi ingestion process. Any ideas on what could be causing this discrepancy?

Re: Issue with Time Shift During Data Migration from MSSQL to PostgreSQL Using NiFi

Shelton — Tue, 11 Mar 2025 23:16:33 GMT

@Ytch
Can you try this solution that has been accepted in this forum
MSSQL to PostgreSQL

happy hadooping

Re: Issue with Time Shift During Data Migration from MSSQL to PostgreSQL Using NiFi

TimL — Tue, 18 Mar 2025 14:47:28 GMT

The issue may be due to NiFi's PutDatabaseProcessor applying a local time zone conversion during data ingestion, causing the 5-hour shift. To fix this, ensure NiFi is explicitly set to handle UTC time zones for both reading and writing. Additionally, using ODBC for PostgreSQL could help by providing better control over time zone handling during the data transfer, ensuring consistency between MSSQL and PostgreSQL.