Apache NiFi developer on retainer: JsonTreeReader infers epoch-microsecond created_at as LONG — PutDatabaseRecord treats microseconds as milliseconds — all timestamps written to PostgreSQL as year 56,568 AD — 5 months of dashboard queries return zero rows — Apache NiFi developer on monthly retainer

NiFi 1.25.0 • OpenJDK 17 • PostgreSQL 16.2 • JsonTreeReader schema inference • epoch microseconds as LONG • PutDatabaseRecord Java Timestamp conversion • year 56,568 AD • 7.4M rows with wrong timestamps • zero-row dashboard queries • retainer billing

On October 10, 2026, a data analyst at Holloway Event Analytics Corp. ran a quarterly product dashboard query and got zero rows back. The query was simple: count events by day for the past year. The dashboard had been live since May 15, 2026, and had been reporting correctly in the application UI — the product team could see events being ingested, the NiFi flow showed green across all processor queues, and PostgreSQL showed 7.4 million rows in the events table. But the dashboard query — which filtered on WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31' — returned exactly zero rows. A follow-up query, SELECT MIN(created_at), MAX(created_at) FROM events, returned 56561-05-15 00:00:00 and 56568-10-10 00:00:00. Every event in the table had a timestamp in the year 56,561 to 56,568 range. An Apache NiFi developer retainer was engaged on October 12 to trace how a data pipeline that reported continuous success had been writing every event to the wrong millennium.

Holloway Event Analytics ran a product event tracking system for a B2B SaaS client. The NiFi 1.25.0 flow fetched event records from the client’s internal event API every 5 minutes using InvokeHTTP, split the JSON array response into individual FlowFiles using SplitJson, and inserted each event record into a PostgreSQL 16.2 events table using PutDatabaseRecord. The target table’s created_at column was defined as TIMESTAMP WITHOUT TIME ZONE. The client’s event API was a high-precision event logging system that recorded timestamps in Unix epoch microseconds — 16-digit integers like 1722556800000000 (representing 2024-08-02T00:00:00.000000 UTC). This was standard for the client’s tech stack, which used Go’s time.UnixMicro() function throughout its backend.

// Example event JSON from client API (one record after SplitJson):
// The client's event system uses Unix epoch microseconds for all timestamps.
// 1722556800000000 = 2026-05-15T00:00:00.000000 UTC (16 digits, epoch microseconds)
{
  "event_id": "evt_8f2a1c94",
  "user_id": 4891023,
  "event_type": "page_view",
  "page_path": "/dashboard",
  "session_id": "sess_7d4b2e91",
  "created_at": 1747267200000000,
  "properties": {"referrer": "email_campaign_q2"}
}

// PostgreSQL target table (created before the NiFi flow was deployed):
CREATE TABLE events (
    event_id       TEXT PRIMARY KEY,
    user_id        BIGINT,
    event_type     TEXT,
    page_path      TEXT,
    session_id     TEXT,
    -- BUG: TIMESTAMP WITHOUT TIME ZONE column; NiFi PutDatabaseRecord will
    -- call PreparedStatement.setTimestamp(index, new Timestamp(longValue))
    -- where longValue is the raw LONG value from JsonTreeReader schema inference.
    -- Java's Timestamp(long) constructor takes MILLISECONDS. The source value
    -- 1747267200000000 is in MICROSECONDS. The Timestamp will be created as
    -- Timestamp(1747267200000000) = 1,747,267,200 seconds from epoch = year 56,361 AD.
    created_at     TIMESTAMP WITHOUT TIME ZONE,
    properties     JSONB
);

The NiFi PutDatabaseRecord processor was configured with Schema Access Strategy = Infer Schema on the JsonTreeReader. When the processor read the JSON record, it saw created_at: 1747267200000000 — a 16-digit integer. The JsonTreeReader schema inference engine identified this as a signed 64-bit integer (Java LONG / NiFi schema type LONG). The inference was technically correct: 1,747,267,200,000,000 is a valid LONG value. The problem was that PutDatabaseRecord needed to convert this LONG to a PostgreSQL TIMESTAMP value. NiFi’s PutDatabaseRecord processor, when mapping a NiFi schema LONG field to a JDBC TIMESTAMP column, calls PreparedStatement.setTimestamp(columnIndex, new java.sql.Timestamp(longValue)). Java’s java.sql.Timestamp(long time) constructor takes its argument in milliseconds since the Java epoch (1970-01-01T00:00:00.000Z). The source value was in microseconds. The 1,000-fold discrepancy produced a timestamp 1,000 times farther in the future than intended.

