Problems using date...

[virruss2]

Hi,

 

I have a little problem with the creation date of some webpages when trying to sort them by date.

I've stored my dates as strings, in this format: 1/08/08 (day/month/year - I've used j/m/y if I remember correctly).

Now I need to select only webpages from a particular range of time, but I cannot compare strings... For example, I need:

 

SELECT * FROM 'mytable' WHERE date > '$my_date'

 

but my date field is a string.

 

Any idea? Thank you in advance.

[Jabop]

Well you aren't using your database properly if you're storing dates as a string.

[virruss2]

Well you aren't using your database properly if you're storing dates as a string.

 

What should I do? What's the easier method in this case? Am I going to loose all my data if I change the type of that field? It's quite a lot...

[Jabop]

i would first add a date or datetime field and create a script to update them all, and then remove the date string field, keeping the new date/datetime

[virruss2]

But is there a way to use a php function, like strtotime() directly in the mysql_query for the date field? What I mean is something like this:

 

SELECT * FROM 'mytable' WHERE strtotime(date) > '$my_date'

 

Is there a way to do this?

[DarkWater]

Not that I know of, and regardless, you shouldn't store dates as strings.  It'll take 5 minutes for you to write up a script to fix the whole database...

[Jabop]

You said that you stored your dates into your table as a string using date("m/d/y") or whatever. strtotime() turns string time into unix epoch time. You could do...

 

SELECT foo FROM bar WHERE time='$var1-$var2-$var3'

[tiki]

Make your date field an INT and store dates as a unix timestamp (e.g., time()).

 

Then you simply just do a strtotime (http://us2.php.net/strtotime) and do a backwards date.

 

$pastDate = strtotime("-5 days");

 

SELECT * FROM 'mytable' WHERE date > '$pastDate '

[Jabop]

tiki's problem will solve it as well, however, your current method and tiki's method are bad practice.

 

that's why there are time related field types within sql. utilize them.

[tiki]

Yes but ive also read and spoke to many people that date/time based column types are slow and do not scale well for large applications.

[DarkWater]

They're perfectly fine.

[virruss2]

Thank you guys for your quick answers. I guess I'll try to change the type of that field.