Page 1 of 1

DATETIME/NOW() not sorting correctly?

Posted: Sun Jan 07, 2007 11:35 am
by seodevhead
Hey guys... I have built a web application that allows users to input data into the database, and each entry is marked with the date and time in its seperate DATETIME DB column using the NOW() mysql function inside the INSERT query.

But now that it has passed new years... I've noticed that all new entires in 2007 are being placed at the bottom of the sort order when SELECT'ing all records from the DB and using ORDER BY datetime_col (which is the NOW() DATETIME column). Why is this happening? Is NOW() or DATETIME not a good function/type to use to record the date and time of a DB record insert? What can I do to fix this or is there another solution? Thanks a bunch for any help.

Posted: Sun Jan 07, 2007 11:38 am
by feyd
Table schema? Exact query?

Posted: Sun Jan 07, 2007 11:45 am
by seodevhead
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]
CREATE TABLE posts (
        id MEDIUMINT NOT NULL AUTO_INCREMENT,
        post TEXT NOT NULL,
        datetime_col DATETIME NOT NULL,
        PRIMARY KEY (id)
);
[/syntax]

Code: Select all

$query9 = "INSERT INTO posts (post, datetime_col) VALUES ('{$clean['post']}', NOW() )";
$result9 = @mysql_query($query9) or trigger_error("Query: $query9\n<br />MySQL Error: " . mysql_error());

Code: Select all

$query1 = "SELECT id, post, DATE_FORMAT(datetime_col, '%c/%d/%y') AS date_posted FROM posts ORDER BY date_posted DESC";
$result1 = @mysql_query($query1) or trigger_error("Query: $query1\n<br />MySQL Error: " . mysql_error());

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Sun Jan 07, 2007 11:56 am
by timvw
seodevhead wrote: SELECT id, post, DATE_FORMAT(datetime_col, '%c/%d/%y') AS date_posted FROM posts ORDER BY date_posted DESC
You are ordering by the string that is generated with DATE_FORMAT... What you seem to want is ORDER BY datetime_col DESC...

Posted: Sun Jan 07, 2007 12:15 pm
by seodevhead
Man..... good god I'm an idiot. Thanks so much for pointing that out to me. I have starred at this code for the last 24 hrs and didn't even see that. I gotta take a break :cry:

Thanks tim