Last active
December 11, 2015 12:49
-
-
Save shantanuo/4603313 to your computer and use it in GitHub Desktop.
The procedure will generate 4,50,000 seconds since the date "2013-01-19". For each record it will save the unix timestamp and corresponding date.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
use test; | |
drop table if exists filler; | |
drop procedure prc_filler; | |
CREATE TABLE filler (id INT NOT NULL, msg_timestamp int, mydate date, key(mydate)) ENGINE=Myisam; | |
DELIMITER $$ | |
CREATE PROCEDURE prc_filler(cnt INT, mydate datetime) | |
BEGIN | |
DECLARE _cnt INT; | |
DECLARE _nextday INT; | |
SET _cnt = 1; | |
WHILE _cnt <= cnt DO | |
SET _nextday = _cnt + 1; | |
INSERT | |
INTO filler | |
SELECT _cnt, unix_timestamp(date_add(mydate, interval _cnt second)), date(date_add(mydate, interval _cnt second)); | |
SET _cnt = _cnt + 1; | |
END WHILE; | |
END | |
$$ | |
DELIMITER ; | |
call prc_filler(450000, '2013-01-19'); | |
The procedure will generate 4,50,000 seconds since the date "2013-01-19". | |
For each record it will save the unix timestamp and corresponding date. The filler table will look like this... | |
(02:14) mysql>select * from test.filler limit 10; | |
+----+---------------+------------+ | |
| id | msg_timestamp | mydate | | |
+----+---------------+------------+ | |
| 1 | 1358533801 | 2013-01-19 | | |
| 2 | 1358533802 | 2013-01-19 | | |
| 3 | 1358533803 | 2013-01-19 | | |
| 4 | 1358533804 | 2013-01-19 | | |
| 5 | 1358533805 | 2013-01-19 | | |
This will NOT make this query faster, but it can be used as an alternative where more complex date functions are involved. | |
Or if you need to return data for specific days, generate the helper table only for those dates. | |
Old Query: | |
SELECT session_esme,count(*) as `sms_sent`,date(FROM_UNIXTIME(msg_timestamp)) as `Date` | |
FROM smppbox.MT GROUP BY session_esme,date(FROM_UNIXTIME(msg_timestamp)); | |
New Query: | |
SELECT session_esme, count(*) as `sms_sent`, b.mydate as mydate | |
FROM smppbox.MT as a INNER JOIN test.filler as b ON a.msg_timestamp = b.msg_timestamp | |
GROUP BY session_esme, b.mydate |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment