How to select rows that are added in a period of time?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

How to select rows that are added in a period of time?

Post by MicroBoy »

How to select rows that are added in a period of time, example in a month? I use TEXT field for date, because the format date that I want to use is dd/MM/yyyy. And if I use a Date field the format will not be like this. I saw a lot of tutorials but they all use the other format of date.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to select rows that are added in a period of time?

Post by califdon »

I would advise against ever using anything but the native DATE storage format when representing a date. You can format the date any way you want, using DATE() function, but you will have the ability to select by month, for example, as you want to do, quite easily, but if you start out storing the date as a text string, you will have to invent your own functions for parsing the components of the date, and you will be at the mercy of users entering invalid dates. Just study the way MySQL (assuming that's the database you're using) stores dates and times, and how you can format them with PHP DATE() function.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: How to select rows that are added in a period of time?

Post by Eric! »

I totally agree with calfidon.

I know this query works with datetime fields, but I don't think it will work properly for text fields for all cases because it won't know months from days or years

Code: Select all

SELECT event FROM `table` WHERE dates>'01/05/2009' AND dates<'01/09/2009' group by event
You can test it... :| I'm quite certain it won't work.
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: How to select rows that are added in a period of time?

Post by MicroBoy »

Yes I'm using mysql, and do you suggest to change date format in output? Or can I change the date format inside mysql?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to select rows that are added in a period of time?

Post by califdon »

MicroBoy wrote:Yes I'm using mysql, and do you suggest to change date format in output? Or can I change the date format inside mysql?
Read the last sentence of my previous post.

STORE all dates as DATE data types in the database. Use PHP to display dates in any format you want, wherever you want, whenever you want. If you already have data in your database, be very careful about changing data types! Don't just change a VARCHAR to a DATE or you will risk losing your data! Create a new field of DATE data type, then use SQL to populate the new field with the proper data from the old field. Then you can check whether the translation gives you valid results. When you are sure that it's correct, you can delete the old field.
MicroBoy
Forum Contributor
Posts: 112
Joined: Sat Mar 14, 2009 5:16 pm

Re: How to select rows that are added in a period of time?

Post by MicroBoy »

Thnx a lot for help:D, finally I made it.
Post Reply