So i'm trying to build a time tracker for reportin...
# sqlv
victorbjorklund
07/30/2021, 12:45 PMSo i'm trying to build a time tracker for reporting. How would I restrict so that a user can't have overlapping time entries? It's for tracking work so the same user can't work at double at the same time. Any constaints I can set in the DB to avoid possibility for overlapping entries from the same user?
k
Kosh
07/30/2021, 1:09 PMmaybe you could make the primary key as userId & date (string formatted yyyy-mm-dd) with this, you don't have to do any constraint otherwise u could add a constraint that check if the date entered is x days after
example
Copy code
CREATE
OR REPLACE FUNCTION public.can_insert_new_tracking_record(dd integer, created_at date) RETURNS boolean LANGUAGE plpgsql AS $$
BEGIN
return NOW() >= (created_at + make_interval(days := dd));
END;
$$;b
burggraf
07/30/2021, 1:22 PMThis depends on the granularity you're using. Time can be down to the millisecond or a year at a time, and it's important to know the smallest "block" of time you need to track.
burggraf
07/30/2021, 1:23 PMIf you're tracking shifts, like an hour at a time (or even 15 minutes at a time, or whatever) then you could simply make a primary key based on that time block, and the db wouldn't let you block two of the same time, obviously.
burggraf
07/30/2021, 1:24 PMIf it's more complex than that, you could always put your logic into a trigger, and the trigger could reject any duplicate entries.
burggraf
07/30/2021, 1:25 PMIf your time blocks cover a start time and end time range (the more complicated route) then you'd probably need a trigger.
burggraf
07/30/2021, 1:25 PMThis wouldn't be too difficult -- the trigger would just check to see if the given range has existing data and disallow the insert if so.
burggraf
07/30/2021, 1:27 PMTons of examples of how to disallow insert if a specific condition is true, such as https://stackoverflow.com/questions/22733254/prevent-insert-if-condition-is-met
v
victorbjorklund
07/30/2021, 1:42 PMGuess it will be the later because it would be one starts at 14:13 to 14:55 and another starts at 14:36 to 15:44 (that should not be allowed) so just making it the primary key won't work.
But im a bit new to supabase, can I use all the functions I can use in postgresql?
k
Kosh
07/30/2021, 1:42 PMYes
v
victorbjorklund
07/30/2021, 1:43 PMAmazing 🙂
b
burggraf
07/30/2021, 2:04 PMSo it looks like you're using minutes as your "blocks". That should still work fine. If I were to do this I'd use the SupaScript library (that I wrote) so I could write the trigger in JavaScript (and maybe even use
moment.js