Datetime Convert - Trying to sort by date by datetime

[Nuv]

I'm trying to sort by my logbook with datetime. However, i am getting strange results when i use "ORDER BY datetime DESC". I searched over net and realized i need to use CONVERT. Though i am not able to understand it. Can someone please explain me

 

My table structure -

 

CREATE TABLE IF NOT EXISTS `logbook` (
  `id` int( NOT NULL AUTO_INCREMENT,
  `category` varchar(100) NOT NULL,
  `user` varchar(100) NOT NULL,
  `description` varchar(300) NOT NULL,
  `datetime` datetime NOT NULL,
  `userinfo` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

 

Query I am using -

 

"SELECT * FROM `logbook` ORDER BY CONVERT(datetime, date, 103) LIMIT 10"

 

Though i am not getting any result. Can someone please point me in the right direction.

 

[PravinS]

No need to use "CONVERT", just use "ORDER BY datetime". Also if possible change the field name "datetime" as it is reserved word

[Nuv]

As i mentioned i already did that. Im getting the following result

 

 

If you'll notice,it hasn't been sorted in any order(either ASC or DESC of datetime).

[Illusion]

try this

SELECT * FROM `logbook` ORDER BY (UNIX_TIMESTAMP(`datetime`)) asc

[Nuv]

Not working. I'm getting the same result. Here is my full code i am using with full logbook.sql dump.

 

  <?php
                                $sort = mysql_query("SELECT * FROM `logbook` ORDER BY (UNIX_TIMESTAMP(`datetime`)) ASC");    
                                while ($row = mysql_fetch_array($sort)) {
                                ?>
                                <tr>
                                    <td><?php echo $row[2] ?></td>
                                    <td><?php echo $row[1] ?></td>
                                    <td><?php echo $row[4] ?></td>
                                    <td><a href="#"><?php echo $row[3] ?></a></td>
                                </tr>
                            <?php } ?>    

 

SQL Dump

 

CREATE TABLE IF NOT EXISTS `logbook` (
  `id` int( NOT NULL AUTO_INCREMENT,
  `category` varchar(100) NOT NULL,
  `user` varchar(100) NOT NULL,
  `description` varchar(300) NOT NULL,
  `datetime` datetime NOT NULL,
  `userinfo` varchar(500) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `logbook`
--

INSERT INTO `logbook` (`id`, `category`, `user`, `description`, `datetime`, `userinfo`) VALUES
(1, 'Backup File', '', 'File added with path = D:/Music/', '2012-06-05 09:09:51', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 10:09:51 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'),
(2, 'Backup File', '', 'File added with path = D:/Company/vroomweb/', '2012-06-05 11:19:47', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 12:19:47 PM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'),
(3, 'Backup File', '', 'File added with path = D:/Company/Form1.pdf', '2012-06-05 11:19:47', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"05/06/2012 12:19:47 PM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:29:"/file:/D:/HTML/storefile.php?";}'),
(7, 'User Login', 'Unknown User', 'Login attempt on admin', '2012-06-06 06:20:06', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:20:06 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}'),
(8, 'User Login', 'Unknown User', 'Login attempt on admin', '2012-06-06 06:21:21', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:21:21 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}'),
(9, 'User Login', 'admin', 'admin logged in.', '2012-06-06 06:21:53', 'a:6:{s:2:"ip";s:11:"127.0.0.1\r\n";s:4:"host";s:8:"Nuv-PC\r\n";s:7:"httpvia";s:6:"Direct";s:4:"date";s:22:"06/06/2012 07:21:53 AM";s:7:"browser";s:173:"Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/5.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; InfoPath.3)";s:10:"requesturi";s:25:"/file:/D:/HTML/login.php?";}');

[Nuv]

Server version: 5.5.16

 

[Pikachu2000]

No need to use "CONVERT", just use "ORDER BY datetime". Also if possible change the field name "datetime" as it is reserved word

 

It isn't a reserved word, actually.

[Illusion]

Seems like version specific issue...

 

following query worked properly in 5.5.24.1

SELECT * FROM `logbook` ORDER BY `datetime` desc;

[Pikachu2000]

It may work in your query due to the fact that you enclosed datetime in backticks, and the OP did not. Without the backticks it may be ambiguous, so they could be needed in this instance.

[Illusion]

I didn't understand this part

 

<tr>
                                    <td><?php echo $row[2] ?></td>
                                    <td><?php echo $row[1] ?></td>
                                    <td><?php echo $row[4] ?></td>
                                    <td><a href="#"><?php echo $row[3] ?></a></td>
                                </tr>

 

if  you are scrambling the rows ... how do you expect them to be displayed in the same order you have fetched the records from database?

[PFMaBiSmAd]

In each row fetched -

$row[0] is the `id`

$row[1] is the `category`

$row[2] is the  `user`

$row[3] is the  `description`

$row[4] is the  `datetime`

$row[5] is the `userinfo`

[Illusion]

@PFMaBiSmAd

Oops... thanks for clarifying. I should have looked at HTML carefully .

[Nuv]

It may work in your query due to the fact that you enclosed datetime in backticks, and the OP did not. Without the backticks it may be ambiguous, so they could be needed in this instance.

 

Funny after using "SELECT * FROM logbook ORDER BY `datetime` ASC" , i get

 

 

And after using "SELECT * FROM logbook ORDER BY `datetime` DESC", i get

 

 

Why am i getting unexpected results ?  :'(

Although, time is perfect when i use 'DESC' but not the dates. I really wanted to sort this only with SQL and not using Php.

[PFMaBiSmAd]

The display you have posted is the result of more code that you have shown. If this isn't due to a bug in mysql, then it's likely something your code on the page is doing. If you have a whole page that doesn't work as expected, you would need to post all the code needed to reproduce that page in order to get help with the problem.

 

When you run the query directly in your favorite database management tool, do you get the expected results?

[PFMaBiSmAd]

In fact, it looks to me like your code is sorting by the username, categories, then the datetime values within each group of username/categories.

[Illusion]

Seems like the data sorted by javascript.  You need to check that.

 

[Jessica]

In fact, it looks to me like your code is sorting by the username, categories, then the datetime values within each group of username/categories.

Agreed.

 

OP, check the rest of the code. And as suggested run the query in phpMyAdmin or similar.

[ignace]

Like PFMaBiSmAd already pointed out your table appears to be sorted by username. CTRL+U and view the source whether it has been correctly sorted there.

[Nuv]

Seems like the data sorted by javascript.  You need to check that.

 

 

Yup Its sorted out by javascript. Sorry for the trouble guys, i figured after PFMaBiSmAd's post.