MySQL - sort records by date field with format DD/MM/YYYY HH:MM ?

sabecs
sabecs used Ask the Experts™
on
Hi,
If I have a table as below, how would I sort records by `date_created` column where date input is in format DD/MM/YYYY HH:MM ?

Thanks in advance for your feedback.


CREATE TABLE IF NOT EXISTS `blogs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` varchar(30) DEFAULT NULL,
  `blogName` varchar(50) DEFAULT NULL,
  `allowComments` tinyint(4) DEFAULT '1',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=154 ;

--
-- Dumping data for table `blogs`
--

INSERT INTO `blogs` (`id`, `date_created`, `blogName`, `allowComments`) VALUES
(134, '18/02/2012 09:00 ', 'Registration Day', 0),
(135, '18/03/2012 22:35 ', 'Level 1 Coaches Course',0),
(140, '12/04/2012 19:00 ', 'Sample Heading 2', 1),
(141, '13/04/2012 18:22 ', 'Dates for Term Two',0),
(142, '20/04/2012 11:00 ', 'Sample Heading 2', 0),
(143, '13/06/2012 18:22 ', 'Sample Heading 3',0),
(144, '20/04/2012 11:00 ', 'Sample Heading 4', 0),
(145, '11/03/2012 22:36 ', 'Presentation Day', 0);
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ORDER by `date_created` DESC
Top Expert 2012
Commented:
Any reason why you want to store date as varchar and not datetime?  You can use "order by str_to_date(date_created, '%d/%m/%Y %H:%i')" but this is very inefficient.  For one thing, datetime uses just 8 bytes vs needing 16 to store 'dd/mm/yyyy hh:mm'.  More importantly, doing "order by str_to_date" will not allow you to use any index on the date_created column.  

It's better to store datetime as datetime, then just format the output the way you prefer it i.e.
"select date_format(date_created, '%d/%m/%Y %H:%i'), blogName from blogs order by date_created"
mySQL convert varchar to date

alter table blog add (realdate timestamp );
update table set realdate = str_to_date(date_created, '%M/%d/%Y %h:%i');

Author

Commented:
Thanks for your help and comments, the date time format is coming from a jQuery calendar so is it possible to sort it woithout chnaging the format in the table?
Top Expert 2012

Commented:
Thanks for your help and comments, the date time format is coming from a jQuery calendar so is it possible to sort it woithout chnaging the format in the table?

You already have your answer to this above.  "order by str_to_date(date_created, '%d/%m/%Y %H:%i')" will order the results, but not the format of the results.  However, we are telling you this is not the ideal way.  If jQuery gives the date in such a format then use str_to_date to store it as datetime when you write it to the table.  Then when you need to show it in a specific format then use date_format.  It seems more work but it is better this way as you'll get better performance and can do more calculations on the data later on, since it is stored using the native datetime datatype.

Author

Commented:
Thanks for your help, much appreciated..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial