CODEWITHSUNDEEP
mysqldatabasetimestamp
molleman
molleman

Reputation: 2946

How record insert time in mysql database

I want to remove a table row from my table new_data once the row is 45 mins old and then input it in another table called old_data.

The only way i can think for this to work, it to query the database lets say every min and remove any row thats (current_time - time inserted) > 45 mins.

Is there any other way of doing this? if not how could i set up a table to record inserted_time?

edit added

How could i write this statement to retrieve the correct data into the old_data table

SELECT * FROM new_spots WHERE (NOW()-created_at)>45mins

and then insert the above into the old_data table

Upvotes: 0

Views: 4446

Answers (4)

James.Xu
James.Xu

Reputation: 8295

you are asking for some kind of auto expiration feature, it is not built into mysql. Memcached provides this feature. So it might be cleaner to achieve your goal as:

when you insert data into your system, you do:

  1. insert your data into memcached with 45 minutes expiration time -- after 45 minutes, the data automatically disappear from memcached.
  2. insert the data into the old_data table with a created_at column -- in case you need to rebuild your memcached when your memcached have to restart or other issue.

So everytime you just need to get the new data from the memcached -- as a side effect, it is faster than get the data from mysql :).

Upvotes: 1

Chris McCauley
Chris McCauley

Reputation: 26393

I don't think there is an automatic way to do this. Here are some alternative ideas.

Use CRON

I have a similar scenario where we need to aggregate data from one table into another. A simple command line tool running via CRON suffices. We receive a few messages a second into our Web server and each results in a database insert. So volumes aren't huge but they are reasonably similar to your scenario

We use the NOW() function to record the insert time and after the records are 1hr old, we process them. It isn't exactly an hour but it is good enough. You can see the created_on field below.

CREATE TABLE glossaries (
    id int(11) NOT NULL auto_increment,

    # Our stuff ...

    created_on datetime default NULL,
    KEY owner_id (owner_id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Use CRON and a Trigger

Alternatively you could use a database trigger to kick off the processing. You would still need something scheduled to cause the trigger to fire but you would get max performance/

Chris

Upvotes: 0

Scott C Wilson
Scott C Wilson

Reputation: 20026

@keymone showed you how to capture the insert time. Then, periodically (every minute seems excessive - every 5 mins? 15 mins?) go through and build a list that meets the criteria, and for each entry, insert into your second table and delete from your first table.

Upvotes: 0

keymone
keymone

Reputation: 8104

you can specify value of time column upon insertion:

INSERT INTO x (created_at) VALUES (NOW());

additionally you can setup VIEW to show you only recent entries.

Upvotes: 1

Related Questions