[thelist] date selection and mysql and PHP

Getafixx getafixx at getafixx.com
Tue May 10 09:03:09 CDT 2005

Hi Burhan and Jay,

I tried both methods you suggested and both didn't work..

At first I thought that this would be perfect and a nice simple solution 
but this doesn't work, or returns no matches

SELECT * FROM `my_table` WHERE `date_time` LIKE '20050509$'

then Burhan suggested this, but again this fails to produce any rows.
SELECT * FROM `my_table` WHERE DATE_FORMAT('%Y%m%d',`date_time`) = 

The original method I think will be used and the coding to get to the 
SELECT * FROM `my_table` WHERE `date_time` BETWEEN "20050509" AND 
"20050510" ORDER BY `date_time`

it's not clean, but at least I get rows returned.

Again thanks for everyone's help.


Burhan Khalid wrote:
> Getafixx wrote:
>> OK I am working on a simple calender (OK I know it's pretty dull stuff)
>> My questions are - I am storing the date and the timing of an event 
>> with DATETIME, which seams to work OK.
>> I have been trying to craft a search to get ONLY the events on the day 
>> in question.. The only way I could do that and it was a big hack was 
>> to use this query, where I have to arse around with figuring out what 
>> the next day is and go from the correct day to the next day...
>> SELECT * FROM `events` WHERE `date_time` BETWEEN "20050509" AND 
>> "20050510" ORDER BY "20050509"
> I can't believe this query works on a DATETIME type field, because the 
> format of that field is '0000-00-00 00:00:00'
> You can check the different date and time types in the manual to see 
> their default format by checking the "zero" value.
> [ snipped ]
>> Maybe my question is why can't I just say where date row = <my date> 
>> where I try and match just the first part of the date with the first 
>> part of any dates in the table
>> SELECT * FROM `my_table` WHERE `date_time` = "20050509"
>> but this doesnt seam to work...
> SELECT `col`, `col2` FROM `my_table` WHERE 
> DATE_FORMAT('%Y%m%d',`date_time`) = '20050509';
>> Also on reading the db for a months worth of dates, would it be better 
>> to do one query for the whole month and then parse it up, or lots of 
>> small queries, for each day?
> I'm not a database guru -- but I believe that many small select queries, 
> not one large complicated one are generally faster. The speed depends on 
> many other factors, such as your table size, the type of query, indices 
> on the table, etc.
> In your case, I don't think it will matter that much.

Justin / Getafixx                                07967 638 529
mailto:qwerty1 at getafixx.com

http://getafixxhosting.com for really cheap web hosting

More information about the thelist mailing list