Epoch(millisecond) to UTC timestamp conversion adding 2 days to the default date(0001-01-01) in nifi

Question

One of my nifi process is taking data from JSON object and inserting it in postgres table. I'm currently facing an issue with inserting default time -62135596800000 (0001-01-01) from NiFi to PostgreSQL. I've noticed that when the data is inserted, there's an unexpected addition of 2 days, resulting in the date showing up as 0001-01-03 in PostgreSQL.

Can someone help me in understanding the root cause for this? Please note that nifi internally converts epoch to UTC while inserting data from JSON to postgres, I am not doing any conversion in nifi process.

Edit:

Here is an example (I have attached screenshots):

1. I am taking a JSON file as an input that has both negative and positive epoch times. I am getting content of JSON file using "GetFile" processor. Here is how JSON file looks like : [ { "EndTime": -62135596800000 }, { "EndTime": -62135596800000 }, { "EndTime": -62135596800000 }, { "EndTime": -62135596800000 }, { "EndTime": 1709320871411 } ]
2. Next, I am using "PutDatabaseRecord" processor to insert this JSON data in a postgres table.Nifi Flow
3. If you see in the screenshot, the date is getting inserted as 0001-01-03 instead of 0001-01-01 in the database.Datatype of column is timestamp. Table screenshot

Answer 1

Something in your system is probably doing a time zone or other conversion.

As a test in psql:

SET TIME ZONE 'UTC'; 
--Make sure the TZ is set to UTC;

select extract(EPOCH  from '01/01/0001 00:00'::timestamp);  
--My system returned -62135596800 - Note that this
--EPOCH is slightly different than yours

select to_timestamp(-62135596800);
--Returned 0001-01-01 00:00:00.000000 +00:00