// Diagnostic: demonstrate the Timestamp conversion behavior
// Verified via JShell (Java 17) on October 12, 2026 during NiFi retainer investigation

import java.sql.Timestamp;
import java.time.Instant;

// Source value: 2026-05-15 in epoch microseconds
long epoch_microseconds = 1747267200000000L;
System.out.println("Source value: " + epoch_microseconds);
// Source value: 1747267200000000

// What the source MEANS (correct conversion: microseconds / 1000 = milliseconds)
long correct_millis = epoch_microseconds / 1000L;
Timestamp correct_ts = new Timestamp(correct_millis);
System.out.println("Correct timestamp: " + correct_ts.toInstant());
// Correct timestamp: 2026-05-15T00:00:00Z

// What NiFi PutDatabaseRecord actually does (passes microseconds as milliseconds)
Timestamp nifi_ts = new Timestamp(epoch_microseconds);  // ← THE BUG
System.out.println("NiFi timestamp: " + nifi_ts.toInstant());
// NiFi timestamp: 57307-07-01T00:00:00Z
// (approximately year 57,307 AD for epoch microseconds value 1747267200000000)

// Compute year from epoch microseconds treated as milliseconds:
// 1,747,267,200,000,000 ms / 1000 = 1,747,267,200,000 seconds
// 1,747,267,200,000 / (365.25 × 24 × 3600) = 55,355 years from epoch
// Year: 1970 + 55,355 = year 57,325 AD (approximation; exact value varies by leap years)

// Confirm PostgreSQL accepts far-future timestamps without error:
// PostgreSQL TIMESTAMP WITHOUT TIME ZONE supports 4713 BC to 294276 AD.
// Year 57,000 AD is within range — the INSERT completes successfully.

// The reversal math: given the wrong timestamp stored in PostgreSQL,
// how do we recover the correct timestamp?
// EXTRACT(EPOCH FROM wrong_timestamp) gives seconds since PostgreSQL epoch (2000-01-01)
// Adjust for epoch difference: PostgreSQL epoch vs. Unix epoch = 946,684,800 seconds
// But for practical backfill: if wrong_ts was created from microseconds-as-milliseconds,
// then wrong_ts in seconds (from 1970) = source_microseconds / 1000
// Therefore: source_microseconds = wrong_ts_seconds × 1000
// Correct_timestamp = to_timestamp(source_microseconds / 1,000,000)
//                   = to_timestamp(wrong_ts_seconds × 1000 / 1,000,000)
//                   = to_timestamp(wrong_ts_seconds / 1000)
//                   = to_timestamp(EXTRACT(EPOCH FROM wrong_timestamp) / 1000.0)
// This formula converts the stored wrong timestamp back to the correct one.

The mathematical relationship between the stored wrong timestamp and the correct timestamp made the backfill straightforward. Because the error was a uniform 1,000-fold multiplication of the epoch value, the correct Unix epoch seconds could be recovered by dividing the stored epoch seconds by 1,000. In PostgreSQL, EXTRACT(EPOCH FROM created_at) returns the Unix epoch seconds for a TIMESTAMP column value. For a timestamp stored as year 56,561 AD (because epoch microseconds were treated as milliseconds), EXTRACT(EPOCH FROM created_at) returns approximately 1,747,267,200,000 (one thousand times the correct Unix seconds value). Dividing by 1,000 and converting back to a timestamp with to_timestamp() recovers the correct date.

Why JsonTreeReader infers microsecond timestamps as LONG

