Storing timezoned HH:MM in PostgreSQL

Question

My application needs to send a daily email to users at a time of their choosing, the timezone is also defined by the user itself.

What's the best way to store HH:MM + timezone? And how can I effectively query this, knowing that my server is in a specific timezone?

My current table is:

CREATE TABLE "reminder" (
    "id"            SERIAL          UNIQUE PRIMARY KEY,
    "user"          INT             NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
    "scheduled_at   TIMESTAMPTZ     NOT NULL,
    "created_at"    TIMESTAMPTZ     NOT NULL DEFAULT CURRENT_TIMESTAMP
)

I'm not sure if TIMESTAMPTZ is the most effective way to do this, since it stores the entire timestamp, and not just HH:MM

Answer 1

Store a time and the time zone name as text.
Not timetz, which is broken and discouraged, and not just a time offset, which would break with daylight saving time (DST) and other time zone oddities.

I suggest:

CREATE TABLE reminder (
  id           int          GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, user_id      int          NOT NULL REFERENCES "user"(id) ON DELETE CASCADE
, scheduled_at time         NOT NULL
, tz           text         NOT NULL DEFAULT 'UTC'
, created_at   timestamptz  NOT NULL DEFAULT now()
);

To generate correct timestamps for "today" (as defined by your current TimeZone setting):

SELECT *
     , (CURRENT_DATE + scheduled_at) AT TIME ZONE tz AS tstz
     , (CURRENT_DATE + scheduled_at) AT TIME ZONE tz AT TIME ZONE 'UTC' AS utc_ts
FROM   reminder;

The resulting tstz may be on a different "day" for the receiver.
utc_ts is the according timestamp in the UTC time zone.

This related answer on SO fits precisely and has ample explanation:

• Accounting for DST in Postgres, when selecting scheduled items

Aside: I wouldn't use the reserved word "user" as identifier.