CODEWITHSUNDEEP
mysqltime
Madrugada
Madrugada

Reputation: 1289

MySQL: Operations with the TIME datatype

Suppose I have a table like that:

create table reserved (
    id int(4) primary key,
    name varchar(30),
    quantity int(4),
    price int(8),
    info varchar(50),
    expiration_time time,
    contract_id varchar(50)
);

And I insert a few rows in it, using the format "HH:MM:SS" for the expiration_time field. Now I want to update this field, by adding 50 seconds:

update reserved set expiration_date=expiration_date+50 where id=1

When I have HH:MM and less than 10 seconds for the SS, everything works fine. But for a time like: 01:10:40, adding 50 seconds would result in 01:11:00 . What can i do to fix this behavior? Thank you

Upvotes: 1

Views: 163

Answers (1)

eggyal
eggyal

Reputation: 126025

Use ADDTIME() together with MAKETIME():

UPDATE reserved
SET expiration_date=ADDTIME(expiration_date,MAKETIME(0,0,50))
WHERE id=1;

Upvotes: 1

Related Questions