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
