Jump to content

Combining and comparing date and time

Recommended Posts


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,



Link to post
Share on other sites

what does your INSERT look like?


here's a link that I refer to from time to time that might help:


still need to see your INSERT 


why no ID in your table?


Edited by niche
Link to post
Share on other sites

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 by Edreyn
Link to post
Share on other sites

here's how you do an INSERT:


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:




Link to post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...