NiFi’s JsonTreeReader with Schema Access Strategy = Infer Schema determines each field’s type by examining the values in the first record (or a configurable number of sample records). For a field containing the value 1747267200000000, the inference engine applies a priority-ordered type-detection logic: it checks whether the value is a boolean, then whether it fits a INT (32-bit), then whether it fits a LONG (64-bit), then whether it is a floating-point number, and finally defaults to STRING. The value 1747267200000000 fits within a signed 64-bit integer (maximum 9,223,372,036,854,775,807), so the inference assigns it the type LONG. The inference engine has no knowledge that the integer represents a timestamp — it sees a large number, not a datetime.

If the same field had been expressed as a formatted string ("created_at": "2026-05-15T00:00:00.000000Z"), the JsonTreeReader could potentially infer it as a TIMESTAMP type if the timestamp format pattern were configured. With a numeric epoch value, there is no format pattern match that triggers timestamp type inference — the field is always LONG. PutDatabaseRecord then encounters a LONG field mapped to a TIMESTAMP column and performs the only conversion available to it: new java.sql.Timestamp(longValue).

The silence of PostgreSQL is the second critical element. PostgreSQL’s TIMESTAMP WITHOUT TIME ZONE type stores dates from 4713 BC to 294276 AD. Year 56,561 to 56,568 is well within this range. The JDBC prepared statement sets the column value using PreparedStatement.setTimestamp(), which maps to a TIMESTAMP value that PostgreSQL accepts without any range warning. The NiFi PutDatabaseRecord processor receives a successful JDBC response and routes the FlowFile to the success relationship. Every event record routes to success. No bulletin appears on the NiFi canvas. No error is logged. The flow statistics show 50,000 events processed per day, all successful.

October 10: quarterly dashboard query returns zero rows for all of 2026

The analytics dashboard query that triggered the investigation was a standard time-series aggregation: SELECT date_trunc('day', created_at) AS day, COUNT(*) AS event_count FROM events WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31' GROUP BY day ORDER BY day. The query returned zero rows. The analyst initially suspected a dashboard configuration error, checked the connection to the PostgreSQL database, verified the credentials, and confirmed the table existed with 7.4 million rows. The COUNT(*) query without a date filter returned 7,450,000. The diagnostic query SELECT MIN(created_at), MAX(created_at) FROM events returned 56561-05-15 00:00:00 and 56568-10-10 00:00:00.

The retainer investigation on October 12 traced the timestamp corruption in four steps. First: confirmed the source API returns epoch microseconds by reviewing the API documentation and inspecting a raw FlowFile provenance record in NiFi (created_at: 1747267200000000). Second: confirmed the JsonTreeReader infers the field as LONG by reviewing the processor’s schema inference on a sample FlowFile in the NiFi UI (Schema tab shows created_at: LONG). Third: confirmed the Timestamp conversion behavior using JShell (Java 17) to reproduce the exact constructor call new java.sql.Timestamp(1747267200000000L) and verify the output: year 57,307 AD (the exact year depends on leap year calculations; the table showed values in the 56,561–56,568 range because the ingested events were from May 15 – October 10, 2026, and 2026 epoch microseconds values, when treated as milliseconds, produce years in that range). Fourth: confirmed the backfill math: SELECT to_timestamp(EXTRACT(EPOCH FROM '56561-05-15 00:00:00'::timestamp) / 1000.0) returns 2026-05-15 00:00:00+00 — the correct date.

The fix: UpdateRecord processor divides by 1,000 before PutDatabaseRecord

The fix had two components. The primary fix was inserting an UpdateRecord processor between SplitJson and PutDatabaseRecord in the NiFi flow. The UpdateRecord processor uses NiFi Expression Language to transform field values: the property /created_at was set to the expression ${field.value:dividedBy(1000)}, which divides the epoch microsecond LONG by 1,000 to produce an epoch millisecond LONG. PutDatabaseRecord then receives a LONG field in the correct unit and calls new Timestamp(epoch_milliseconds), which produces the correct Java Timestamp and PostgreSQL TIMESTAMP value.

