[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`) =
'20050509';
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.
Justin
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://getafixx.com
http://getafixxhosting.com for really cheap web hosting
==============================================================
More information about the thelist
mailing list