How does PostgreSQL's AT TIME ZONE handle daylight saving transitions when converting UTC timestamps to a regional time zone on DigitalOcean Managed Databases?

Answer to the core question

When you run SELECT value AT TIME ZONE 'zone' on a timestamptz that is already UTC‑aware, PostgreSQL:

• treats the input instant as UTC.
• applies the offset that the target time‑zone (from the IANA tz database) has for that instant.
• returns a timestamp (no time‑zone) that represents the local wall‑clock time.

Key behaviours

1. Input type matters – timestamptz AT TIME ZONE zone returns timestamp with the earlier offset in ambiguous hours; timestamp AT TIME ZONE zone returns timestamptz and uses the server’s current time zone setting.
2. Ambiguous hour handling – the operator implicitly resolves the ambiguity by picking the earlier offset. To see both possibilities you can:

   SELECT '2023-11-05 01:30:00+00'::timestamptz AT TIME ZONE 'America/New_York';  -- EST (earlier)
   SELECT ('2023-11-05 01:30:00+00'::timestamptz + INTERVAL '1 hour') AT TIME ZONE 'America/New_York';  -- EDT (later)
   

3. Session settings – the AT TIME ZONE operator is independent of the session’s timezone GUC. It uses only the zone name you supply. The only session‑level setting that can influence the result indirectly is lc_time for locale‑specific formatting, not the conversion itself.
4. DigitalOcean Managed DBs – they run the same PostgreSQL releases as on‑prem installations (currently 15/16) and keep the tzdata package up‑to‑date. Therefore the DST rules applied by AT TIME ZONE are identical to a local PostgreSQL server.

Practical verification on DigitalOcean

• Check the PostgreSQL version:

  SELECT version();
  

• Verify the time‑zone data for the zone you care about:

  SELECT * FROM pg_timezone_names WHERE name = 'America/New_York';
  

• Test the conversion around a DST transition:

  -- Spring forward (gap)
  SELECT '2023-03-12 07:30:00+00'::timestamptz AT TIME ZONE 'America/New_York';  -- 2023-03-12 03:30:00-04 (EDT)
  
  -- Fall back (ambiguous hour)
  SELECT '2023-11-05 01:30:00+00'::timestamptz AT TIME ZONE 'America/New_York';  -- 2023-11-05 01:30:00-05 (EST)
  

Recommendation for your use‑case

Store all event timestamps as timestamptz in UTC. When you need to present them in a user’s local zone, simply apply:

SELECT event_ts AT TIME ZONE 'America/New_York' AS local_time
FROM events;

Missing diagnostic detail

Could you let me know which PostgreSQL version your DigitalOcean Managed Database is running? That will confirm the exact tzdata release and help verify the DST rules you see.