Muhammad Essam's avatar

Summing time sperated into two mysql columns

I'm storing duration 1, 2,3, 4 , ...... and duration type days , or hours the problem i want to sum all the duration the right way for example i want to add 1 day to 2 hours and so on

0 likes
7 replies
Randy_Johnson's avatar

Okay, am not completely sure what it is you're asking but you would go about it by

  1. Creating a migration
  2. In the migration have column duration, date
  3. You would have the duration in seconds.
  4. You can then call this in your controller.
  5. With the duration you can then do a math calc to get mins, hours.
  6. And you have your date.

OR

You could have have twi columns, start (DATETIME), finish (DATETIME).

This will store the time and date, you can take the time value from s and f do a calc to have a time frame. Hope this helps.

1 like
Muhammad Essam's avatar

@randy_johnson yes you are right but imagain theat i have two column one called duration that only have number for example 1, 2, 3, ... , and anthor column called duration type that can only has [days, hours] so if a want to sort 3 hours => first colum will have the value 3 and the second column will have the value hours my question is how to sum all rows in these two columns?

jbloomstrom's avatar

You can use DateInterval::createFromDateString

$quantity = 2;
$unit = "days";

$start = now();
$interval = DateInterval::createFromDateString("{$quantity} {$unit}");

$end = $start->add($interval);
1 like
Snapey's avatar

wel of course the best way would be to store all data in hours.

If you can do this for new data, then you could create a job that 'fixes' existing data

1 like
Muhammad Essam's avatar

@Snapey In the UI I have two spearte inputs one is number input, and the other is select input with two options [days, hours] so the first thing jump to my mind was to design the database as so. but in your opinion if i can store the data in hours how can i display it. i must stick to the UI

Please or to participate in this conversation.