List:General Discussion« Previous MessageNext Message »
From:Ravi Malghan Date:April 6 2006 12:12pm
Subject:Re: select all events from (today-N) days
View as plain text  
Ok. I found something. But wondering if this is most
efficient
Events for yesterday:
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%Y-%m-%d') = (CURDATE() -
INTERVAL 1 DAY);

Events for last week
select count(*) from EVENT_DATA where
FROM_UNIXTIME(utime,'%U') =
(DATE_FORMAT(CURDATE(),'%v')-1);

TIA
Ravi
--- Ravi Malghan <rmalghan@stripped> wrote:

> Hi: I have a date/time field (utime) which has unix
> time in epoch time. I want to select events from
> yesterday and another statement for all events from
> previous week.
> 
> I have tried the functions listed at
>
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html.
> But I either get a syntax error or just the wrong
> info.
> 
> select * from TABLE where utime < endtime and utime
> >
> starttime
> 
> how do I get the starttime and endtime for yesterday
> and also the last week(Sunday to Saturday)?
> 
> Thanks
> Ravi
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> http://mail.yahoo.com 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/mysql?unsub=1
> 
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
select all events from (today-N) daysRavi Malghan6 Apr
  • Re: select all events from (today-N) daysRavi Malghan6 Apr
    • Re: select all events from (today-N) daysJoerg Bruehe7 Apr
      • Re: select all events from (today-N) daysRavi Malghan10 Apr
        • Re: select all events from (today-N) daysJoerg Bruehe10 Apr
          • Re: select all events from (today-N) daysRavi Malghan10 Apr
        • Re: select all events from (today-N) daysShawn Green10 Apr