Sort MySQL by DD-MM-YYYY without altering data

[Freston]

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

[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.

But the performance will be abysmal and he strongly suggests to convert the dates once in the DB and fix the application logic accordingly.

[zombykillah]

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.

[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 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

[Freston]

Hey thanks!

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.

But the performance will be abysmal (...)

Hmmmmm performance is nice but it's not a big consideration.

(...) and he strongly suggests to convert the dates once in the DB and fix the application logic accordingly.

Alas this is not an option.

[Freston]

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

[Freston]

Hmmm, sometimes I wish I was a better geek. I created a testdatabase...

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.


_____

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.

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.

[zombykillah]

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:

select * from datetest ORDER BY STR_TO_DATE(date,"%e-%c-%Y");

[Freston]

Excellent! That does the trick!

Thanks very much