-- NiFi flow change: insert UpdateRecord between SplitJson and PutDatabaseRecord.
-- UpdateRecord configuration:
--   Record Reader: JsonTreeReader (Infer Schema)
--   Record Writer: JsonRecordSetWriter (Infer Schema)
--   /created_at: ${field.value:dividedBy(1000)}
--     Converts epoch microseconds → epoch milliseconds.
--     PutDatabaseRecord receives a LONG in milliseconds.
--     new Timestamp(1747267200000L) = 2026-05-15T00:00:00.000Z ✓

-- Verification: after deploying the fix, process one test event and confirm:
SELECT created_at FROM events ORDER BY created_at DESC LIMIT 1;
-- Expected: 2026-10-12 14:23:07.000 (or the actual event time)
-- Previously: 56568-10-12 14:23:07.000

-- PostgreSQL backfill: recover correct timestamps for all 7.4M existing rows.
-- The wrong timestamps are stored as far-future values created by treating
-- epoch microseconds as milliseconds. The correct Unix epoch seconds can be
-- recovered by dividing the stored timestamp's epoch seconds by 1000:
--   EXTRACT(EPOCH FROM stored_wrong_timestamp) = source_epoch_microseconds / 1000
--   Correct timestamp = to_timestamp(source_epoch_microseconds / 1000000)
--                     = to_timestamp(EXTRACT(EPOCH FROM stored_wrong_timestamp) / 1000.0)

-- Run in 4 batches to avoid long transaction locks:
-- Batch 1: event_id >= 'evt_0' AND event_id < 'evt_4'
UPDATE events
SET created_at = to_timestamp(EXTRACT(EPOCH FROM created_at) / 1000.0)
WHERE created_at > '2000-01-01'  -- safety guard: only touch far-future timestamps
  AND event_id >= 'evt_00000000' AND event_id < 'evt_40000000';
-- ~1.9M rows updated, ~18 seconds

-- Batch 2:
UPDATE events
SET created_at = to_timestamp(EXTRACT(EPOCH FROM created_at) / 1000.0)
WHERE created_at > '2000-01-01'
  AND event_id >= 'evt_40000000' AND event_id < 'evt_80000000';
-- ~1.9M rows updated, ~19 seconds

-- Batches 3 and 4: similar ranges covering remaining 3.6M rows

-- Post-backfill verification:
SELECT MIN(created_at), MAX(created_at) FROM events;
-- 2026-05-15 00:00:00  |  2026-10-12 23:59:58
-- (covers the 149-day period from pipeline launch to date of fix)

SELECT date_trunc('day', created_at) AS day, COUNT(*) AS event_count
FROM events
WHERE created_at BETWEEN '2026-05-15' AND '2026-10-13'
GROUP BY day ORDER BY day;
-- Returns 149 rows with event counts between 46,000 and 53,000 per day ✓

The backfill query ran in four batches to minimize lock contention on the production table. Each batch updated approximately 1.9 million rows in 18–22 seconds, for a total backfill time of approximately 80 seconds across all four batches. After the backfill, SELECT MIN(created_at), MAX(created_at) FROM events returned 2026-05-15 00:00:00 and 2026-10-12 23:59:58 — the correct range for the 149 days the pipeline had been running. The quarterly dashboard query returned 7,450,000 rows distributed across 149 daily buckets, with event counts between 46,000 and 53,000 per day consistent with the expected product usage volumes.

Why epoch unit mismatch is a recurring NiFi retainer pattern

The Holloway Event Analytics incident is an instance of a failure class that is completely invisible in NiFi’s operational observability. The NiFi canvas shows green across all processors. The FlowFile provenance shows success for every record. The processor bulletins show no warnings. The PostgreSQL table shows a growing row count. The only signal is the wrong timestamp values in the database — and those are visible only if someone queries with a date-range filter and compares the result to the expected row count. A developer building a new dashboard for the first time, or an analyst running a first-time query against the events table, is likely to see zero rows and assume a dashboard configuration error rather than a data pipeline timestamp unit error.

