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