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,

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


1 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 (
, 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):

     , (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:

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

  • That looks great, trying it out now! Also thanks for the tip on user and using GENERATED ALWAYS AS IDENTITY instead of SERIAL. I had to look it up, but seems that SERIAL is discouraged.
    – user244938
    Dec 18, 2021 at 9:35
  • Can you share links to how timetz is broken and/or discouraged? Jun 10 at 4:42
  • @JesusGalvan: The full quote from the manual is in the linked answer: stackoverflow.com/a/13243029/939860. Basically, DST breaks the concept of timetz. While you don't know the exact date, timetz is ambiguous. Jun 10 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.