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]
