Edreyn Posted January 22, 2018 Share Posted January 22, 2018 Hello, I am trying to do something similar to what is used in browser MMOs. CREATE TABLE Processes ( Name varchar(255) NOT NULL UNIQUE, StartTime TIMESTAMP, DurationTime TIMESTAMP, EndTime TIMESTAMP, CurrentTime TIMESTAMP, IsFinished int, PRIMARY KEY (Name) ); Specifically: I have a process, that has a StartTime and DurationTime. IsFinished starts as 0. I need: 1) When I do INSERT: Calculate the end time, the result is put into EndTime field 2) When I want to update (for now manually, not with timer): Compare CurrentTime (should be taken automatically) to EndTime. If CurrentTime is past, IsFinished turns to '1' How it can be achieved? I have some basic knowledge, but not how to add or compare timestamps. Thank you in advance, Evgenie Link to comment Share on other sites More sharing options...
niche Posted January 22, 2018 Share Posted January 22, 2018 (edited) what does your INSERT look like? EDIT: here's a link that I refer to from time to time that might help: https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql still need to see your INSERT EDIT EDIT: why no ID in your table? Edited January 22, 2018 by niche Link to comment Share on other sites More sharing options...
Edreyn Posted January 22, 2018 Author Share Posted January 22, 2018 (edited) I don't have an insert for my situation, I don't even know how it could look. I'll check the link, thank you! Sorry, I didn't read correctly. I imagine something like this: INSERT INTO Processes (StartTime, DurationTime, IsFinished) VALUES ('2018-01-22 15:31:00', '0000-00-00 00:05:00', '0'); And then i want to calculate EndTime = StartTime + DurationTime Edited January 22, 2018 by Edreyn Link to comment Share on other sites More sharing options...
niche Posted January 22, 2018 Share Posted January 22, 2018 here's how you do an INSERT: https://www.w3schools.com/sql/sql_insert.asp when i was a beginner I'd do two INSERTS. One to INSERT the original data and one to UPDATE (do the calcs) here's how you do an UPDATE: https://www.w3schools.com/sql/sql_update.asp Link to comment Share on other sites More sharing options...
Edreyn Posted January 22, 2018 Author Share Posted January 22, 2018 (Previous message updated) Link to comment Share on other sites More sharing options...
niche Posted January 22, 2018 Share Posted January 22, 2018 https://stackoverflow.com/questions/27088849/how-to-insert-a-calculated-sum-in-a-table see the 2nd answer Link to comment Share on other sites More sharing options...
justsomeguy Posted January 22, 2018 Share Posted January 22, 2018 Why do you need the end time? You have the start time and duration, you can calculate the end time with those, so why store the end time separately? I wouldn't do that, it's redundant. You wouldn't need to track whether it's finished either as long as you delete the finished jobs when they end. You also shouldn't save the current time, because you would need to update your entire database every second which is extremely inefficient. You should just have a script that runs periodically to check if the start time plus the duration (i.e., the end time) is less than the current time, which you can get dynamically. If it is, then the job is finished and you can delete it from the table. Link to comment Share on other sites More sharing options...
Edreyn Posted January 22, 2018 Author Share Posted January 22, 2018 Yes, that allowed me to add values of two columns, but I still don't get the expected result. INSERT INTO Processes(Name, StartTime, DurationTime, EndTime, IsFinished) SELECT 'AAA', st, dt, st + dt, 0 FROM (SELECT '2018-01-22 09:00:00' as st, '0000-00-00 00:05:00' as dt) Processes; The expected result for EndTime column is: '2018-01-22 09:05:00' The actual result is: '2018' Of course I declared EndTime as TIMESTAMP Link to comment Share on other sites More sharing options...
Ingolme Posted January 22, 2018 Share Posted January 22, 2018 If you want to add dates you have to use the date and time functions. Personally, I'd store start and end dates and ignore the duration, but it all depends on how you intend to select data later on. This syntax looks like it's creating a virtual table and naming it "Processes" FROM (SELECT '2018-01-22 09:00:00' as st, '0000-00-00 00:05:00' as dt) Processes; If you want to create your own values to insert, then don't use the INSERT...SELECT syntax, instead use INSERT...VALUES Link to comment Share on other sites More sharing options...
Edreyn Posted January 23, 2018 Author Share Posted January 23, 2018 Quote Personally, I'd store start and end dates and ignore the duration As I say, I am trying to do something similar to browser games. The player starts a task, for example "forge a sword". When he starts, current time should be stored. The task also has a duration, that can change depending on other variables. I need to calculate end time myself (start + duration), then check once in a while if new current time is past end time. If it is, the task is marked as finished. So, I don't insert end time myself, I need to calculate it. Link to comment Share on other sites More sharing options...
Ingolme Posted January 23, 2018 Share Posted January 23, 2018 The end time can be calculated by a server-side programming language prior to being added to the database. Link to comment Share on other sites More sharing options...
Edreyn Posted January 23, 2018 Author Share Posted January 23, 2018 Okay, thanks for reply. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now