--- Ravi Malghan <[EMAIL PROTECTED]> 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 ? > > 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 > > > > There is a whole section on date/time functions: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I think the one you need is called UNIX_TIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine __________________________________________________ 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]
