CODEWITHSUNDEEP
mysql
Shabarinath Volam
Shabarinath Volam

Reputation: 747

Query to get tables created on a particular date

Hi here my interest is to get all the tables which are created on a particular date irrespective of time when it has been created for this in order to get created time for the list of tables I have tried:

select table_name,create_time 
from information_schema.tables 
where table_schema='database_name'

and I have got the result as

| Table_name                       | 2012-09-13 03:09:50 |  
| Table_name                       | 2012-10-01 08:05:41 |
| Table_name                       | 2012-10-01 08:05:41 |
| temp                             | 2011-05-25 03:05:50 |

Now I need to get the tables which are created on 2012-10-01 for this I have tried:

select table_name,create_time 
from information_schema.tables 
where table_schema='database_name' and create_time='2012-10-01'

and I am getting the result Empty set (5.09 sec).

Here I need to get all the tables which are created on 2012-10-01 irrespective of time.

Upvotes: 1

Views: 2033

Answers (4)

metalfight - user868766
metalfight - user868766

Reputation: 2750

As we already have other options. Please see if below can help you.

You can simply use like to get tables created on particular date, worked well for.

select table_name,create_time  from information_schema.tables  where table_schema='dbname' AND (create_time like "2012-10-01 %");

Hope this help !!

Upvotes: 1

sn00k4h
sn00k4h

Reputation: 443

SELECT table_name,create_time 
FROM information_schema.tables 
WHERE table_schema='database_name' AND
    create_time BETWEEN '2012-10-01 00:00:00' AND '2012-10-01 23:59:59'

Upvotes: 2

XMen
XMen

Reputation: 30248

SELECT table_name,create_time FROM information_schema.tables WHERE table_schema='quadv2_dev' AND DATE_FORMAT(create_time,'%Y-%m-%d')='2012-09-17'

Upvotes: 0

RocketDonkey
RocketDonkey

Reputation: 37269

Pretty sure there is a better way, but this seems to be working for me:

SELECT table_name,create_time from information_schema.tables
where table_schema='database_name'
AND create_time BETWEEN '2012-10-01' AND DATE_ADD('2012-10-02', INTERVAL -1 SECOND)

Upvotes: 0

Related Questions