Need to count e-mails depending on the date they were received.

Posted on 2011-05-01
Last Modified: 2012-05-11
Hello experts!

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')
  FROM ticket
 WHERE date_format(create_time,'%m-%d-%Y') = date_format(sysdate(), '%m-%d-%Y')
 and upper(title) like upper('%CLIENT NAME%status');

Open in new window

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.
Question by:CarlsbergFTW
    LVL 82

    Accepted Solution

    MySQL standard date format is 'YYYY-MM-DD HH:MM:SS'.  A lot of other formats are supported but I always use this and convert if I have to.  You can do arithmetic comparisons on MySQL DATETIME values so you can use a WHERE like:

    WHERE create_time > '12/14/2010' AND create_time < '12/15/2010'

    The time part is '00:00:00' if it is not specified.


    WHERE create_time > '12/14/2010 8:39:32' AND create_time < '12/14/2010 9:39:32'

    There is also an 'INTERVAL' function that I don't understand exactly:
    LVL 3

    Author Comment

    yeah i've fixed the issue afterall, i've converted the date calculation as unix_timestamp and got to this:

    SELECT title,
      FROM ticket,
           (SELECT count(title) count
              FROM ticket
             WHERE unix_timestamp(CREATE_TIME) >=
                      unix_timestamp('2011-05-01 00:00:00')
                   AND unix_timestamp(CREATE_TIME) <=
                          unix_timestamp('2011-05-01 23:00:00')
                   AND upper(title) LIKE upper('%status')) count
     WHERE unix_timestamp(CREATE_TIME) >= unix_timestamp('2011-05-01 00:00:00')
           AND unix_timestamp(CREATE_TIME) <=
                  unix_timestamp('2011-05-01 23:00:00')
           AND upper(title) LIKE upper('%status');

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now