Well long story short: i'm trying to make a simple report which will help our company track automatic messages from our clients.
So , we have few customers each are running one or more databases. We have automated scripts that run at HH:mm hours on each database sending reports by e-mail with the status of each db running. The e-mails are being stored in a ticketing platform named otrs that is based on a mysql database (would of been much better if it were oracle but hey gotta work with what we have).
The main idea is to count the e-mails on a specific day a certain client sends. The email subjects are relatively the same. The problem i've encountered is working with the date system in mysql which i rarely used and it feel very hard to work with (for me that is).
I've managed to work on this select:
SELECT title, date_format(create_time,'%m-%d-%Y %H:%i')
WHERE date_format(create_time,'%m-%d-%Y') = date_format(sysdate(), '%m-%d-%Y')
and upper(title) like upper('%CLIENT NAME%status');
This will display the tickets a certain client sent but it's only working for the day the request is being run on - sysdate.
I need to know how to work with date_format function or any other suggestion that you might have regarding how to count e-mails between two dates or in a specific day at a specific hour that i choose.
The create_time is of "datetime" type and looks something like this: 12/14/2010 8:39:32 PM, if it helps there also is another table called create_time_unix of type bigint : 1292359172 and title is varchar.