For an instructor lead, in-depth look at learning SQL click below.
In this blog post, we will learn how to build a simple yet powerful time tracking application using SQL. From understanding task durations to managing time spent on various project tasks, a time tracking app can be an invaluable tool in both personal organization and professional project management. As always, we’ll start by setting up the database structure first.
Part 1: Designing the Database
For our application, we’ll need three tables: Users, Tasks, and TimeLogs.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE Users ( UserID INT PRIMARY KEY, UserName VARCHAR(100) ); CREATE TABLE Tasks ( TaskID INT PRIMARY KEY, UserID INT FOREIGN KEY REFERENCES Users(UserID), Description VARCHAR(250) ); CREATE TABLE TimeLogs ( LogID INT PRIMARY KEY, TaskID INT FOREIGN KEY REFERENCES Tasks(TaskID), Start_time DATETIME, End_time DATETIME ); |
Part 2: Querying the Data
There are countless queries that can help us retrieve meaningful data for our time tracking application. For demonstration, let’s create some queries to retrieve task durations and user’s total time spent.
Getting Duration of Each Task:
|
1 2 3 4 |
SELECT TaskID, DATEDIFF(MINUTE, Start_time, End_time) AS Duration FROM TimeLogs; |
Getting Total Time Spent by User:
|
1 2 3 4 5 6 7 |
SELECT Users.UserName, SUM(DATEDIFF(MINUTE, TimeLogs.Start_time, TimeLogs.End_time)) as TotalTime FROM Users JOIN Tasks ON Users.UserID = Tasks.UserID JOIN TimeLogs ON Tasks.TaskID = TimeLogs.TaskID GROUP BY Users.UserName; |
Part 3: Updating and Deleting Records
Adding, deleting, or updating time logs should be a common operation in our time tracking application. Here’s how to do it in SQL:
Inserting a New Time Log:
|
1 2 3 4 |
INSERT INTO TimeLogs (TaskID, Start_time, End_time) VALUES (1, '2022-05-01 09:00:00', '2022-05-01 11:00:00'); |
Updating an Existing Time Log:
|
1 2 3 4 5 |
UPDATE TimeLogs SET End_time = '2022-05-01 12:00:00' WHERE LogID = 1; |
Deleting a Time Log:
|
1 2 3 4 |
DELETE FROM TimeLogs WHERE LogID = 1; |
Conclusion
There you have it! This is just a basic overview of how you can build a time tracking application using SQL. Of course, this simple app can be extended in many ways. The possibilities are endless when you begin to include more features, tables, and relationships between them.