Timezone aware uniqueness constraint-CodePudding

I am working with a timesheet app, used by users from multiple timezones. I am trying to introduce a unique constraint, that only allows users to clock_in or clock_out once per day in the local timezone.

Please refer to the following table declaration:

Table "public.entries"
---------------------------------------------
  Column   |              Type              |
---------------------------------------------
id         | bigint                         |
user_id    | bigint                         |
entry_type | string                         | enum(clock_in, clock_out)
created_at | timestamp(6) without time zone |

But little lost on how to handle the timezone-aware uniqueness.

Update:

I am considering 0:00 hrs to 23:55 hrs of local time zone as day.
User's timezone is stored in the users table but can move to the entries table if it helps with constraints.

CodePudding user response：

First off, I suggest using timestamp with timezone aka timestamptz for the created_at column. As mentioned in the comment, this doesn't actually store a timezone, just stores the UTC time and defaults to an output formatting that includes the global system timezone. It's confusing.

This illustrates the nonsense of timestamp without timezone:

postgres=# select now()::timestamptz;
              now
-------------------------------
 2022-04-11 01:45:40.559639-07  -- the actual current time, with Pacific offset, yes I'm up late
(1 row)

postgres=# select now()::timestamp;
            now
----------------------------
 2022-04-11 01:45:38.801277   -- 01:45 Pacific but looks like UTC, so you'll get the wrong time
(1 row)

Now your application needs to obtain the user's timestamp somehow based on their location, then you can store it in a separate TEXT column or something. Then you add a uniqueness constraint on the tuple (created_at, timezone).

CodePudding user response：

Firstly, you'll probably want to use a native datetime datatype and a range one at that, e.g. tstzrange (with timezone) / tsrange (without timezone) – they allow you to natively store a start and end time – see https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

You can optionally add an exclusion constraint to ensure no two shifts overlap – see: https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT if that's all you really want to ensure, then that might be enough.

If you definitely want to ensure there's only one shift starting or ending per day, you can use a function to derive a unique index:

create unique index INDEX_NAME on TABLE_NAME (date_trunc('day', lower(column_name)))

For your example specifically:

create unique index idx_unique_shift_start_time on entries (user_id, date_trunc('day', lower(active_during)))

create unique index idx_unique_shift_end_time on entries (user_id, date_trunc('day', upper(active_during)))

These two indexes take the lower or upper bounds of the range (i.e. the start time or end time), then truncate to just the day (i.e. drop the hours, minutes, seconds etc) and then combine with the user_id to give us a unique key.