thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 15 of 20 Intermediate Interpretation

How can timestamp with time zone shift the date a sample appears to have been collected?

Short answer

PostgreSQL stores a timestamp with time zone as an instant in UTC and displays it in the session's time zone. Casting it to a date uses that session time zone, so the same sample can fall on different days for users or servers in different zones.

Why

The PostgreSQL documentation says a timestamp with time zone value is converted to UTC on input, the original zone is not retained, and on output it is converted to whatever the current TimeZone setting is. That is the right design for recording an instant, but it surprises people who read the value as a calendar date. A blood sample drawn at 23:30 in New York on 15 January 2026 is stored as 04:30 UTC on 16 January.

A report run with the session set to UTC that groups by collected_at::date counts it on 16 January, while the same report run in New York counts it on 15 January. Daily enrollment counts, stability time points and visit windows can all move by a day this way. The column type without time zone has the opposite risk, because it stores the wall-clock reading and loses which zone it came from.

Interpret dates deliberately: decide which zone defines the study day, convert explicitly with AT TIME ZONE and the site's named IANA zone before taking the date, and record the site's zone alongside the instant. For true calendar facts such as a date of birth, a plain date column avoids the issue altogether.

A strong answer covers

  • Stored as UTC, original zone not kept
  • Displayed and cast in the session TimeZone
  • Date boundaries move with the zone
  • Convert with AT TIME ZONE before taking the date
  • Use a plain date type for calendar facts

Common mistakes

  • Assuming timestamp with time zone keeps the zone it was entered in
  • Grouping by a cast to date without fixing the session or the zone in the query