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 <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED] > > __________________________________________________ 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/[EMAIL PROTECTED]
