Determine week-ends from a list of dates

[tiota]

MYSQL  5.1.36

PHP 5.3.0

 

I have a query which extracts  dates from a database and I want to determine which dates are week-ends, specifically

saturday and sunday. Lets say $start_date = "2010-06-01"

and $end_date = "2010-06-30".

 

My query below worked pefectly, but to figure out week-end

dates is a bit complicated to me. How to do it in php ?

 

$qry = "Select employee.dept_num, department.dept_desc, timesheet.ts_timein, timesheet.ts_timeout,timesheet.ts_date

From timesheet, employee, department

Where timesheet.emp_num = employee.emp_num

AND department.dept_num = employee.dept_num

AND timesheet.ts_date BETWEEN '$start_date' AND '$end_date' ";

 

sample output below if  you like

 

+----------+----------------+-----------+------------+------------+

| dept_num | dept_desc      | ts_timein | ts_timeout | ts_date    |

+----------+----------------+-----------+------------+------------+

| E2501    | Administration | 08:48:00  | 00:00:00  | 2010-07-01 |

| E2501    | Administration | 08:23:00  | 04:19:00  | 2010-07-05 |

| E2501    | Administration | 08:19:00  | 00:00:00  | 2010-07-06 |

| E2501    | Administration | 09:01:00  | 04:22:00  | 2010-07-07 |

| E2501    | Administration | 08:23:00  | 00:00:00  | 2010-07-08 |

| E2501    | Administration | 09:35:00  | 04:37:00  | 2010-07-02 |

| E2501    | Administration | 08:44:00  | 04:52:00  | 2010-07-05 |

| E2501    | Administration | 08:22:00  | 04:53:00  | 2010-07-06 |

| E2501    | Administration | 08:43:00  | 00:00:00  | 2010-07-07 |

| E2501    | Administration | 08:19:00  | 04:38:00  | 2010-07-08 |

| E2501    | Administration | 09:04:00  | 04:28:00  | 2010-07-09 |

| E2501    | Administration | 08:13:00  | 04:15:00  | 2010-07-05 |

| E2501    | Administration | 09:33:00  | 00:00:00  | 2010-07-06 |

| E2501    | Administration | 10:29:00  | 04:18:00  | 2010-07-07 |

| E2501    | Administration | 09:32:00  | 04:18:00  | 2010-07-09 |

| E2501    | Administration | 04:39:00  | 00:00:00  | 2010-07-02 |

| E2501    | Administration | 09:55:00  | 04:19:00  | 2010-07-05 |

 

 

 

 

 

Thanks in advance

 

 

 

 

 

 

[otuatail]

You can find what day of the week this is ....

    $stamp = time();

    $day = date('w',$stamp);

0=sunday

 

Desmond

[otuatail]

Problem

Date in is hh:mm:ss and Date out is hh:mm:ss  BUT

but  ts_date  is YYYY/MM/DD

 

you should scrap the last field and have the first as date and time

YYYY/MM/DD HH/MM/SS

 

Whats the point of having 3 fields? what happens if the last date is in the next day

 

$stamp =  strtotime($date); // gets the date as unix stamp

$day = date('w',$stamp); // 0 - 7