ERROR: invalid input syntax for type timestamp with time zone in AT TIME ZONE conversion

Answer

PostgreSQL does not provide a configuration option or function to choose the second offset for an ambiguous local time during a DST fallback. The AT TIME ZONE operator (and the equivalent timezone function) always picks the first matching offset found in the zoneinfo database for the given zone.

Confirmed facts

• The behavior is implementation‑defined and may change when the underlying zoneinfo data is updated.
• There is no GUC, session setting, or built‑in flag to select the alternative offset.
• Storing timestamps as timestamptz in UTC and converting only for presentation avoids the ambiguity.

Steps to resolve the invalid input syntax for type timestamp with time zone error

1. Identify the expression that feeds AT TIME ZONE (e.g., a column, a cast, or a string literal).
2. Ensure it is a valid timestamp or timestamptz value. If it is a text literal, cast it explicitly: my_text::timestamp.
3. If the value represents a local time that could be ambiguous, disambiguate it by attaching an explicit UTC offset, e.g. '2023-11-05 01:30:00-05'::timestamptz.
4. Then apply the zone conversion: (my_value) AT TIME ZONE 'America/New_York' (if you started with a timestamp) or my_timestamptz AT TIME ZONE 'UTC' to get a wall‑clock time.
5. Test with a known good value: SELECT '2024-01-01 12:00:00'::timestamp AT TIME ZONE 'UTC'; should return a timestamptz without error.
6. Replace the problematic expression with the verified pattern and re‑run the original query.

Best practices for deterministic conversion

• Store all points in time as timestamptz (UTC) and only convert to local zone for display.
• When you must accept wall‑clock input from users, ask them to specify the offset or use a dropdown that includes the zone name and let the application decide which offset to store (e.g., always store the earlier offset).
• Validate input against a list of known unambiguous timestamps or reject times that fall within the DST fallback hour unless you have a rule to pick an offset.
• Pin the version of the IANA timezone database used by your PostgreSQL cluster (e.g., via pg_timezone_names or the OS package) and test upgrades in a staging environment.
• If you need to support both offsets, store two separate timestamptz values or keep the original string and the chosen offset alongside it.