1

Please consider the following stored procedure I have:

ORIGINAL Stored Procedure:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE `myProc`()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING

BEGIN
  -- ERROR
ROLLBACK;

END;

START TRANSACTION;

INSERT INTO mydb.mytable(some values)

SELECT (some values)

FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

DELETE FROM   mydb.mytable

WHERE m_dt BETWEEN  CURDATE() - INTERVAL 60 DAY AND CURDATE() - INTERVAL 60;

COMMIT;

END

Since m_dt datatype is datetime, I want to modify my BETWEEN condition. I want to add 00:00:00 before the AND and 23:59:59 after the AND condition. For example something like the following:

WHERE m_dt BETWEEN CURDATE() - INTERVAL 60 DAY 00:00:00 AND CURDATE() - INTERVAL 60 23:59:59;

By any chance it is possible in MySQL?

2 Answers 2

1

I would code as follows:

SELECT (some values)
FROM   mydb.mytable
WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL     0 SECOND 
AND   m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND;

DELETE FROM mydb.mytable
WHERE m_dt >= CURDATE() - INTERVAL 60 DAY + INTERVAL     0 SECOND 
AND   m_dt <= CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND;

Just to demonstrate the date math,

mysql> SELECT
    -> NOW() now,CURDATE() + INTERVAL 0 SECOND dt0,
    -> CURDATE() - INTERVAL 60 DAY + INTERVAL     0 SECOND dt1,
    -> CURDATE() - INTERVAL 60 DAY + INTERVAL 86399 SECOND dt2\G
*************************** 1. row ***************************
now: 2014-07-14 18:18:21
dt0: 2014-07-14 00:00:00
dt1: 2014-05-15 00:00:00
dt2: 2014-05-15 23:59:59
1 row in set (0.00 sec)

mysql>

Just make sire m_dt is indexed.

Give it a Try !!!

1
  • Awesome. This solution is much better than the other. I believe performance wise it's wise not to use BETWEEN.
    – John
    Commented Jul 16, 2014 at 16:16
1

Try this

I believe the first part already evaluates to 00:00:00. So I just added the seconds in.

WHERE m_dt BETWEEN  (CURDATE() - INTERVAL 60 DAY) AND 
  (DATE_ADD((CURDATE() - INTERVAL 60 DAY),INTERVAL 86399 SECOND))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.