DATETIME/NOW() not sorting correctly?

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
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

DATETIME/NOW() not sorting correctly?

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Table schema? Exact query?
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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]
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Post 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
Post Reply