Dates question 1

[rjseals]

I am creating a help desk system for my work. I basically have a ticket table (MYSQL). I am trying to create a report that will tell me how many tickets are opened per day on a certain range of dates (in the past). When a ticket is opened I put the timestamp on the "Submitted_On" field.

What would be an efficient way to calculate the number of open tickets per day over a range of days in the past?

 

[DRJ478]

You would use a SQL select statement that gets the date, the count of the records, combine it with a WHERE clause that restricts by date and GROUP BY date

SELECT submit_date, count(*) AS num_items FROM `whatevertable` WHERE submit_date >= '$fromdate' AND submit_date <= '$todate' GROUP BY submit_date

 

[rjseals]

So would I have to run that query in a loop for every day I wanted to see how many tickets were opened?

IE if I wanted to see the number of tickets opened from 11/01/05 to 11/05/05, I would have to run that query 5 times?

And does it matter that I am storing the submit_date as a timestamp?

Thanks.

 

[DRJ478]

No.
Here's why:

a) the WHERE clause selects all dates in the range you query for
b) the count(*) counts the number of tickets per date because:
c) the group by gathers the results per individual date.

Since you store it in a timestamp YYYY-MM-DD HH:MM:SS you should extract the date only portion with the MySQL date functions.

 

[rjseals]

I think I am confused on what a timestamp is, sorry my bad. I am storing the submit_date date as this $date=date(); And storying that numerical value into the database.

 

[DRJ478]

So, you have a UNIX timestamp which needs to be converted into a human readable date using the MySQL Date/Time formatting functions. A quick visit to

http://dev.mysql.com

will give you the answer (once the site is up again...)

 

[vacunita]

Sorry to butt in here DRJ, but the function Date() needs at least 1 paramter. which is what it is going to display.

If you try to do

$date=date();

You should get a "Wrong parameter count for date()..." warning.

So this brings us down to what is it that rjseals is putting into the data base, and more importantly why is he not getting a Warning.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[rjseals]

DRJ,

I think at this point I am just going to convert my date tables to the datetime format because it seems as though that will save me a lot of trouble.

Vacunita,

My bad again. I meant to say I get the timestamp by:
$date=time();

Thanks!

 

[vacunita]

Then you are getting the date in unix timestamp, which in itself is easier to hande since it is just a big integer.

int time ( void )

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970

You can then use DRJ suggestion and it should work fine. once that is done you can convert the results into readable dates.

I think it is simpler with a unix timestamp than it is with a Date configuration.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[DRJ478]

One note:
MySQL provides auto-updating for timestamp fields. There are some recent changes in the timestamp fields (with MySQL 4.1) that you need to take into consideration when reading the manual.
If your MySQL is older than 4.1 things will be different.
However, the first timestamp is autmatically updated under specific circumstances (so, there would be no need to supply any value). Read:

http://dev.mysql.com/doc/refman/4.1/en/timestamp-pre-4-1.html

and the following page for 4.1

 

[rjseals]

Is this the function I need to use to convert from the unix timestamp? FROM_UNIXTIME

 

[DRJ478]

You are correct.

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

 

[rjseals]

First off thanks for all your help DRJ.

I think I am getting this down correctly but getting wierd results. I have about 7 test tickets in this database with

3 opened on the 11/15/05
1 on the 11/16/05
3 on the 11/18/05

When I run this query:

select FROM_UNIXTIME( SUBMITTED_ON ) , COUNT( * )FROM TICKET
WHERE SUBMITTED_ON <= FROM_UNIXTIME( '1130828400' )
GROUP BY SUBMITTED_ON

//the unix timestamp (1130828400) = 11/01/05

Gives this result:

FROM_UNIXTIME
( SUBMITTED_ON) COUNT( *)

2005-11-15 15:56:57 1
2005-11-15 16:37:04 1
2005-11-15 17:08:09 1
2005-11-16 11:10:10 1
2005-11-18 14:30:11 1
2005-11-18 14:30:25 1
2005-11-18 14:31:06 1

See anything wrong with my query? Do I need to specify the FROM unix to only do the date (without the time)?

Thanks.

 

[rjseals]

Duh,

Im pretty sure I need to specify only the date instead of the whole string.

 

[DRJ478]

Yes, that's right. The formatting parameter will allow you to restrict it only to date. Then the group clause will do it's job properly.
However, you've got the concept down now. It's better to use the power of the database server and SQL language than retrieveing a lot of data and managing with PHP to extract, count etc. Good programming makes use of all technologies involved. If you need data, just get what you need.

Cheers,
DRJ

 

[rjseals]

One last question.

I was hoping to return all dates between the two timestamps even if it has 0 opened tickets on that day. Right now with the query I am using it is only returning dates that have more than 0 open tickets on that day.

Here is my query:

SELECT FROM_UNIXTIME( ASSIGNED_ON, '%m/%d' ) , COUNT( * )
FROM TICKET WHERE SUBMITTED_ON >= FROM_UNIXTIME( '1130828400', '%m/%d/%y' )AND SUBMITTED_ON >= FROM_UNIXTIME( '1139432345', '%m/%d/%y' ) GROUP BY FROM_UNIXTIME( SUBMITTED_ON, '%m/%d/%y' )

which returns

DATE COUNT

11/18 3
11/16 1
11/15 3

Can I add anything to my query to get all dates?
so for instance if my start date were 11/1/05 and end date were 11/30/05 would return:

11/30 0
....
11/18 3
11/17 0
11/16 1
11/15 3
11/14 0
11/13 0
...
11/1 0

Thanks once again.

 

[DRJ478]

Now, this is a combination of PHP processing the results from the db query.
PHP should be used to iterate each date in the range and check if there is something reported from the dataset.
Here's a simple idea how you could achieve that:
1. create an associative array that is keyed by date with one element for each day in the range
2. do the sql query and assign the returned count to the dat entry in the array
3. for output iterate the array

That's just one way to do it. You can do it without any array and just iterae the dates... It's up to your imagination. However, since MySQL doesn't have the data, I'd do that part in PHP.