Trying to use DATEADD and getting function does not exist error

Question

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' );

Try `DATE_ADD( ScheduleTime, INTERVAL 20 MINUTE )` (note the `_`). Please read https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add. — toonice, May 08 '17 at 03:35
@toonice MI was standing for minutes, when I use `UPDATE Database SET ScheduleTime = DATE_ADD(ScheduleTime, INTERVAL 20 MINUTE) where CAST(ScheduleTime as time) < '22:30:00' or CAST(ScheduleTime as time) >= '05:00:00'; `It does not give me error but it also does not change anything in the table — Schwann, May 08 '17 at 03:46
Likely to do with the `WHERE` statement. I need to check a couple of things. Won't be long. — toonice, May 08 '17 at 03:47
@toonice yes but still data is the same, my date format in the entire column is 2017-05-07 20:49:48 in all rows — Schwann, May 08 '17 at 03:53
Do you mean that all of your time values *are* `20:49:38` or just of that format? Also, what type is the field `ScheduleTime`? — toonice, May 08 '17 at 03:59
all values are the same which is "2017-05-07 20:49:48" and now I need to change those all to start from "2017-05-07 20:49:48" and add 20 mins interval with the where clause attached. scheduletime is the column's name in the table which needs to be updated. — Schwann, May 08 '17 at 04:01
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/143641/discussion-between-toonice-and-schwann). — toonice, May 08 '17 at 04:05

score 2 · Accepted Answer · edited May 23 '17 at 10:31

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)

Thank you for spending hours of time working to fix this issue for me. I cannot say how much I appreciate this. The code works flawlessly and it even gives option to define a custom start time and date. — Schwann, May 08 '17 at 07:01
You're welcome. Glad to help. I shall have an explanation added shortly. — toonice, May 08 '17 at 08:05
Explanation added. Also, an error was spotted. Before, when it found that a nominee `ScehduleTime` value within the prohibited timeframe it set the time component to `05:00:01` and the date component to the *same* day, rather than the *next* day as it should have. This error has now been corrected in my Answer. My apologies if this has caused any inconvenience. — toonice, May 08 '17 at 09:35
Thank you very much for spending even more time to fix the issue that I did not even notice. God Bless you. — Schwann, May 08 '17 at 16:37

Trying to use DATEADD and getting function does not exist error

1 Answers1