List:General Discussion« Previous MessageNext Message »
From:Ravi Malghan Date:April 10 2006 9:15pm
Subject:Re: select all events from (today-N) days
View as plain text  
Joerg: thank you. That worked and much faster too.

WHERE EVENT_DATA.utime >
UNIX_TIMESTAMP(SUBDATE(CURDATE(), 5)) AND
EVENT_DATA.utime < UNIX_TIMESTAMP(SUBDATE(CURDATE(),
4))

Ravi

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

> Hi Ravi, all,
> 
> 
> Ravi Malghan wrote:
> > 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 ?
> 
> "need to"?
> This is not required in order for the command to
> work (at all),
> 
> but IMNSHO this is the proper way to write an SQL
> statement to achieve 
> good performance.
> (Mathematically spoken, it is not "sufficient", but
> it may be 
> "necessary"; and it definitely is portable to all
> SQL systems.)
> 
> (Aside: Are "sufficient" and "necessary" the terms
> used when discussing 
> mathematics and logic in English? Just curious.)
> 
> > 
> > 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.
> 
> Currently, you do
> ... WHERE FROM_UNIXTIME(utime,'%Y-%m-%d')
>            = (CURDATE() - INTERVAL 1 DAY);
> 
> IMO, you should do something like
> ... WHERE utime BETWEEN
>      UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 1
> 0:0:0 DAY_SECOND)) AND
>      UNIX_TIMESTAMP (DATE_SUB (CURDATE(), INTERVAL 0
> SECOND))
> 
> DATE_SUB() converts from DATE to DATETIME if the
> interval has a 
> component with finer granularity than days, and it
> maps a date to its 
> beginning (00:00:00), according to the manual.
> 
> 
> Disclaimers:
> 1) not tested.
> 2) I cannot guarantee that a "0 second" interval is
> not optimized away.
>     If that happens, you may need to use "1 second",
> and the result will
>     be inexact around midnight.
> 3) Note the hint in the manual about the lossy
> conversion,
>     especially at the start and end of daylight
> saving time.
> 
> 
> 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