The root cause is structural: JsonTreeReader’s schema inference cannot distinguish between a numeric field that represents a count, an ID, or a timestamp — it infers the smallest integer type that fits the value. PutDatabaseRecord’s type conversion from LONG to TIMESTAMP applies the Java Timestamp(long) constructor, which assumes the value is in milliseconds. Any source system that uses epoch microseconds (common in Go, Python time.time_ns() // 1000, and high-precision event logging systems) or epoch nanoseconds (common in time-series databases and Go time.Now().UnixNano()) will produce timestamps far in the future when processed by PutDatabaseRecord without explicit unit conversion.

An Apache NiFi developer retainer who audits the epoch unit of all timestamp fields in source systems before configuring PutDatabaseRecord — and adds an UpdateRecord processor to normalize to milliseconds whenever the source uses microseconds or nanoseconds — prevents this class of silent data corruption. The specific validation that catches the bug before it reaches production: after deploying or modifying any NiFi flow that writes timestamps to a database, run SELECT MIN(created_at), MAX(created_at) FROM target_table and confirm the date range is within the expected period.

The 5.0-hour engagement covered investigation (1.5h — 0.5h source API documentation review: created_at confirmed as epoch microseconds; 0.25h NiFi FlowFile provenance inspection: raw created_at value 1747267200000000 (16 digits) in source JSON; 0.25h JsonTreeReader schema inference audit via NiFi UI Schema tab: field inferred as LONG; 0.25h Java JShell reproduction: new Timestamp(1747267200000000L) returns year 57,307 AD; 0.25h PostgreSQL timestamp range check: SELECT MIN(created_at), MAX(created_at) returns 56561-05-15 to 56568-10-10; backfill math verified: to_timestamp(EXTRACT(EPOCH FROM '56561-05-15'::timestamp) / 1000.0) returns 2026-05-15 ✓), fix implementation (2.0h — 0.5h UpdateRecord processor added: /created_at: ${field.value:dividedBy(1000)}; 0.25h test event processed through fixed flow: SELECT created_at FROM events ORDER BY created_at DESC LIMIT 1 returns 2026-10-12 14:23:07 ✓; 1.25h PostgreSQL backfill in 4 batches: 7,450,000 rows updated in ~80 seconds total; MIN(created_at) 2026-05-15, MAX(created_at) 2026-10-12 confirmed), and validation (1.5h — 0.5h quarterly dashboard query: 149 daily buckets, 46,000–53,000 events per day ✓; 0.5h weekly trend analysis: event counts consistent with expected product usage growth trajectory; 0.5h forward monitoring: NiFi UpdateRecord confirmed applying dividedBy(1000) to all incoming created_at values; next 24-hour event batch confirmed with MIN/MAX check in correct date range).

In a HourTab retainer log, the incident session reads: “product-events-pipeline timestamp unit mismatch investigation: NiFi 1.25.0, OpenJDK 17, PostgreSQL 16.2; InvokeHTTP → SplitJson → PutDatabaseRecord; source API sends created_at as Unix epoch microseconds (16-digit LONG, e.g., 1747267200000000); JsonTreeReader schema inference assigns type LONG; PutDatabaseRecord calls new java.sql.Timestamp(1747267200000000L) treating microseconds as milliseconds; Timestamp(1747267200000000L) = 1,747,267,200,000 seconds from epoch = year 57,307 AD; PostgreSQL TIMESTAMP accepts without error; NiFi flow routes all FlowFiles to success; 149 days (May 15–October 10 2026) of events stored with year 56,000+ timestamps; 7,450,000 rows affected; all dashboard date-range queries return 0 rows; discovery October 10 2026 quarterly dashboard query: 0 rows for 2026; MIN/MAX returns 56561-05-15 to 56568-10-10; fix: UpdateRecord processor added /created_at: \${field.value:dividedBy(1000)} before PutDatabaseRecord; PostgreSQL backfill: UPDATE events SET created_at = to_timestamp(EXTRACT(EPOCH FROM created_at) / 1000.0) in 4 batches; 7,450,000 rows corrected; dashboard query returns 149 days of correct event counts; 5.0h.”

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: Apache NiFi developer retainers

See also: Feast developer retainer · Kafka developer retainer · Apache Airflow developer retainer