List:General Discussion« Previous MessageNext Message »
From:Ravi Malghan Date:April 10 2006 7:15pm
Subject:Re: select all events from (today-N) days
View as plain text  
Joerg: are you saying I need to compute the start time
and end time in epoch values and use it in 
select count(*) from EVENT_DATA
 where utime >  start_time and utime < end_time ?

Can you provide some pointers and what to search on. I
have searched and can't seem to find any leads on how
to get the start_time and end_time values.

Thanks
Ravi
--- Joerg Bruehe <joerg@stripped> wrote:

> Hi Ravi, all!
> 
> 
> Ravi Malghan wrote:
> > 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.
> >>
> 
> No, that is not the most efficient way.
> 
> If you have any sizable amount of data, you need an
> index to allow your 
> "where condition" to be evaluated without accessing
> all records (also 
> called "table scan").
> For the efficient use of that index, you should
> ensure that the "where 
> condition" is of the form
>     column_value   comparison_operator  expression
> 
> It does not matter whether "expression" is
> complicated, it needs to be 
> computed only once, but "column_value" should just
> be the column name 
> and not a function / expression using it.
> 
> So what you need is
>     select count(*) from EVENT_DATA
>        where utime >  (seconds of your period start)
> 
> Sorry, I lack the time to scan the manual for the
> correct expression to 
> calculate that start value.
> 
> 
> HTH,
> Joerg
> 
> -- 
> Joerg Bruehe, Senior Production Engineer
> MySQL AB, www.mysql.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