If I set an alarm on my phone for 6am, I want it to go off at 6am in whatever time zone I happen to be in, even if I travel after setting the alarm.
If I set an alarm on my phone for 6am, I want it to go off at 6am in whatever time zone I happen to be in, even if I travel after setting the alarm.
However it's not a postgres "timestamp with timezone", which (as both TFA and fphilipe's article note) doesn't store a timezone.
The problem of "future event in a local place" is why the "store everything as UTC" trend is wrong, incidentally: if the timezone's offset changes (which happens regularly, sometimes with very little warning) the stored time is now completely off, by minutes to days[0] depending on the change. "Store everything as UTC" is only suitable if you're only ever storing past events (timezones don't normally get retroactively altered).
[0] https://www.timeanddate.com/news/time/samoa-dateline.html
Basically, a timestamp should be tied to a timezone or a location, or at worst an indirection (like you being at a specific location). And since we don't tend to have practical support for tying timestamps to locations, we tie them to timezones instead.
IMO, in a database you should store the "natural" representation of date-time as your users think of it. Sometimes (like your alarm clock), it's timezone-less concept. Sometimes (like a sporting event) it's a combination of datetime + location. Sometimes (like historical events) that's a fixed instant in time. Recognize which is which and model appropriately.