CODEWITHSUNDEEP
mysqlbashloops
Deano
Deano

Reputation: 12200

while loop and milliseconds

I have been deleting records from my database based on their time stamp.

My query is simple 

delete from calldetailrecord where StartTime between 1262321999000 AND 1309492799000;

1262321999000 = Thu Dec 31 2009 23:59:59 GMT-0500 (Eastern Standard Time)

1296449999000 = Sun Jan 30 2011 23:59:59 GMT-0500 (Eastern Standard Time)

For milliseconds, I have used:

http://www.ruddwire.com/handy-code/date-to-millisecond-calculators/ to make my calculations

I wonder how can I make a while loop to delete the data month by month and stop when there is no more data.

Upvotes: 0

Views: 115

Answers (3)

Neftanic
Neftanic

Reputation: 950

Looks like you want to do selective delete. First prepare periods of time you want to delete, generate single query and than run it.

DELETE FROM calldetailrecord WHERE StartTime BETWEEN 1353897342 AND 1353897592 OR StartTime BETWEEN 1353897754 AND 1353897764;

You should use as minimum connections to db as possible.

Upvotes: 0

Woot4Moo
Woot4Moo

Reputation: 24316

I think you will get better performance this way: 

Create table  new_table as select *  calldetailrecord where StartTime > 1309492799000

drop table calldetailrecord;
alter table new_table rename to calldetailrecord;

This gets around the issue of doing a row by row delete, which is going to be super slow.

Upvotes: 0

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57650

I wonder how can I make a while loop to delete month by month and it will stop when there are no more data.

If you want to delete all the data from a table use truncate command. 

truncate calldetailrecord;

Simply a single delete will do it too

delete from calldetailrecord;

Upvotes: 2

Related Questions