The JMA Evening Update Nulls Today's Temperature

The short version

Japan’s JMA forecast API drops the temperature for “today” from the forecast in its 17:00 evening update. Weather code, weather text and rain chance still appear in the evening update, but today’s high and low have already passed their observation time and are no longer provided as forecast values.

Overwrite that absence naively with ON CONFLICT ... DO UPDATE and today’s temperature, already fetched in the morning update, is erased with null. The fix is to make the upsert’s conflict update use COALESCE(excluded.col, existing col) for the temperature alone, keeping the existing value when the new one is null.

What it looks like

On the signage board, only the high and low for “today” show as “— / —”. The weather icon and rain chance are correct. It is not a fault but a matter of how the data looks, so nothing appears in the error logs.

Why

Forecast fetching runs on a schedule, upserting one region and one target date at a time with upsertWeatherForecast, keyed uniquely on (area_code, source, forecast_date). The morning fetch puts today’s temperature in with correct values.

But in JMA’s 17:00 (evening) update, today’s weather code, weather text and rain chance continue to be provided while today’s temperature drops out of the forecast (the day’s high has already been observed, so issuing it as a forecast is meaningless). The parser passes that absence straight through as tempMin: null, tempMax: null on the row for the same target date.

The problem starts here: this evening refetch is also an upsert against the same (area_code, source, forecast_date). The conflict update (onConflictDoUpdate) was overwriting the temperature columns unconditionally with the new values, so the real temperature stored that morning is erased by the evening’s null fetch. The display layer is only rendering null correctly as “—”; there is no defect there. The cause is that the upsert’s conflict resolution was written on the premise “the latest fetch is always authoritative”.

Fixing it

In the upsert’s conflict update, make the temperature alone COALESCE(excluded.temp_x, existing temp_x). Use the existing value only when the new one is null, and overwrite as before when a non-null new value arrives.

.onConflictDoUpdate({
  target: [weatherForecasts.areaCode, weatherForecasts.source, weatherForecasts.forecastDate],
  set: {
    weatherCode: input.weatherCode ?? null,
    weatherText: input.weatherText ?? null,
    // Keep the existing temperature when the new value is null.
    // excluded.temp_x = this INSERT's value, weatherForecasts.temp_x = the existing row's value.
    tempMin: sql`coalesce(excluded.${sql.raw(weatherForecasts.tempMin.name)}, ${weatherForecasts.tempMin})`,
    tempMax: sql`coalesce(excluded.${sql.raw(weatherForecasts.tempMax.name)}, ${weatherForecasts.tempMax})`,
    pop: input.pop ?? null,
    // ...
  },
})

Narrowing this to the temperature alone is the point. Weather code, weather text and rain chance are always included in the evening update even for today, so those may be overwritten with the new values as before. Unless you pick out “only the fields that can go missing” to retain, genuinely new information (a correction or an update) gets frozen at the old value too.

There is no worry about a stale temperature persisting either. A target date’s forecast drops out of the read query’s scope once the date has passed, so a retained value is never displayed on subsequent days.

Why it wasn’t noticed

This bug produces no error. The fetch job finishes normally, the DB write succeeds, and the display layer renders null as “—” exactly as specified. Every layer is correctly discharging its own responsibility, and the temperature quietly disappears.

We noticed by paying attention to the shape of the operation itself — the same target date being fetched twice, around 17:00. The blind spot was that the upsert’s implicit premise, “a later fetch is always newer and more correct than an earlier one”, does not hold field by field for some external APIs. It is a shape common to any cache that fetches periodically and writes to a DB, so the fix is recorded as a documentation comment on the implementation, to return to next time a similar API integration is written.