CODEWITHSUNDEEP
mysql
wayofthefuture
wayofthefuture

Reputation: 9395

Mathematical Operation On MySQL Table Name

I have a database with thousands of tables, named by UTC time in seconds since 1.1.1970 (i.e. time 3651233210). I am trying to show the tables that are greater than time X and less than time Y. I am aware of how to use a select statement to perform mathematical operations on query results from a single table, but I cannot figure out how to do it on the table name.

Here is an idea of what I'm trying to do:

SHOW TABLES FROM DATABASE history WHERE TABLENAME.SUBSTRING(4, LEN(TABLENAME) - 4) > TimeX AND TABLENAME.SUBSTRING(4, LEN(TABLENAME) - 4) < TimeY;

I do realize this in no way resembles a valid MySql statement, but I think it helps explain what I'm trying to accomplish. Your time is appreciated, Thanks.

UPDATE: THANK YOU EUGEN, THE FINAL STATEMENT I USED IS AS FOLLOWS:

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db_time' AND CAST(SUBSTRING(TABLE_NAME,2) AS UNSIGNED) > 1383072311 AND CAST(SUBSTRING(TABLE_NAME,2) AS UNSIGNED) < 1383130756 ORDER BY CAST(SUBSTRING(TABLE_NAME,2) AS UNSIGNED) ASC LIMIT 1;

Upvotes: 0

Views: 102

Answers (2)

Abdul Manaf
Abdul Manaf

Reputation: 4888

Try below one, I have explained it with example by using a test database named as world.

use world

Created some sample tables

CREATE TABLE `1.1.1970` (id INT);  
CREATE TABLE `1.1.1971` (id INT);  
CREATE TABLE `1.1.2003` (id INT);  
CREATE TABLE `1.1.2013` (id INT);

Below query will list tables in given range , but you need to pass values of as standard MySQL date format '1970-01-01' instead of 1.1.1970

SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'world' 
AND STR_TO_DATE(REPLACE(TABLE_NAME,'.','-'),'%d-%m-%Y') > '1970-01-01' 
AND STR_TO_DATE(REPLACE(TABLE_NAME,'.','-'),'%d-%m-%Y') < '2003-01-01';

Gives output 1.1.1971.

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65264

SELECT TABLE_NAME FROM information_schema 
WHERE TABLE_SCHEMA='your_database_name'
AND TABLE_NAME.SUBSTRING(4, LEN(TABLE_NAME) - 4) > 'TimeX' 
AND TABLE_NAME.SUBSTRING(4, LEN(TABLE_NAME) - 4) < 'TimeY';

Upvotes: 2

Related Questions