1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Sorted fields by date/time

Discussion in 'MySQL' started by pmf123, Feb 6, 2017.

0
  1. #1
    I have a bunch of data with dates in the 'dt' column.

    When i try and sort them by the column, 'dt', it doesn't get the dates in the correct order.

    It seems random, or just sorting by name of day followed by day of month (see below)

    Example:

    select dt from episodes order by dt desc;

    | Fri, 1 Apr 2016 20:00:00 GMT |
    | Fri, 09 Sep 2016 00:00:00 +0000 |
    | Fri, 09 Sep 2011 00:00:00 +0000 |
    | Fri, 09 Oct 2015 18:00:00 +0000 |
    | Fri, 09 Oct 2015 00:00:00 +0000 |
    | Fri, 09 Nov 2012 00:00:00 +0000 |
    | Fri, 09 May 2014 10:08:05 GMT |
    | Fri, 09 May 2014 00:00:00 +0000 |
    | Fri, 09 May 2014 00:00:00 +0000 |
    | Fri, 09 May 2014 00:00:00 +0000 |
    | Fri, 09 Mar 2012 00:00:00 +0000 |
    | Fri, 09 Jan 2015 00:00:00 +0000 |
    | Fri, 09 Jan 2015 00:00:00 +0000 |
    | Fri, 09 Dec 2016 17:15:00 +0000 |
    | Fri, 09 Dec 2016 00:00:00 +0000 |
    | Fri, 09 Dec 2011 00:00:00 +0000 |
    | Fri, 09 Aug 2013 00:00:00 +0000 |
    | Fri, 08 Nov 2013 00:00:00 +0000 |
    | Fri, 08 May 2015 15:49:59 GMT |
    | Fri, 08 May 2015 15:35:09 GMT |
    | Fri, 08 May 2015 00:00:00 +0000 |
    | Fri, 08 May 2015 00:00:00 +0000 |
    | Fri, 08 May 2015 00:00:00 +0000 |
    | Fri, 08 Mar 2013 00:00:00 +0000 |
    | Fri, 08 Jun 2012 00:00:00 +0000 |
    | Fri, 08 Jul 2016 22:00:00 +0000 |
    | Fri, 08 Jul 2016 00:00:00 +0000 |
    | Fri, 08 Jul 2011 00:00:00 +0000 |
    | Fri, 08 Jan 2016 17:00:00 +0000 |
    | Fri, 08 Jan 2016 00:00:00 +0000 |
    | Fri, 08 Feb 2013 00:00:00 +0000 |
    | Fri, 08 Aug 2014 00:00:00 +0000 |
    | Fri, 08 Aug 2014 00:00:00 +0000 |
    | Fri, 08 Aug 2014 00:00:00 +0000 |
    | Fri, 08 Apr 2016 16:00:00 +0000 |
    | Fri, 08 Apr 2016 00:00:00 +0000 |
    | Fri, 07 Sep 2012 08:46:03 GMT |
    | Fri, 07 Sep 2012 00:00:00 +0000 |
    | Fri, 07 Oct 2016 22:00:00 +0000 |
    | Fri, 07 Oct 2016 20:00:00 GMT |
    | Fri, 07 Oct 2016 00:00:00 +0000 |
    | Fri, 07 Oct 2011 00:00:00 +0000 |
    | Fri, 07 Nov 2014 14:00:11 GMT |
    | Fri, 07 Nov 2014 00:00:00 +0000 |
    | Fri, 07 Nov 2014 00:00:00 +0000 |
    | Fri, 07 Mar 2014 00:00:00 +0000 |
    | Fri, 07 Jun 2013 00:00:00 +0000 |
    | Fri, 07 Jun 2013 00:00:00 +0000 |
    | Fri, 07 Feb 2014 00:00:00 +0000 |
    | Fri, 07 Dec 2012 00:00:00 +0000 |
    | Fri, 07 Aug 2015 18:00:00 +0000 |
    | Fri, 07 Aug 2015 09:00:00 GMT |
    | Fri, 07 Aug 2015 00:00:00 +0000 |
    | Fri, 06 Sep 2013 00:00:00 +0000 |
    | Fri, 06 Sep 2013 00:00:00 +0000 |
    | Fri, 06 Nov 2015 21:00:00 +0000 |
    | Fri, 06 Nov 2015 00:00:00 +0000 |
    | Fri, 06 May 2016 16:29:00 +0000 |
    | Fri, 06 May 2016 00:00:00 +0000 |
    | Fri, 06 Mar 2015 00:00:00 +0000 |
    | Fri, 06 Jun 2014 00:00:00 +0000 |
    | Fri, 06 Jun 2014 00:00:00 +0000 |
    | Fri, 06 Jun 2014 00:00:00 +0000 |
    | Fri, 06 Jul 2012 00:00:00 +0000 |
    | Fri, 06 Jan 2017 21:00:00 +0000 |
    | Fri, 06 Jan 2017 00:00:00 +0000 |
    | Fri, 06 Jan 2012 00:00:00 +0000 |
    | Fri, 06 Feb 2015 00:00:00 +0000 |
    | Fri, 06 Feb 2015 00:00:00 +0000 |
    | Fri, 06 Dec 2013 00:00:00 +0000 |
    | Fri, 05 Sep 2014 00:00:00 +0000 |
    | Fri, 05 Sep 2014 00:00:00 +0000 |
    | Fri, 05 Sep 2014 00:00:00 +0000 |
    | Fri, 05 Oct 2012 00:00:00 +0000 |
    | Fri, 05 Jun 2015 16:00:00 +0000 |
    | Fri, 05 Jun 2015 07:04:44 GMT |
    | Fri, 05 Jun 2015 00:00:00 +0000 |
    | Fri, 05 Jul 2013 13:14:09 GMT |
    | Fri, 05 Jul 2013 00:00:00 +0000 |
    | Fri, 05 Feb 2016 19:58:24 GMT |
    | Fri, 05 Feb 2016 17:33:00 +0000 |
    | Fri, 05 Feb 2016 00:00:00 +0000 |
    | Fri, 05 Dec 2014 10:48:23 GMT |
    | Fri, 05 Dec 2014 00:00:00 +0000 |
    | Fri, 05 Dec 2014 00:00:00 +0000 |
    | Fri, 05 Aug 2016 16:49:03 GMT |
    | Fri, 05 Aug 2016 16:11:00 +0000 |
    | Fri, 05 Aug 2016 14:26:57 +0000 |
    | Fri, 05 Aug 2016 00:00:00 +0000 |
    | Fri, 05 Aug 2011 00:00:00 +0000 |
    | Fri, 05 Apr 2013 00:00:00 +0000 |
    | Fri, 04 Sep 2015 16:00:00 +0000 |
    | Fri, 04 Sep 2015 00:00:00 +0000 |
    | Fri, 04 Oct 2013 00:00:00 +0000 |
    | Fri, 04 Oct 2013 00:00:00 +0000 |
    | Fri, 04 Nov 2016 20:00:00 GMT |
    | Fri, 04 Nov 2016 17:45:00 +0000 |
    | Fri, 04 Nov 2016 13:59:13 +0000 |
    | Fri, 04 Nov 2016 00:00:00 +0000 |
    | Fri, 04 Nov 2011 00:00:00 +0000 |
    | Fri, 04 May 2012 00:00:00 +0000 |
    | Fri, 04 Mar 2016 21:00:00 +0000 |
    | Fri, 04 Mar 2016 20:16:00 GMT |
    | Fri, 04 Mar 2016 00:00:00 +0000 |
    | Fri, 04 Jul 2014 00:00:00 +0000 |
    | Fri, 04 Jul 2014 00:00:00 +0000 |
    | Fri, 04 Jan 2013 00:00:00 +0000 |
    | Fri, 04 Feb 2011 08:30:00 GMT |
    | Fri, 04 Dec 2015 00:00:00 +0000 |
    | Fri, 04 Apr 2014 00:00:00 +0000 |
    | Fri, 04 Apr 2014 00:00:00 +0000 |
    | Fri, 03 Oct 2014 00:00:00 +0000 |
    | Fri, 03 Oct 2014 00:00:00 +0000 |
    | Fri, 03 May 2013 00:00:00 +0000 |
    | Fri, 03 May 2013 00:00:00 +0000 |
    | Fri, 03 Jun 2016 20:00:00 +0000 |
    | Fri, 03 Jun 2016 00:00:00 +0000 |
    | Fri, 03 Jun 2011 00:00:00 +0000 |
    | Fri, 03 Jul 2015 16:58:35 GMT |
    | Fri, 03 Jul 2015 00:00:00 +0000 |
    | Fri, 03 Jan 2014 00:00:00 +0000 |
    | Fri, 03 Jan 2014 00:00:00 +0000 |
    | Fri, 03 Feb 2017 20:00:00 +0000 |
    | Fri, 03 Feb 2017 18:57:00 +0000 |
    | Fri, 03 Feb 2017 16:13:00 +0000 |
    | Fri, 03 Feb 2017 00:19:00 +0000 |
    | Fri, 03 Feb 2017 00:00:00 +0000 |
    | Fri, 03 Feb 2012 00:00:00 +0000 |
    | Fri, 03 Apr 2015 10:08:44 GMT |
    | Fri, 02 Sep 2016 16:15:00 +0000 |
    | Fri, 02 Sep 2016 00:00:00 +0000 |
    | Fri, 02 Sep 2011 00:00:00 +0000 |
    | Fri, 02 Oct 2015 18:00:00 +0000 |
    | Fri, 02 Oct 2015 00:00:00 +0000 |
    | Fri, 02 Nov 2012 00:00:00 +0000 |
    | Fri, 02 May 2014 00:00:00 +0000 |
    | Fri, 02 May 2014 00:00:00 +0000 |
    | Fri, 02 May 2014 00:00:00 +0000 |
    | Fri, 02 Mar 2012 00:00:00 +0000 |
    | Fri, 02 Jan 2015 00:00:00 +0000 |
    | Fri, 02 Jan 2015 00:00:00 +0000 |
    | Fri, 02 Dec 2016 20:10:00 +0000 |
    | Fri, 02 Dec 2016 19:25:00 +0000 |
    | Fri, 02 Dec 2016 00:00:00 +0000 |
    | Fri, 02 Dec 2011 00:00:00 +0000 |
    | Fri, 02 Aug 2013 00:00:00 +0000 |
    | Fri, 01 Nov 2013 00:00:00 +0000 |
    | Fri, 01 May 2015 00:00:00 +0000 |
    | Fri, 01 May 2015 00:00:00 +0000 |
    | Fri, 01 Mar 2013 00:00:00 +0000 |
    | Fri, 01 Jun 2012 00:00:00 +0000 |
    | Fri, 01 Jul 2016 20:00:00 +0000 |
    | Fri, 01 Jul 2016 00:00:00 +0000 |
    | Fri, 01 Jul 2011 00:00:00 +0000 |
    | Fri, 01 Jan 2016 17:00:00 +0000 |
    | Fri, 01 Jan 2016 11:29:22 GMT |
    | Fri, 01 Feb 2013 00:00:00 +0000 |
    | Fri, 01 Aug 2014 08:58:49 GMT |
    | Fri, 01 Aug 2014 00:00:00 +0000 |
    | Fri, 01 Aug 2014 00:00:00 +0000 |
    | Fri, 01 Apr 2016 14:24:50 +0000 |
    | Fri, 01 Apr 2016 11:30:00 GMT |
    | Fri, 01 Apr 2016 00:00:00 +0000 |
    +---------------------------------+

    Help!
     
    pmf123, Feb 6, 2017 IP
  2. pmf123

    pmf123 Notable Member

    Messages:
    1,447
    Likes Received:
    75
    Best Answers:
    0
    Trophy Points:
    215
    #2
    Example formats in my data:

    Wed, 22 Jul 2015 02:00:00 GMT
    Sat, 16 Feb 2013 00:00:00 +0000

    This should work, but doesn't:

    mysql> select dt from episodes order by STR_TO_DATE(`dt`,'%m/%d/%Y %h:%i:%s %p') desc;
    +-------------------------------+
    | dt |
    +-------------------------------+
    | Wed, 22 Jul 2015 02:00:00 GMT |
    | Fri, 18 Dec 2015 20:00:00 GMT |
    | Sun, 3 Jan 2016 20:00:00 GMT |
    | Fri, 8 Jan 2016 20:00:00 GMT |
    | Fri, 15 Jan 2016 20:00:00 GMT |
    | Fri, 22 Jan 2016 20:00:00 GMT |
    | Fri, 29 Jan 2016 20:00:00 GMT |
    | Fri, 5 Feb 2016 20:00:00 GMT |
    | Fri, 12 Feb 2016 20:00:00 GMT |
    | Fri, 19 Feb 2016 20:00:00 GMT |
    | Fri, 26 Feb 2016 20:00:00 GMT |
    | Fri, 4 Mar 2016 20:00:00 GMT |
    | Fri, 11 Mar 2016 20:00:00 GMT |
    | Fri, 18 Mar 2016 20:00:00 GMT |
    | Fri, 25 Mar 2016 20:00:00 GMT |
    | Fri, 1 Apr 2016 20:00:00 GMT |
    | Fri, 8 Apr 2016 20:00:00 GMT |
    | Fri, 15 Apr 2016 20:00:00 GMT |
    | Fri, 11 Dec 2015 20:00:00 GMT |
    | Fri, 27 Nov 2015 20:00:00 GMT |
    | Fri, 20 Nov 2015 20:00:00 GMT |
    | Mon, 27 Jul 2015 23:00:00 GMT |
    | Sat, 1 Aug 2015 21:00:00 GMT |
    | Fri, 7 Aug 2015 21:00:00 GMT |
    | Fri, 14 Aug 2015 22:00:00 GMT |
    | Fri, 21 Aug 2015 20:00:00 GMT |
    | Fri, 28 Aug 2015 20:00:00 GMT |
    | Fri, 4 Sep 2015 20:00:00 GMT |
    | Fri, 11 Sep 2015 20:00:00 GMT |
    | Fri, 18 Sep 2015 20:00:00 GMT |
    | Fri, 25 Sep 2015 20:00:00 GMT |
    | Fri, 2 Oct 2015 20:00:00 GMT |
    | Fri, 9 Oct 2015 20:00:00 GMT |
    | Fri, 16 Oct 2015 20:00:00 GMT |
    | Fri, 23 Oct 2015 20:00:00 GMT |
    | Fri, 30 Oct 2015 20:00:00 GMT |
    | Fri, 6 Nov 2015 20:00:00 GMT |
    | Fri, 13 Nov 2015 20:00:00 GMT |
    | Fri, 22 Apr 2016 20:00:00 GMT |
    | Fri, 29 Apr 2016 20:00:00 GMT |
    | Wed, 14 Sep 2016 20:00:00 GMT |
    | Fri, 23 Sep 2016 20:00:00 GMT |
    | Fri, 30 Sep 2016 20:00:00 GMT |
    | Fri, 07 Oct 2016 20:00:00 GMT |
    | Fri, 14 Oct 2016 20:00:00 GMT |
    | Fri, 21 Oct 2016 20:00:00 GMT |
    | Fri, 28 Oct 2016 20:00:00 GMT |
    | Fri, 04 Nov 2016 20:00:00 GMT |
    | Fri, 11 Nov 2016 20:00:00 GMT |
    | Fri, 18 Nov 2016 20:00:00 GMT |
    | Fri, 25 Nov 2016 20:00:00 GMT |
    | Fri, 16 Dec 2016 20:00:00 GMT |
    | Fri, 23 Dec 2016 20:00:00 GMT |
    | Fri, 30 Dec 2016 20:00:00 GMT |
    | Fri, 6 Jan 2017 20:00:00 GMT |
    | Fri, 13 Jan 2017 20:00:00 GMT |
    | Fri, 27 Jan 2017 20:00:00 GMT |
    | Fri, 9 Sep 2016 20:00:00 GMT |
    | Fri, 2 Sep 2016 20:00:00 GMT |
    | Fri, 26 Aug 2016 20:00:00 GMT |
    | Fri, 6 May 2016 20:00:00 GMT |
    | Fri, 13 May 2016 20:00:00 GMT |
    | Fri, 20 May 2016 20:00:00 GMT |
    | Fri, 27 May 2016 20:00:00 GMT |
    | Fri, 3 Jun 2016 20:00:00 GMT |
    | Fri, 10 Jun 2016 20:00:00 GMT |
    | Fri, 17 Jun 2016 20:00:00 GMT |
    | Fri, 24 Jun 2016 20:00:00 GMT |
    | Fri, 1 Jul 2016 20:00:00 GMT |
    | Fri, 8 Jul 2016 20:00:00 GMT |
    | Fri, 15 Jul 2016 20:00:00 GMT |
    | Fri, 22 Jul 2016 20:00:00 GMT |
    | Fri, 29 Jul 2016 20:00:00 GMT |
    | Fri, 5 Aug 2016 20:00:00 GMT |
    | Fri, 12 Aug 2016 20:00:00 GMT |
    | Fri, 19 Aug 2016 20:00:00 GMT |
    | Wed, 24 Aug 2016 20:00:00 GMT |
    | Fri, 3 Feb 2017 20:00:00 GMT |
    +-------------------------------+
    78 rows in set, 78 warnings (0.06 sec)

    mysql>

    More out of order results:

    | Sun, 06 Oct 2013 00:00:00 +0000 |
    | Sun, 29 Sep 2013 00:00:00 +0000 |
    | Sun, 22 Sep 2013 00:00:00 +0000 |
    | Sun, 12 Mar 2000 00:00:00 +0000 |
    | Sat, 03 Mar 1979 00:00:00 +0000 |
    | Sat, 19 Mar 1977 00:00:00 +0000 |
    | Sun, 26 Jul 1987 00:00:00 +0000 |
    | Sun, 30 Nov 2003 00:00:00 +0000 |
    | Sun, 11 Aug 2013 00:00:00 +0000 |
    | Sun, 04 Aug 2013 00:00:00 +0000 |
    | Sun, 28 Jul 2013 00:00:00 +0000 |
    | Sun, 21 Jul 2013 00:00:00 +0000 |
    | Sun, 14 Jul 2013 00:00:00 +0000 |
     
    pmf123, Feb 6, 2017 IP
  3. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #3
    They're not out of order. You do, however, have a very fucked up datetime field. Store the content properly, in a proper datetime column, and the sorting will work just fine.
     
    PoPSiCLe, Feb 6, 2017 IP
  4. pmf123

    pmf123 Notable Member

    Messages:
    1,447
    Likes Received:
    75
    Best Answers:
    0
    Trophy Points:
    215
    #4
    Unfortunately this data comes from RSS feeds in those formats, so I can't alter the original source.

    Is there a way to fix them before they are saved by me into the database?
     
    pmf123, Feb 6, 2017 IP
  5. pmf123

    pmf123 Notable Member

    Messages:
    1,447
    Likes Received:
    75
    Best Answers:
    0
    Trophy Points:
    215
    #5
    I figured it out...

    Before storing them as an integer, I did this:

    $dt = strtotime($dt);
     
    pmf123, Feb 6, 2017 IP