Thanks Michael, This way works fine anyway was just interested if there was a better way of doing it.
Pete -----Original Message----- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, 21 March 2005 4:07 PM To: Pete Moran Cc: mysql@lists.mysql.com Subject: Re: DateTime Select optimised Pete Moran wrote: > Hi All, > > Is there a simpler way of doing a select for a given date, for instance if I > have a datetime field called date > > And so its populated with a load of values such as > > 2005-01-07 09:00 > 2005-01-07 10:00 > 2005-01-07 11:00 > 2005-01-07 12:00 > > If I wanted all records which fall on 2005-01-07 I could of course do > > Select * from <TABLE> where date > ‘2005-01-07’ and date < > DATE_ADD(‘2005-01-07’, INTERVAL 24 HOUR) > > However is there a simpler way of doing it by just passing one date like > > Select * from <TABLE> where date = ‘2005-01-07’ > > ? No. A date is fundamentally a range of datetime values, so this is the right way. I'd like to point out, however, that your query misses midnight (00:00). You should change it to use '>=' instead of '>': SELECT * FROM <TABLE> WHERE date >= ‘2005-01-07’ AND date < ‘2005-01-07’ + INTERVAL 24 HOUR; Alternatively, you could add 1 day instead of 24 hours: SELECT * FROM <TABLE> WHERE date >= ‘2005-01-07’ AND date < ‘2005-01-07’ + INTERVAL 1 DAY; You could make the query look simpler by changing the datetime column into a date or string and then doing an = comparison, but that would prevent the use of the index. The only other way to make this simpler would be to split the date and time into separate columns, but that would no doubt cause headaches elsewhere. Michael -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.7.4 - Release Date: 18/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
