Heroku TZ environment variable limits regarding database timestamp synchronization

Answer

The TZ config var on Heroku only changes the operating‑system timezone seen by processes. It does not automatically set the JVM default timezone, nor does it directly tell JDBC drivers to use that zone. Whether the driver interprets java.sql.Timestamp values in the dyno’s local time or in UTC depends on whether the JVM’s default timezone has been set to match TZ.

When drift can occur

• If the JVM default timezone is left at its Heroku default (UTC) – which happens when you neither launch the dyno with -Duser.timezone nor call TimeZone.setDefault in code – most Java JDBC drivers (PostgreSQL, MySQL, etc.) will convert timestamps to/from the database using UTC. In this case changing TZ has no effect on stored values and there is no drift risk.
• If you explicitly set the JVM default timezone to the value of TZ (e.g., via -Duser.timezone=$TZ in the Procfile or by calling TimeZone.setDefault at startup), the driver will use that zone when constructing a java.sql.Timestamp without an explicit offset. The raw value sent to PostgreSQL’s TIMESTAMP WITHOUT TIME ZONE column will then reflect the dyno’s local wall‑clock time. Because the database session remains in UTC (Heroku‑managed Postgres defaults to UTC), the stored raw value will be offset from UTC, and a later change to TZ will cause new writes to use a different offset while existing rows keep the old offset – apparent data drift.

Confirmed facts from the research

• The TZ var only changes the OS timezone; it does not automatically set the JVM default timezone.
• Most Java JDBC drivers use the JVM default timezone to convert java.sql.Timestamp values when no explicit timezone is supplied in the connection URL.
• For PostgreSQL, TIMESTAMP WITHOUT TIME ZONE stores the raw wall‑clock value; if the driver applied the JVM timezone when building the timestamp, that raw value will reflect the dyno’s TZ.
• Changing TZ after data has been written can cause apparent drift unless the driver or connection is forced to use UTC.
• Forcing the connection to use UTC (e.g., PostgreSQL JDBC parameter -c timezone=UTC or Hibernate property hibernate.jdbc.time_zone=UTC) eliminates drift regardless of the dyno’s TZ.

Steps to verify and eliminate drift for your app

1. Check the JVM default timezone at runtime:

   System.out.println(TimeZone.getDefault().getID());

   If it prints UTC, the TZ var is not influencing the driver.
2. Confirm the database session timezone (run once via heroku pg:psql):

   SHOW timezone;

   Expect UTC for a standard Heroku Postgres.
3. Insert a test timestamp using plain JDBC (or Hibernate) without explicit offset:

   Timestamp ts = new Timestamp(Instant.now().toEpochMilli());
       // execute INSERT ... VALUES (?, ...)

   Then query the raw column value and compare it to Instant.now().toEpochMilli(). If they match, the driver is using UTC; if they differ by the offset of your TZ, the driver is using the JVM timezone.
4. If you see a mismatch and want to avoid drift:
   • Option A: Keep the JVM default as UTC (do not set -Duser.timezone or TimeZone.setDefault) and rely on the driver’s UTC conversion.
   • Option B: Explicitly force UTC in the JDBC connection (e.g., add -c timezone=UTC to the PostgreSQL connection URL) or set Hibernate’s hibernate.jdbc.time_zone=UTC.

Missing diagnostic detail that would change the recommendation

Do you currently set the JVM default timezone to match the TZ variable (via -Duser.timezone in your Procfile or by calling TimeZone.setDefault at startup)? If the answer is “no”, then the TZ var has no effect on JDBC timestamp handling and drift is not a risk; if “yes”, you must apply one of the UTC‑forcing steps above to prevent drift when TZ changes.