Find the answer to your Linux question:
Results 1 to 9 of 9
I have a dump of a database that I need to find patterns in. The trouble is that all columns are defined as varchar(100) and now I have trouble sorting ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Linux Engineer Freston's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    1,049

    [solved]Sort MySQL by DD-MM-YYYY without altering data


    I have a dump of a database that I need to find patterns in.

    The trouble is that all columns are defined as varchar(100) and now I have trouble sorting by date. If all dates where following the system friendly YYYYMMDD format all would be well but they follow the local convention of DD-MM-YYYY

    I could easily cope with altering the dates using sed or awk on the dumpfile, but I am not allowed to alter the data in any way.


    So I'm looking for a way to tell MySQL to treat the Foo_Time column in the table as a date field formatted as DD-MM-YYYY and thus sort 9-9-2011 before 1-10-2011
    Last edited by Freston; 10-26-2011 at 02:26 PM.
    Can't tell an OS by it's GUI

  2. #2
    Trusted Penguin Irithori's Avatar
    Join Date
    May 2009
    Location
    Munich
    Posts
    3,410
    I talked with one of my dbas, and techically there is a way to sort that by the use of substring(...) || substring(...) || substring(...) etc.

    But the performance will be abysmal and he strongly suggests to convert the dates once in the DB and fix the application logic accordingly.
    You must always face the curtain with a bow.

  3. #3
    Linux User
    Join Date
    Dec 2009
    Posts
    264
    Hi,
    I don't get why you mustn't alter a dump.
    You may wanna create a dump of the dump and modify that one.

    Well anyway, you can also create a new table containing the line index and a date-time field for the order.
    You can fill it with a SELECT index, date as datetime FROM db INTO dates_db;
    If you wanna order by date you can simply use NATJOIN like
    SELECT * FROM db NATJOIN dates_db ORDER BY datetime;

    Or you simply change the type of column to date, should be fine 2.

  4. $spacer_open
    $spacer_close
  5. #4
    Linux Engineer Kloschüssel's Avatar
    Join Date
    Oct 2005
    Location
    Italy
    Posts
    773
    Or you read the mysql manuals here and find out that there's a way, namely this function. Indeed the performance is LOW, but you can cast strings to datetimes with a custom format string and use that column in the order clause.

    Anyway, if the column is always a datetime I share the others opinion and strongly recommend to alter the database. The most common way is to add a new column:

    alter table <table> add <converted_column> datetime;
    update <table> set <converted_column>=str_to_date(<column>, format);

    cheers

  6. #5
    Linux Engineer Freston's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    1,049
    Hey thanks!
    Quote Originally Posted by Irithori
    I talked with one of my dbas, and techically there is a way to sort that by the use of substring(...) || substring(...) || substring(...) etc.
    I read in that it's not a native supported feature of MySQL... I was afraid of that.


    Quote Originally Posted by Irithori
    But the performance will be abysmal (...)
    Hmmmmm performance is nice but it's not a big consideration.


    Quote Originally Posted by Irithori
    (...) and he strongly suggests to convert the dates once in the DB and fix the application logic accordingly.
    Alas this is not an option.
    Can't tell an OS by it's GUI

  7. #6
    Linux Engineer Freston's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    1,049
    Quote Originally Posted by Kloschüssel
    Or you read the mysql manuals here and find out that there's a way, namely this function. Indeed the performance is LOW, but you can cast strings to datetimes with a custom format string.
    Heh, yes I was reading the MySQL manuals for longer than I care to admit. But I did not notice the function you mention. A thousand thanks! I'll try that
    Can't tell an OS by it's GUI

  8. #7
    Linux Engineer Freston's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    1,049
    Hmmm, sometimes I wish I was a better geek. I created a testdatabase...
    Code:
    mysql> use test
    Database changed
    mysql> show tables;
    Empty set (0.00 sec)
    
    mysql> create table datetest ( id int not null auto_increment, date varchar(50), tekst varchar(50), primary key (id));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> desc datetest;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | date  | varchar(50) | YES  |     | NULL    |                |
    | tekst | varchar(50) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    mysql> insert into datetest (date, tekst) values ("1-9-2000","haha");
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into datetest (date, tekst) values ("3-10-2000","hihi");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into datetest (date, tekst) values ("17-10-2000","hoho");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into datetest (date, tekst) values ("9-11-2000","hehe");
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show tables;
    +----------------+
    | Tables_in_test |
    +----------------+
    | datetest       |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from datetest;
    +----+------------+-------+
    | id | date       | tekst |
    +----+------------+-------+
    |  1 | 1-9-2000   | haha  |
    |  2 | 3-10-2000  | hihi  |
    |  3 | 17-10-2000 | hoho  |
    |  4 | 9-11-2000  | hehe  |
    +----+------------+-------+
    4 rows in set (0.00 sec)
    Now I want to select tekst order by date but the str_to_date() function poses an immediate problem for me as it's syntax is STR_TO_DATE(str,format) and I'm at a loss as to how I can feed the value of 'date' as str to str_to_date and use it for sorting the entire table.


    _____


    Quote Originally Posted by zombykillah
    Well anyway, you can also create a new table containing the line index and a date-time field for the order.
    That is starting to look like a good option, although it would mean using an external reference (second table) to use for sorting data in the database table. Not sure if I can do that.


    Quote Originally Posted by zombykillah
    I don't get why you mustn't alter a dump.
    You may wanna create a dump of the dump and modify that one.
    A valid question. Let's just say that if I could do that, I didn't need to come here and ask for advice. It's an event log from one of our systems and I need to use 100% exact same data as is in the log for verifiability and repeatability of certain queries.
    Can't tell an OS by it's GUI

  9. #8
    Linux User
    Join Date
    Dec 2009
    Posts
    264
    STR_TO_DATE(str,format) : STR_TO_DATE*«*Date Time Functions*«*MySQL Tutorial
    STR_TO_DATE(date_string,"%e-%c-%Y")

    Should do it in your case.

    How I would try the function:
    Code:
    select * from datetest ORDER BY STR_TO_DATE(date,"%e-%c-%Y");

  10. #9
    Linux Engineer Freston's Avatar
    Join Date
    Mar 2007
    Location
    The Netherlands
    Posts
    1,049
    Excellent! That does the trick!

    Thanks very much
    Can't tell an OS by it's GUI

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •