How to compute extra pay in night shifts and on sundays

I did a table in MySQL storing start and end times of working shifts in datetime type. I need to calculate two things from them (in MySQL). First of all, how many minutes someone has worked in given shift between 10pm and 6am, as well as how many minutes someone has worked on a sunday. Sunday should have priority over those night times, and it shouldn't count those sunday mornings twice.

Thank you in advance.

D.

I tried this btw, but it didn't work:

SELECT *,
IF(DAYNAME(start)=DAYNAME(end),
    IF(TIME(start)<='6:00:00' AND TIME(end)<='6:00:00',TIME(end)-TIME(start),
        IF(TIME(start)>='22:00:00' AND TIME(end)>='22:00:00',TIME(end)-TIME(start),
            IF(TIME(start)>='6:00:00' AND TIME(end)<='22:00:00',0,
                IF(TIME(start)<='6:00:00' AND TIME(end)<='22:00:00','6:00:00'-TIME(start),
                    IF(TIME(start)>='6:00:00' AND TIME(end)>='22:00:00',TIME(end)-'22:00:00',NULL)
                    )
   开发者_如何学Go             )
            )
        ),
    IF(TIME(start)<='22:00:00' AND TIME(end)<='6:00:00','2:00:00'+TIME(end),
        IF(TIME(start)<='22:00' AND TIME(end)>='6:00:00','8:00:00',
            IF(TIME(start)>='22:00:00' AND TIME(end)<='6:00:00','24:00:00'-TIME(start)+TIME(end),
                IF(TIME(start)>='22:00:00' AND TIME(end)>='6:00:00','24:00:00'-TIME(start)+'6:00:00',NULL)
                )
            )
        )
    ) AS ExtraTime FROM `work`

---

minutes worked:

SELECT TIMESTAMPDIFF(MINUTE, `start_time`, `end_time`) FROM ... WHERE DAYOFWEEK(`start_time`) != 1

minutes worked on Sunday:

SELECT TIMESTAMPDIFF(MINUTE, `start_time`, `end_time`) FROM ... WHERE DAYOFWEEK(`start_time`) = 1

---

In PHP, try this code (warning: it hasn't been tested):

<?php
$regularPay = 100; //100 dollars a day
$weekendPay = $regularPay + ($regularPay / 2); //150 dollars over weekend
$link = mysqli_connect("localhost","username","password");
mysqli_select_db("dbname");
$pay = date("w")==0 or date("w")==6 ? $weekendPay : $regularPay; //0=Sun, 6=Sat
$query = mysqli_query($link,"INSERT INTO pay SET employee='John Smith',pay='$pay'");
?>

Simply replace the strings with the appropriate values for your computer.

Ad@m.