CODEWITHSUNDEEP
mysqlsql
Nick
Nick

Reputation: 353

how to add hours ,minutes or seconds in 'TIME' Datatype in mysql

I want to add hours or minutes in TIME datatype (no DATE in here) in MySQL:

create table table1(
    arrival_time TIME
);

Here is the case in which the problem is, after 23:59:59 in arrival_time, if I add 1 hour , it should display 00:59:59, not 24:59:59.

I have tried many functions like addtime(), date_add() and all but doesn't work with the above requirement.

Upvotes: 1

Views: 2128

Answers (2)

Hiren
Hiren

Reputation: 1

I have two fields. One field as time_frame with time like

01:15

and in second field minutes in number

45

This is working for me,

( ADDTIME(STR_TO_DATE( appointments.time_frame, "%H:%i:%s" ), STR_TO_DATE( CONCAT( "0", FLOOR( `appointments`.appx_hour / 60 ), ":", MOD ( `appointments`.appx_hour, 60 ),":00" ),"%H:%i:%s" ))) AS total_time

Upvotes: 0

Abecee
Abecee

Reputation: 2393

select arrival_time,
       maketime(mod(HOUR(date_add(arrival_time, INTERVAL 1 HOUR)), 24),
                mod(minute(date_add(arrival_time, INTERVAL 2 MINUTE)), 60),
                mod(second(date_add(arrival_time, INTERVAL 2 SECOND)), 60)) sooner_or_later,
       TIME((ADDTIME(TIME('23:59:59'), TIME('01:02:02')))%(TIME('24:00:00'))) or_rather_so
from table1;

returns

|                   ARRIVAL_TIME |                SOONER_OR_LATER |                   OR_RATHER_SO |
|--------------------------------|--------------------------------|--------------------------------|
| January, 01 1970 23:59:59+0000 | January, 01 1970 00:01:01+0000 | January, 01 1970 01:02:01+0000 |

Second column pushing bits. Last column doing proper arithmetic - borrowed from ADDTIME() return 24 hour time

SQL Fiddle

Upvotes: 1

Related Questions