SQL help to add daily between dates

Question

SQL help to add daily between dates

Navigation

#1 by (1 votes)

1

I have two tables: company and truck

In the table, I have the data entry fields, datasaida, valordiaria I would like to select all the companies, and list it and the total amount that the company spent on daily, only if the datasaida is different from '0000-00-00 00:00:00' in case. If you did not complete the daily rate, type 1 day and 20 hours for example, the daily rate is counted the same. Always rounded up.

I'm using PHP. I'm stuck with this SQL that first needs to figure out how many days I've spent in total for each truck, then do the daily rate, then add up all the totals and generate the total that was spent by each company.

$sql = mysql_query("SELECT * FROM empresa WHERE emp_ativo = '1' ORDER BY emp_nome ASC");
while ($linha = mysql_fetch_assoc($sql)){

    $sql_valor = mysql_query("SELECT * FROM caminhao WHERE cam_ativo = '1' AND emp_id = '".$linha['emp_id']."' AND cam_datasaida != '0000-00-00 00:00:00'");
    while ($val = mysql_fetch_array($sql_valor)){
        ...
    }

    $lista[] = $linha;

}

php mysql

asked by anonymous 09.07.2014 / 22:28

1 answer

Use: before or a div with an image? How to create an event, the mouse reaches a certain area of the application

score 1 · Answer 1

You can use the DATEDIFF function of mysql . The syntax is as follows:

DATEDIFF(date1,date2)

In a query it would look like this:

SELECT DATEDIFF('2015-07-24', '2014-07-24') AS DiffDate

The return is:

+----------+
| DiffDate |
+----------+
|      365 |
+----------+

Being 365 the difference between 7/24/2015 and 7/24/2014.

If you need the difference in hours, you can use the TIMEDIFF function whose syntax is the same:

TIMEDIFF(expr1,expr2)

In a query it would look like this:

SELECT TIMEDIFF('2014-07-28 12:45:00', '2014-07-24 10:55:00') AS DiffTime

The return is:

+----------+
| DiffTime |
+----------+
| 97:50:00 |
+----------+

If you need the decimal difference, 1.5 hours for example, we can use TIMEDIFF in conjunction with the TIME_TO_SEC function whose syntax is as follows:

TIME_TO_SEC(time)

In a query it would look like this:

SELECT TIME_TO_SEC(TIMEDIFF('2014-07-24 16:48:00', '2014-07-24 07:00:00'))/3600 AS DecimalTime

The return is:

+-------------+
| DecimalTime |
+-------------+
|      9.8000 |
+-------------+

I hope I have helped:)

Updating

As you need the value in days, simply divide the result ob- tained by 24, being as follows:

SELECT TIME_TO_SEC(TIMEDIFF('2014-07-24 16:48:00', '2014-07-24 07:00:00'))/3600/24 AS DecimalTime

The return is:

+-----------------+
| DecimalTime     |
+-----------------+
|      0.40833333 |
+-----------------+