Hi

my problem is regarding this site www.are-you-bored.com
if you go to the bottom of the page you will see a link submited on 1/04/2005 this link should have been on top of the site, because i am using oder by date desc

but for some reason the script is seeing

01-04-2005 05:04:37 to be older then the submitions in march

can any one help with this problem.


the date is saved in this format
$date = date ("d-m-Y H:m:s");

what do i need to do? i'm lost.

You should save the dates in UNIX timestamp format, ie: Use mktime(), and the format the date when you display it.

When you convert a timestamp to date, and store it as a string, then you cannot compare them as dates. They are strings.

You have a few options; one would be what Xenatino suggested. The other would be to convert your database column format to one of the date and time formats.

a simple solution would be that if you have an auto_encrement field e.g. id

order by id desc

then the newest is always at the top regarless of the date/time.

ok can you help me convert the old dates into timedate format.

the date in the old field is stored like this 01-04-2005 05:04:07

how do i convert it to DATETIME format?

i have created a new field called newdate with DATETIME as the type.

can i run some code to get the data from the old field and place it in to the new field , in the right DATETIME format?

i have absolutely no idea.. i've always done things like that from id and never used the datetime

date is what i always use.

Originally posted by pinkegobox
a simple solution would be that if you have an auto_encrement field e.g. id

order by id desc

then the newest is always at the top regarless of the date/time.

yeah i could do that but this site is a major project of mine , i want to have a good start.

thing is sometimes there are links submitted first but aproved after the newer submited links, so i need to oder it by the aproval date. :(

Originally posted by pinkegobox
i have absolutely no idea.. i've always done things like that from id and never used the datetime

date is what i always use.

yeah this is my firs time dealing with this timestrap issue.

Because MySQL provides string to date conversion functions it should be fairly trivial to change the datatype of the column to a proper date/time type.

Your other option would be not to use an asinine date format like that. The only sane one that makes sense on the internet is yyyy-mm-dd. Anything else is ambiguous, confusing, and unsortable.

Try something like:

ALTER TABLE foo ADD COLUMN new_date TIMESTAMP;
UPDATE foo SET new_date=STR_TO_DATE(date, '%d-%m-%Y %H:%m:%s');
ALTER TABLE foo DROP COLUMN date;
ALTER TABLE foo CHANGE COLUMN new_date date TIMESTAMP;

just a guess then you retrieve the data try this $date = date($row['datestamp'])

where $row['datestamp'] is the field from the array like $row = mysql_fetch_array($result) $result being the query

just like doing stripslashes etc...
rather than just retireveing the raw data from the table.. then it would recognise it as a date i think..

Originally posted by error404
Because MySQL provides string to date conversion functions it should be fairly trivial to change the datatype of the column to a proper date/time type.

Your other option would be not to use an asinine date format like that. The only sane one that makes sense on the internet is yyyy-mm-dd. Anything else is ambiguous, confusing, and unsortable.

Try something like:

ALTER TABLE foo ADD COLUMN new_date TIMESTAMP;
UPDATE foo SET new_date=STR_TO_DATE(date, '%d-%m-%Y %H:%m:%s');
ALTER TABLE foo DROP COLUMN date;
ALTER TABLE foo CHANGE COLUMN new_date date TIMESTAMP;


thanks to alll you guys, its sorted now.

error404 i used your code and it works nicely. but instead of replacing my date field i've decide to keep both of them.

i've put this code in my query to aprove the links newdate=STR_TO_DATE('$date', '%d-%m-%Y %H:%i:%s');

so basically i echo the date field on my pages, that is the format i want. and i used the newdate field for my query to the database, so the links show in correct date order.

Originally posted by pinkegobox
just a guess then you retrieve the data try this $date = date($row['datestamp'])

where $row['datestamp'] is the field from the array like $row = mysql_fetch_array($result) $result being the query

just like doing stripslashes etc...
rather than just retireveing the raw data from the table.. then it would recognise it as a date i think..

thanks for your help but i've sorted it out now.

glad to hear you have it fixed