Jump to content

Combining and comparing date and time


Edreyn
 Share

Recommended Posts

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

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 by niche
Link to comment
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 comment
Share on other sites

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

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

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...