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 <[EMAIL PROTECTED]>:
> 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 |
> |
> >
>
+-------------+------------------+------+-----+---------------------+----------------+
>
>
>
> [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
