https://supabase.com/ logo
Join Discord
Powered by
Hi all, I'm not great with databases and have a db...
# help
s
Hi all, I'm not great with databases and have a db design question. I have a table for tasks, and a table for sessions (in the frontend you can start a timer, and the session has a task id, start time, and stop time). I'd like to have a "total time spent" feature in my frontend, but I'm wondering how to calculate that from my database. Should I create a new property and update it each time the value changes, or is there a special query I can make that will calculate it without much overhead? I feel like the first approach is a bit hacky, but I'm scared that querying the database so many times just to get each tasks "total time spent" is a bit much. Any input/help would be appreciated!
s
You can create a view with the calculated difference, I'm just not too sure how this will work if data is missing, like the stop time for instance, another option is to use a computed column. To calculate the difference you can store the start and stop time as timestamps then follow this https://learnsql.com/cookbook/how-to-calculate-the-difference-between-two-timestamps-in-postgresql
s
What do you mean create a view with the calculated difference, is that different than what I mentioned doing? Sorry if that's a dumb question, just a bit confused
s
Ah sorry, yeah I was giving two different options, so a Postgres view is something you would create in the SQL editor in the Supabase UI, you can query those the same way how you query a normal table with the supabase-js library.
s
Because I do want a view where a user can see how much time was spent in total on a specific task. The problem is, my current solution would be having to query the db for each session with a task_id of that tasks id, then calculating the amount of time spent, which is fine but I'm wondering if it'd be more efficient to just save it as a total and update that each time a "session" is created, or if there's some other solution I'm not aware of
Ah I see what you mean. Where can I learn more about those views you mentioned and/or computed columns? Just searched the supabase docs for that but nothing came up
s
Yeah these are Postgres features, so not specific to Supabase, you can read more about views here https://www.postgresqltutorial.com/postgresql-views/
I'm going to create a quick test in my playground and see if views will work for this
s
Thanks!
s
Yes a view will work as it updates based on the underlying query
So I created a table similar to what you describe except I don't have a task_id relationship in mine
Copy code
sql
-- Table Definition
CREATE TABLE "public"."sessions" (
    "id" int8 NOT NULL,
    "created_at" timestamptz DEFAULT now(),
    "end_at" timestamptz,
    "task_id" int8,
    PRIMARY KEY ("id")
);
and from this I create a View called 
sessions_difference
, you can call it whatever you please
Copy code
sql
CREATE VIEW sessions_difference AS 
SELECT
  id,
  task_id,
  created_at,
  end_at,
  end_at - created_at AS difference
FROM sessions;
In this view I am creating a select query against the sessions table with a column that is calculated during the query which is the 
end_at - created_at AS difference
part of the script. Now in javascript you can just call this view as you would a normal table
Copy code
js
const { data, error } = await supabase.from('sessions_difference')
  .select('*')
  .eq('task_id', taskId);
In the instance above 
taskId
would be the variable name in your code of the task id.
Something to note about this is that row level security (RLS) won't be available on this view, so if you have data that shouldn't be shared between different users or anything like that then this might not be a good option
s
whoaaa didn't even know this exists, thank you so much @User 🙂
unfortunately I believe this would have to have RLS enabled because I want users to only be able to see their own sessions
Where could I learn more about computed columns? Getting mixed results when searching, some of it which has super verbose language that I don't get
s
I think this stack overflow answer is a good place to start reading about computed columns. https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql You can create a computed column on creation of the table using the SQL editor, example below
Copy code
sql
CREATE TABLE "public"."sessions" (
    "id" BIGSERIAL NOT NULL,
    "created_at" timestamptz DEFAULT now(),
    "end_at" timestamptz,
    "task_id" int8,
    "difference" interval GENERATED ALWAYS AS (end_at - created_at) STORED,
    PRIMARY KEY ("id")
);
In this example the computed property will always generate the difference once both values are in place, if you don't have a end_at the difference column will be NULL
There is a issue with this that if you didn't provide both the created_at and end_at value when you do a INSERT on the table, you won't be able to do an UPDATE to set the end_at value as this will error out.
And another options is to create the computed column using a function, this will allow for UPDATE to happen too.
Copy code
sql
-- create table without the total_time column
CREATE TABLE "public"."sessions" (
    "id" BIGSERIAL NOT NULL,
    "created_at" timestamptz DEFAULT now(),
    "end_at" timestamptz,
    "task_id" int8,
    PRIMARY KEY ("id")
);

-- create the total_time computed column using a function
CREATE FUNCTION total_time(sessions)
    RETURNS interval
    LANGUAGE sql AS
$$
    SELECT SUM(end_at - created_at)
    FROM public.sessions ps
    WHERE ps.id = $1.id
$$;

-- querying the database whilst referring that column
SELECT id, created_at, end_at, task_id, sessions.total_time
FROM public.sessions
You should be able to call this the same in JS now
Copy code
js
const { data, error } = await supabase.from('session')
  .select(`id, created_at, end_at, task_id, total_time`);
s
@User Thanks, I'll give that a shot. I just started reading through the pgpsql docs because I found a video from a supabse team member talking about functions, and the SQL ones are straightforward enough but I couldn't get this pgpsql one to work
Copy code
Yeah there may be times where end_at isn't defined so that doesn't work. I'm trying to piece together a function by reading the pgpsql docs because I found a video about it from a supabase team member and I honestly didn't even know this is a thing (I try to avoid db stuff which is why I'm using supabase in the first place lol). Could you help me understand why I'm getting date/time field value out of range: "1644782218932" when I run the following?
create or replace function getTotalTimeSpentOnAction(action_id int) returns integer as $$
  declare
    sesh sessions;
    time_spent int default 0;
  begin
    for sesh in
    select start, stop
    from sessions where sessions.action = action_id
    loop
      time_spent = time_spent + (sesh.stop - sesh.start);
    end loop;
    return time_spent;
  end;
$$ language plpgsql;
What you're suggesting looks much easier though, so I'll try that out
s
A Postgres function would work too if you wanted to just call it via rpc instead of querying the table.
s
I don't know what rpc is 😅
I barely know sql, I just know the super super basics of it and understanding programming concepts of enough to piece things together, but I don't know the syntax that well, let alone pgpsql stuff
s
I'm so sorry for using all these acronyms, its remote procedure call, which in the Supabase sense is calling a Postgres function from the supabase-js library. The library has a method called 
.rpc
https://supabase.com/docs/reference/javascript/rpc
s
> I'm so sorry for using all these acronyms No need to be sorry, I really appreciate the help. Thanks for pointing that out though, I'll keep that in mind going forward as I believe I have a couple more features for my app that I'd like to have db functions for as opposed to server side logic.
s
I find this website to be really good when learning postgres features and SQL in general https://www.postgresqltutorial.com/, the postgres documentation is good but It feels like information overload for me.
s
awesome, thanks!
PreviousNext
Powered by