Help get each HOUR from a date() for SQL filtering

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Help get each HOUR from a date() for SQL filtering

Post by avstudio1 »

I am currently writing some very bulky code to select each hour from a POSTED date to a page. I want to be able to generate SQL to say how many parts were made between each hour of a particular shift, there are 3 shifts. The user selects a date and a shift (Day, Night, Afternoon) from a drop down list which gets posted to a results page.

I am managing the dates as such:

Code: Select all

If (!isset($_POST['PickDate'])) {
        $PickDate = date("l, F d, Y");
} else {
        $PickDate = date("l, F d, Y", $_POST['PickDate']);
}

If (!isset($_POST['PickShift'])) {
        $PickShift = "Night Shift";
} else {
        $PickShift = $_POST['PickShift'];
}
then I isolate the shifts into 3 different groups (Night Shift Shown):

Code: Select all

$NightShiftStart = date("Y-m-d H:i:s", mktime(23,00,00,$PickMonth, $PickDay-1, $PickYear));
$NightShiftEnd = date("Y-m-d H:i:s", mktime(06,59,59,$PickMonth, $PickDay, $PickYear));
I pass this through a query and print out a report of how many parts were made for that shift:

Code: Select all

$NightShiftCountSQL = "SELECT DCM_ID AS DCM, COUNT(ShiftCount) AS COUNT
                       FROM ester_perpart
                       WHERE TS BETWEEN '$NightShiftStart' AND '$NightShiftEnd'
                       GROUP BY DCM_ID
                      ";
Now they have asked for a breakdown by each hour, and my code is becoming unmanageable. I am creating 2 variables for every hour group:

Code: Select all

$CurrentShiftHour1Start = date("Y-m-d H:i:s", mktime(23,00,00, $PickMonth, $PickDay-1, $PickYear));
        $CurrentShiftHour1End = date("Y-m-d H:i:s", mktime(23,59,59, $PickMonth, $PickDay-1, $PickYear));
and then I am creating 8 SQL statements per shift, etc. etc.

There has to be a better way. I would like to write a single query to handle the 8 hour breaks. Any suggestions ???

Thanks,
Justin
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT `DCM_ID` AS `DCM`, COUNT(`ShiftCount`) AS `COUNT`
FROM `ester_perpart`
GROUP BY HOUR(`TS`)
maybe?
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post by avstudio1 »

GROUP BY HOUR, thats a great idea! Do you know how to display the hour in which I am grouping by?

What I am after is the following result:

Count results for DCM #1

HOUR | 1 | 2| etc (or HOUR | 7:00 | 8:00 | etc., either is acceptable)
COUNT | 100 | 200 | etc

I made a slight change already, this is my SQL:

Code: Select all

"SELECT HOUR('TS') AS HOUR, COUNT(`ShiftCount`) AS `COUNT`
                         FROM `ester_perpart`
                         WHERE TS BETWEEN '$CurrentShiftStart' AND '$CurrentShiftEnd'
                         AND DCM_ID = '$PickDCM'
                         GROUP BY HOUR(`TS`)
                         ";
which puts out:

HOUR | 0 | 0 | etc
COUNT | 100 | 200 | etc
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

avstudio1 wrote:

Code: Select all

"SELECT HOUR('TS') AS HOUR, COUNT(`ShiftCount`) AS `COUNT`
                         FROM `ester_perpart`
                         WHERE TS BETWEEN '$CurrentShiftStart' AND '$CurrentShiftEnd'
                         AND DCM_ID = '$PickDCM'
                         GROUP BY HOUR(`TS`)
                         ";
HOUR(`TS`)

Notice ` vs '
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post by avstudio1 »

attacked by the quote monster, THX!
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post by avstudio1 »

I need some more help on this.

I have been developing on a MYSQL engine, and then transferring to MSSQL once I get to site.

The HOUR (`TS`) does not work on the site machine. Also the quote style ` doesn't seem to like it here either.
Is there a MSSQL alternative to the HOUR() function?


Thanks,
Justin
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

Post by avstudio1 »

A spoonful at a time ......


DATEPART(hh, TS);



;)
Post Reply