How to implement SELECT field1, field2 FROM table1 Where field3=1 order by DATE(timestampfield)

[eluser]Zahidul Ripon[/eluser]
I have a problem in codeignitor that is i have a timestamp field (like 2010-06-05 13:30:00). I want to execute my query order by only date part of the timestamp field (like 2010-06-05).
Please anyone help me.

[eluser]Mutsop[/eluser]
I don't quite get your problem...
If I'm correct, you want to order by your results by just the date instead of date and time?

[eluser]Zahidul Ripon[/eluser]
[quote author="Mutsop" date="1289327851"]I don't quite get your problem...
If I'm correct, you want to order by your results by just the date instead of date and time?[/quote]

Yes you are right.

[eluser]Mutsop[/eluser]
Well does it actually matter if you order by date/time or date?
I mean thats the part I don't get...

If you really insist on doing other wise, what I would suggest is:
Format your timestamp to just a date and work from there.

[eluser]Zahidul Ripon[/eluser]
i wnat to know the syntax of writing sql query.

Example
if the sql query is ORDER BY fieldname;
we write in codeigniter $this->db->order_by('fieldname');
BUT
if the sql query is ORDER BY date(fieldname);//fieldname is timestamp like 2010-06-05 13:30:00
What is the syntax writing this query in codeignitor??

[eluser]Mutsop[/eluser]
Well technicly that should work, maybe use the " instead of '

Just set DESC or ASC next to it.
(fieldname being the datestamp columnname)

Code:

$this->db->order_by = "fieldname DESC";

[eluser]Zahidul Ripon[/eluser]
[quote author="Mutsop" date="1289399358"]Well technicly that should work, maybe use the " instead of '

Just set DESC or ASC next to it.
(fieldname being the datestamp columnname)

Code:

$this->db->order_by = "fieldname DESC";

[/quote]

You didn't understand my question. I mean ORDER BY date(timestmpfield);

timestampfield=2010-06-05 13:30:00
date(timestmpfield)=2010-06-05

[eluser]Mutsop[/eluser]
Well it doesn't matter whether you order by timestampfield or date(timestampfield) which I think doesn't work.
Your ordering would be the same if the date part would work.

I don't get why you only want to order by date, instead of date+time which is less code and logical.

For example:
only date:

Code:

2010-06-06
2010-06-06
2010-06-05
2010-06-05
2010-06-04

date+time:

Code:

2010-06-06 13:30:00
2010-06-06 12:00:00
2010-06-05 17:00:00
2010-06-05 13:30:00
2010-06-04 15:00:00

Your date ordering is the same. But the date+time would take less code to use...