List:General Discussion« Previous MessageNext Message »
From:mfatene Date:September 3 2005 7:29am
Subject:Re: querry problem ( datetime => Monday - Sunday )
View as plain text  
I think that your solution is also a generic one.

But i thought that since saturday and sunday are not business days, the query
will not be played.

On monday 08h00, we know that there is no new records in the table.

I encourage Crisiti to study this solution also.

Mathias

Selon Michael Stassen <Michael.Stassen@stripped>:

> inferno wrote:
>  > Hi,
>  >
>  >   I have a problem: I need to make a select for data that was entered
>  > more than 24 hours ago, but in that 24 hours I have to count only Monday
>  > - Friday since that is the working program, and does not have the status
>  > = '2' ( Solved ) and the problem is that I sincerly do not know how.
>  >   Any help/suggestions are apreciated since I am just a beginner.
>  >
>  > Best regards,
>  > Cristi Stoica
>  >
>  > P.S.: to give you a little idea on what I am using:
>  > ( the interface is coded in PHP for the users )
>  > MySQL 4.0.24 and the the data looks like this:
>  >
>  >
>
+-------------+------------------+------+-----+---------------------+----------------+
>
>  > | Field       | Type             | Null | Key | Default             |
> Extra          |
>  >
>
+-------------+------------------+------+-----+---------------------+----------------+
>
>  > | id          | int(25) unsigned |      | PRI | NULL                |
> auto_increment |
>  > | client_name | varchar(100)     |      |     |                     |
>              |
>  > | code        | bigint(13)       |      |     | 0                   |
>              |
>  > | status      | varchar(13)      |      |     | 0                   |
>              |
>  > | date        | datetime         |      |     | 0000-00-00 00:00:00 |
>              |
>  >
>
+-------------+------------------+------+-----+---------------------+----------------+
>
>
>
> mfatene@stripped wrote:
>  > Hi Cristi,
>  > Look at this :
>  >
>  > mysql> select now();
>  > +---------------------+
>  > | now()               |
>  > +---------------------+
>  > | 2005-09-02 23:15:21 |
>  > +---------------------+
>  > 1 row in set (0.00 sec)
>  >
>  > mysql> select DATE_ADD(now(), INTERVAL -1 DAY);
>  > +----------------------------------+
>  > | DATE_ADD(now(), INTERVAL -1 DAY) |
>  > +----------------------------------+
>  > | 2005-09-01 23:15:27              |
>  > +----------------------------------+
>  > 1 row in set (0.00 sec)
>  >
>  > mysql> select date_format(now(),'%a');
>  > +-------------------------+
>  > | date_format(now(),'%a') |
>  > +-------------------------+
>  > | Fri                     |
>  > +-------------------------+
>  > 1 row in set (0.01 sec)
>  >
>  >
>  > So your query should be similar to  :
>  >
>  > Select * from tbl where status='2' and date <= DATE_ADD(now(),
>  > INTERVAL -1 DAY)
>  > and date_format(date, '%a') in ('Mon', 'Tue', ...,'Fri');
>  >
>  >
>  > Hope that helps
>  > Mathias
>  >
>
> inferno wrote:
>  > Hi,
>  >
>  >    It is perfect, I was thinking of doing it in php but the solution
>  > that I've had was no way optimal.
>  >    Thank you very much for the help.
>  >
>  > Best regards and have a nice week-end,
>  > Cristi Stoica
>
> Are you sure?  I don't think that query does what you describe.  Mathias'
> query shows rows entered over 24 clock hours ago, but leaves out weekend
> rows.  I thought you wanted rows over 24 business hours old.  That is, if
> you run this query at 09:30 on a Monday, 24 hours ago means 09:30 last
> Friday.  Is that correct?  In other words, a row entered at 16:30 on Friday
> is not yet 24 business hours old at 09:30 on Monday, because weekends don't
> count.  Such an entry would be returned by Mathias' query.
>
> If I'm right, you need a different query.  The key is that "yesterday" is 1
> day ago if today is Tuesday through Friday, but it is 3 days ago if today is
> Monday.  Hence, you need something like
>
>    SET @daysago = IF(DAYNAME(CURDATE()) = 'Monday', 3, 1);
>    SET @yesterday = NOW() - INTERVAL @daysago DAY;
>
>    SELECT * FROM yourtable
>    WHERE date <= @yesterday
>      AND status = '2';
>
> You can do it in one query without user variables, if you like, but it's a
> little uglier:
>
>    SELECT * FROM yourtable
>    WHERE date <= NOW() - INTERVAL IF(DAYNAME(CURDATE()) = 'Monday', 3, 1) DAY
>      AND status = '2';
>
> I've assumed the query will only be run on a business day.  If you need to
> be able to run this on the weekend and get correct results, it becomes a bit
> more complicated.  Something like:
>
>    SET @yesterday =  CASE DAYNAME(CURDATE())
>       WHEN 'Saturday' THEN CURDATE() - INTERVAL 1 DAY
>       WHEN 'Sunday' THEN CURDATE() - INTERVAL 2 DAY
>
>       WHEN 'Monday' THEN NOW() - INTERVAL 3 DAY
>
>       ELSE NOW() - INTERVAL 1 DAY
>
>    END;
>
>    SELECT * FROM yourtable
>    WHERE date <= @yesterday
>      AND status = '2';
>
> Again, you can do it in one query by replacing @yesterday in the SELECT with
> the CASE statement on the right side of the SET statement, but it's ugly.
>
> For more information, see the manual:
>
> Date and time functions
> <http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html>
>
> User variables
> <http://dev.mysql.com/doc/mysql/en/variables.html>
>
> IF and CASE functions
> <http://dev.mysql.com/doc/mysql/en/control-flow-functions.html>
>
> Michael
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>


Thread
querry problem ( datetime => Monday - Sunday )inferno2 Sep
RE: querry problem ( datetime => Monday - Sunday )mfatene2 Sep
  • Re: querry problem ( datetime => Monday - Sunday )inferno2 Sep
    • Re: querry problem ( datetime => Monday - Sunday )Michael Stassen3 Sep
      • Re: querry problem ( datetime => Monday - Sunday )mfatene3 Sep
      • Re: querry problem ( datetime => Monday - Sunday )inferno3 Sep