CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Schwann
Schwann

Reputation: 167

Trying to use DATEADD and getting function does not exist error

I have the table TableName which contains approximately 5,000 rows. It consists of a product_id and a ScheduleTime.

What I want to do with this table is replace each value of ScheduleTime, regardless of whatever it might currently be. Each record's new value of ScheduleTime should be set to the nominee value of 20 minutes after the previous record's, with the following exception. If a nominee ScheduleTime occurs after 22:30:00 or before 05:00:00 (i.e. it is too late or too early), then it should be set to the next available datetime.

I should be able to specify what the first new value of ScheduleTime will be.

This is the query that I am using at the moment...

UPDATE TableName 
SET ScheduleTime = DATEADD( MI, ScheduleTime, 20 ) 
WHERE CAST( ScheduleTime AS TIME ) >= '22:30:00' 
   OR CAST( ScheduleTime AS TIME ) < '05:00:00';

But it is producing this error message...

#1305 - FUNCTION DatabaseName.DATEADD does not exist

This query can be used to create the table that I have...

CREATE TABLE IF NOT EXISTS TableName
(
    product_id VARCHAR( 255 ) NOT NULL,
    ScheduleTime DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am populating the above table using the following sample data...

INSERT INTO TableName ( product_id,
                        ScheduleTime )
VALUES ( '01', '2017-05-07 22:00:59' ),
       ( '02', '2017-05-07 09:09:59' ),
       ( '03', '2017-05-07 09:59:59' ),
       ( '04', '2017-05-07 09:49:59' ),
       ( '05', '2017-05-07 23:09:59' ),
       ( '06', '2017-05-07 23:09:59' ),
       ( '07', '2017-05-07 23:09:59' ),
       ( '08', '2017-05-07 23:09:59' ),
       ( '09', '2017-05-07 23:09:59' ),
       ( '10', '2017-05-07 23:09:59' ),
       ( '11', '2017-05-07 23:09:59' ),
       ( '12', '2017-05-07 23:09:59' ),
       ( '13', '2017-05-07 23:09:59' ),
       ( '14', '2017-05-07 23:09:59' ),
       ( '15', '2017-05-07 23:09:59' ),
       ( '16', '2017-05-07 23:09:59' ),
       ( '17', '2017-05-07 23:09:59' ),
       ( '18', '2017-05-07 23:09:59' ),
       ( '19', '2017-05-07 23:09:59' ),
       ( '20', '2017-05-07 23:09:59' );

Upvotes: 1

Views: 17852

Answers (1)

toonice
toonice

Reputation: 2246

Please try the following...

SET @workingScheduleTime := ( SELECT DATE_ADD( '2017-05-06 22:00:59', INTERVAL -20 MINUTE ) );
UPDATE TableName
JOIN ( SELECT product_id,
              CASE
                  WHEN TIME( DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE ) ) NOT BETWEEN '05:00:01' and '22:29:59' THEN
                      @workingScheduleTime := DATE_ADD( TIMESTAMP( DATE( @workingScheduleTime ), '05:00:01' ), INTERVAL 1 DAY )
                  ELSE
                      @workingScheduleTime := DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE )
              END AS ScheduleTime
       FROM TableName
     ) AS redatedList ON TableName.product_id = redatedList.product_id
SET TableName.ScheduleTime = redatedList.ScheduleTime;

This solution by works by looping through each record and adding 20 minutes to the value given to the previous record's ScheduleTime field. It also allows the initial value of ScheduleTime to be specified. Thus we will need to start with a working value of ScheduleTime that is 20 minutes before our initial value so that the first iteration of the loop will return our initial value. Thus my solution starts with the statement...

SET @workingScheduleTime := ( SELECT DATE_ADD( '2017-05-06 22:00:59', INTERVAL -20 MINUTE ) );

With our working value thus initialised I have implemented an UPDATE of the table TableName using a SELECT statement based upon Eric's solution found at MySQL - UPDATE query based on SELECT Query. The statement follows...

UPDATE TableName
JOIN ( SELECT product_id,
              CASE
                  WHEN TIME( DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE ) ) NOT BETWEEN '05:00:01' and '22:29:59' THEN
                      @workingScheduleTime := DATE_ADD( TIMESTAMP( DATE( @workingScheduleTime ), '05:00:01' ), INTERVAL 1 DAY )
                  ELSE
                      @workingScheduleTime := DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE )
              END AS ScheduleTime
       FROM TableName
     ) AS redatedList ON TableName.product_id = redatedList.product_id
SET TableName.ScheduleTime = redatedList.ScheduleTime;

This statement starts by performing an INNER JOIN between one instance of TableName to the results of the following subquery based on their shared value of product_id...

SELECT product_id,
       CASE
           WHEN TIME( DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE ) ) NOT BETWEEN '05:00:01' and '22:29:59' THEN
               @workingScheduleTime := DATE_ADD( TIMESTAMP( DATE( @workingScheduleTime ), '05:00:01' ), INTERVAL 1 DAY )
           ELSE
               @workingScheduleTime := DATE_ADD( @workingScheduleTime, INTERVAL 20 MINUTE )
           END AS ScheduleTime
FROM TableName

This subquery selects the value of product_id for each record in TableName and then checks if the next value of the working value will occur within a timeframe prohibited by the Questioner as being too late / too early. If it does then the next acceptable time (05:00:01 on the next day) is chosen by the CASE statement. If the nominee value falls within the acceptable timeframe then it is chosen by the CASE statement. The working value is updated

The value chosen by the CASE statement is then chosen as the subquery's new value of ScheduleTime.

As mentioned above the results of the subquery are then joined to TableName in such way that each existing record of TableName effectively has it's new value appended to it. The SET statement uses this newly established relationship to change each existing value of ScheduleTime to its corresponding new value.

This answer was tested against a dataset created using the CREATE statement from the Question and populated using the following script...

INSERT INTO TableName ( product_id,
                        ScheduleTime )
VALUES ( '01', '2017-05-07 22:00:59' ),
       ( '02', '2017-05-07 09:09:59' ),
       ( '03', '2017-05-07 09:59:59' ),
       ( '04', '2017-05-07 09:49:59' ),
       ( '05', '2017-05-07 23:09:59' ),
       ( '06', '2017-05-07 23:09:59' ),
       ( '07', '2017-05-07 23:09:59' ),
       ( '08', '2017-05-07 23:09:59' ),
       ( '09', '2017-05-07 23:09:59' ),
       ( '10', '2017-05-07 23:09:59' ),
       ( '11', '2017-05-07 23:09:59' ),
       ( '12', '2017-05-07 23:09:59' ),
       ( '13', '2017-05-07 23:09:59' ),
       ( '14', '2017-05-07 23:09:59' ),
       ( '15', '2017-05-07 23:09:59' ),
       ( '16', '2017-05-07 23:09:59' ),
       ( '17', '2017-05-07 23:09:59' ),
       ( '18', '2017-05-07 23:09:59' ),
       ( '19', '2017-05-07 23:09:59' ),
       ( '20', '2017-05-07 23:09:59' );

The results of my code have been confirmed as appropriate by the Questioner.

Further Reading

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between (on MySQL's BETWEEN and NOT BETWEEN operators)

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date (on MySQL's DATE() function)

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add (on MySQL's DATE_ADD() function)

https://dev.mysql.com/doc/refman/5.7/en/set-statement.html (on MySQL's SET statement)

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_time (on MySQL's TIME() function)

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestamp (on MySQL's TIMESTAMP() function)

https://dev.mysql.com/doc/refman/5.7/en/update.html (on MySQL's UPDATE statement)

Upvotes: 2

Related Questions