get Mysql database creation date with java

Question

Using mysql 5.5.2 on windows (Wamp) I'm writing a Java application that use several databases. I want to list databases names and their creation dates. Databases have the same table's only data vary (Archives). Any trick with Java?

EDIT:

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| formaction_archive_test |
| mysql                   |
| performance_schema      |
| sw_formaction           |
| test                    |
+-------------------------+
6 rows in set (0.00 sec)

"formaction_archive_test" and "sw_formaction" are both created by me "root" and they have the same tables. My problem is how to get their respective creation dates.

I found this solution:

mysql> use information_schema;
Database changed
mysql> SELECT table_name, create_time
    -> FROM tables
    -> WHERE table_schema NOT IN('mysql', 'information_schema') ;
+----------------------------------------------+---------------------+
| table_name                                   | create_time         |
+----------------------------------------------+---------------------+
| cond_instances                               | NULL                |
| events_waits_current                         | NULL                |
| events_waits_history                         | NULL                |
| events_waits_history_long                    | NULL                |
| events_waits_summary_by_instance             | NULL                |
| events_waits_summary_by_thread_by_event_name | NULL                |
| events_waits_summary_global_by_event_name    | NULL                |
| file_instances                               | NULL                |
| file_summary_by_event_name                   | NULL                |
| file_summary_by_instance                     | NULL                |
| mutex_instances                              | NULL                |
| performance_timers                           | NULL                |
| rwlock_instances                             | NULL                |
| setup_consumers                              | NULL                |
| setup_instruments                            | NULL                |
| setup_timers                                 | NULL                |
| threads                                      | NULL                |
| annee                                        | 2012-06-06 16:19:04 |
| categorie                                    | 2012-06-06 16:19:04 |
| certification                                | 2012-06-06 16:19:04 |
| client                                       | 2012-06-06 16:19:04 |
| clientold                                    | 2012-06-06 16:19:04 |
| connaisance_ecole                            | 2012-06-06 16:19:04 |
| duree                                        | 2012-06-06 16:19:04 |
| eleve                                        | 2012-06-06 16:19:04 |
| famille                                      | 2012-06-06 16:19:04 |
| formateur                                    | 2012-06-06 16:19:04 |
| formation                                    | 2012-06-06 16:19:04 |
| inscription                                  | 2012-06-06 16:19:04 |
| lieux                                        | 2012-06-06 16:19:05 |
| lst_formation                                | 2012-06-06 16:19:05 |
| moyen_paiement                               | 2012-06-06 16:19:05 |
| paiement                                     | 2012-06-06 16:19:05 |
| session                                      | 2012-06-06 16:19:05 |
| souhait                                      | 2012-06-06 16:19:05 |
| souhaits                                     | 2012-06-06 16:19:05 |
| type_certification                           | 2012-06-06 16:19:05 |
+----------------------------------------------+---------------------+
37 rows in set (0.01 sec)

But it shows me only the last created database informations. May be the solution is to see files creation date?

Answer 1

Should be this request to get it :

SELECT table_name, create_time 
FROM information_schema.tables 

You can remove information_schema and mysql schemas to get the one you created :

SELECT table_name, create_time 
FROM information_schema.tables 
WHERE table_schema NOT IN('mysql', 'information_schema')

EDIT :

To get a database creation time, the only way is to check the oldest table :

SELECT MIN(create_time)
FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'my_database'

If you want, you can insert a useless (or not) table in your database, like _config, which will never be dropped.

Answer 2

I'm not sure if there's a specific trick in Java that can help, but checking the INFORMATION_SCHEMA_TABLES table referenced here: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html might help. Possibly take the minimum creation time of all tables, and use that as the database creation time?

AFAIK, there isn't a way to check when a database was created using pure SQL.