Re: querry problem ( datetime => Monday - Sunday )

[inferno]

Hi,

    Yes I want only 24 hours of business program, and like you've said I 
do not want to include Saturday and Sunday, since nobody is working in 
the week-end.
    Thank you very much for the help.

Best regards and have a nice week-end,
Cristi Stoica

Michael Stassen wrote:

> 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