Storing and retrieving mySQL timers data, right approach-CodePudding

I'm working on thermometer in PHP (Laravel) and mySQL DB. Thermometer functionality has timers so you can set time from and time to certain time period what temperature you want.

My initial setup was table timers

And data I entered was

While trying to retrieve value from timers on current time I realized that time(data type) can't be viewed as date is, because if we look at first row, from_time 23:30:00 is actually bigger than to_time 06:00:00 but we look at it as a next day.

I was testing this using

//$time = date("H:i");
  $time = date("H:i",strtotime(' 14 hour'));
  $timers = Timers::whereTime('from_time', '<=', $time)->whereTime('to_time', '>=', $time)->first();
  
  print ($time);
  print ('</br>');
  
  if($timers){
    print ('Value '.$timers->value);
  }

So my question is to this.. What is the best approach for storing and retrieving this kind of timers data ?

My thots was to have extra table that splits timers so for first row example I have two entries..

id	from_time	to_time	value
1	23:30:00	23:59:59	19.50
2	00:00:00	06:00:00	19.50
3	06:00:00	23:30:00	21.50

And for second row only one entry because it dose not passes midnight 00:00:00

This way I can use mentioned php (Laravel) example of retrieving data. Is this the right approach or is there better one?

CodePudding user response：

Keep your existing table design...
...but change the semantics/meaning of your to_time to mean the "length of time after from_time" instead of being the absolute time-of-day when the timer should stop.
- Personally, I'd rename it to length (or timer_length if you want to avoid clashing with an existing SQL keyword),
Note that in MySQL, the time type does not represent a time-of-day value: i.e. its range is not constrained to 00:00:00-23:59:59, its range is actually approximately -839h to 838h hours, which means you'll need a CHECK constraint to ensure values are always non-negative and aren't excessively long either.
You can also add a computed-column that shows the actual time-of-day end time too:

CREATE TABLE timers (
    id           bigint(20) PRIMARY KEY AUTO_INCREMENT,
    homes_id     bigint(20),
    value        decimal(16,2),
    from_time    time,
    timer_length time,
    days         char(100),
    created_at   timestamp,
    updated_at   timestamp,

    timer_end    time AS ( ADDTIME( from_time, timer_length ) ),

    CONSTRAINT pk_timers PRIMARY KEY ( id ),
    
    CONSTRAINT fk_homes FOREIGN KEY ( homes_id ) REFERENCES homes ( homes_id ),

    CONSTRAINT ck_timer_length CHECK ( timer_length > 0 AND timer_length < '24:00:00' )
)

CodePudding user response：

You can do this:

   $totalDuration = $finishTime->diffInSeconds($startTime);

// e.g $totalDuration = 10

You can then use gmdate:

gmdate('H:i:s', $totalDuration);

// 00:00:10

Also, having it in DateTime would make you know the day and time as you can use Carbon in Laravel to convert it to what you want.