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
| |
>
+-------------+------------------+------+-----+---------------------+----------------+
[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]
