Combining and comparing date and time

[Edreyn]

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

 

[niche]

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

[Edreyn]

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

[niche]

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

 

 

[Edreyn]

(Previous message updated)

[niche]

https://stackoverflow.com/questions/27088849/how-to-insert-a-calculated-sum-in-a-table

see the 2nd answer 

[justsomeguy]

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.

[Edreyn]

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

[Ingolme]

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

[Edreyn]

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.

[Ingolme]

The end time can be calculated by a server-side programming language prior to being added to the database.

[Edreyn]

Okay, thanks for reply.