Execution of a query according to timeout

Navigation
0

Very good friends I wanted to know if you can help me with an idea to solve a question.

I would like to calculate the interests that a client has every certain amount of time (24 hrs for example).

I have the idea of doing a Timer Out Global, and when the clock reaches 0, make an UPDATE to all the clients that are in my database. 

CREATE TABLE ahorro(
ahorro_id int not null PRIMARY key AUTO_INCREMENT,
user_id int not null,
user_uid int not null,
capital_real float(11,2) not null,
monto float(11,2) not null,
interes_recibidos float(11,2) not null,
capital_total float(11,2) not null,
hist_date datetime not null default CURRENT_TIMESTAMP
);

where:

amount: It is the amount that the client will enter.

capital_real: It is the sum of the amounts that the user has entered.

interes_recibidos: It is (capital_real * 15 * 1/36000).

capital_total: It is the summation (capital_real + interestreceived).

My question is if I can execute everything in a certain amount of time. and if it is possible, there is a way to update the columns (capital_real, interes_recibidos, capital_total) at the same time.

Greetings, thank you for your time.

    
asked by Nor 23.12.2018 в 22:53
source

2 answers

0

What you can do, and I think it would be more correct is to create a scheduled task in your database. In this way you can specify an action to be performed after a certain time has elapsed. You should follow the following steps:

  • First we have to make sure that we have the scheduler started this we see it with a: mysql> SHOW processlist;

    • It is not booted, for this we have to modify a parameter of my.cnf in the section mysqld: event-scheduler=ON . We restart mysql and we can verify that:

    Now we create the event, following the syntax: 

    CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

    For our case: 

    CREATE event evento_intereses ON schedule every 24 hour do (Aquí van las acciones que necesites realizar, ya sea un update, llamar a un sp, etc.);

    I hope it serves you. Greetings!

        
    answered by 24.12.2018 / 01:58
    source
    0

    For this type of solution, I recommend using Crontab or using a procedure in the database that updates the fields you need.

    On server:

    If you have access to the server, the best thing would be to make a Script.php that does the following:

  • Connect to database
  • Request data and apply new calculation
  • Update changes

    • After doing the Script.php you have to give execution permissions with the command 'chmod', for example: 

    chmod a+x script.php

    And then add the Script to your server with 'crontab', for that you write in the terminal: 

    crontab -e

    And at the end of the document you add the timer (username is the username of the server): 

    5 3 * * * /usr/bin/php /home/username/script.php

    The previous line will execute the script at 3:05 AM. This example has the time defined, then the interpreter and then the path of the script to execute.

    As a datum, with this website you can check the configuration of your Cron: link

    And if you want to find the path of your interpreter in PHP you can use the following command in your terminal: 

    which php

    If you plan to do a Javascript timer in the view I think it can be risky. And if you do not have access to the server to do a cron then a database-based procedure may be the most viable.

    Now if you use Node.js then you can make a Javascript script and use this Library for the Crontab: link

        
    answered by 24.12.2018 в 04:13
    Openpyxl boxes with formulas RealmSwift Dependency in pod spec