Generate a text file based on comparison of two rows in MYSQL

Question

I have a MYSQL table which contains timestamp and direction (buy/sell signal) of stock market data.

Below is the CREATE and INSERT statement of sample data.

The table is in descending order of timestamp, and the table is truncated and reinserted at 5-minute interval. I have included a id field which is autoincremented, as it may help in comparing the first row with the second row.

Everytime the direction of the market changes, I want a text file to be generated. As an example (from sample data), when timestamp was 15:00:00, since it was the first row that was inserted to the table, it should generate a text file as SELL.txt. At 15:05:00, since the direction changed from SELL to BUY, it should generate a text file as BUY.txt. Since the direction did not change at 15:10:00 and 15:15:00 compared to the previous row, no text file should be generated. At 15:20:00, since the direction changed from BUY to SELL, it should generate a text file as SELL.txt. Since the direction did not change at 15:25:00 and 15:30:00 compared to the previous row, no text file should be generated.

In Summary, if the cell value of the first row of direction field is not equal to the cell value of the second row of direction field, then a text file has to be generated based on the value of the first row of direction field. If the cell value of the first row of direction field is equal to the cell value of the second row of direction field, then no text file has to be generated.

I am assuming this can be implemented using stored procedures. However, I am new to stored procedures, and I have not been able to get this implemented so far. I would truly appreciate if someone can help in this regard.

thanks and regards,

CREATE TABLE `tbl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `timestamp` datetime DEFAULT NULL,
  `direction` varchar(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `market`.`tbl`
(`id`,
`timestamp`,
`direction`)
VALUES
(1,'2020-02-24 15:30:00','BUY'),
(2,'2020-02-24 15:25:00','SELL'),
(3,'2020-02-24 15:20:00','SELL'),
(4,'2020-02-24 15:15:00','BUY'),
(5,'2020-02-24 15:10:00','BUY'),
(6,'2020-02-24 15:05:00','BUY'),
(7,'2020-02-24 15:00:00','SELL');

Answer 1

CREATE TRIGGER tr
AFTER INSERT
ON tbl
FOR EACH ROW
BEGIN
IF EXISTS ( SELECT 1
            FROM tbl t1, tbl t2
            WHERE t1.`timestamp` BETWEEN CURRENT_TIMESTAMP - INTERVAL 2 MINUTE
                                     AND CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
              AND t2.`timestamp` BETWEEN CURRENT_TIMESTAMP - INTERVAL 7 MINUTE
                                     AND CURRENT_TIMESTAMP - INTERVAL 3 MINUTE
              AND t1.direction != t2.direction ) THEN
    IF 'SELL' = ( SELECT direction
                  FROM tbl
                  ORDER BY `timestamp` DESC LIMIT 1 ) THEN
        /* SELECT 1 INTO OUTFILE 'SELL.txt'; */
        INSERT INTO service (txt) VALUES (CONCAT(CURRENT_TIMESTAMP, ' SELL'));
    ELSE
        /* SELECT 1 INTO OUTFILE 'BUY.txt'; */ 
        INSERT INTO service (txt) VALUES (CONCAT(CURRENT_TIMESTAMP, ' BUY'));
    END IF;
END IF;
END

fiddle

Execute the fiddle a lot of times - you'll see that the messages are generated when the directions in 2 last records differs, and not generated when the direcions are the same.

The problem - each insert (except the first one) generates an insertion into the service table (and OUTFILE creation if uncomment it) - but the second attempt to create OUTFILE (which already exists) will fail which will cause the whole insertion query fail. You must create some static mark (service table which stores the timestamp is safe - and check it with some clearance like in records checking, +/- 2 min. seems to be useful) which allows to identify that the file was already created during this INSERT, and do not try to create it one more time.