Mysql – Multiple tables entry with current timestamp

MySQL

I have several different tables that the user can perform an action on, for example:

Vote
Comment
Chat

Each of the above tables will have a timestamp column, to hold the date of creation (it is set to the current time).

I am using long polling to get real-time updates for other users that require these updates.

I assume it would heavier on the database to constantly search multiple tables for rows where time values are greater than some value. The number of these kinds of tables can increase if features are added.

So, I want to create a dummy table called actions, which will contain all the actions that a user performed. There will be another column in that table that will contain what the type of that action is, for example vote, comment, chat etc.

It will also contain a timestamp value, so that my long polling can just search in this actions table for any actions.

If there are actions present that are newer than a certain timestamp, then go to those action type tables (vote, comment, chat), and get rows that are newer than that same value, and return them to the user.

My question is: As I am inserting data to two different tables (and I want both of the timestamp values to be exactly equal), what should I do to maintain this integrity?

Best Answer

I would suggest creating your actions table with a timestamp field using a default of current_timestamp, and have the other tables use regular timestamp fields with no default value.

Then, after inserting a new row into your actions table, you can retrieve that row and insert the required rows into the additional tables using that returned value to populate the relevant fields.

In this way, the values between the tables will match as you require.

You may require an auto-incrementing field in your actions table, and then use the SQL command SELECT LAST_INSERT_ID(); within the same connection to get the auto-incrementing id from the actions table, allowing you to then perform another SELECT in order to get the timestamp value.

It will add a few queries to your overall insert, but will achieve the desired result.

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

MySQL – Select Rows Based on Latest Date with Multiple Joins

Your query can be simplified to:

SELECT
    c.id,
    c.name,
    a.rtime,
    s.status_name,
    u.user_name
FROM company c
    LEFT JOIN
      ( SELECT 
            company_id,
            MAX(rtime) AS maxdate
        FROM action
        WHERE deleted IS NULL
        GROUP BY company_id
      ) AS x ON x.company_id = c.id
    LEFT JOIN action a ON  a.deleted IS NULL
                       AND a.company_id = x.company_id 
                       AND a.rtime = x.maxdate 
    LEFT JOIN user u ON u.id = a.user_id
    LEFT JOIN status s ON s.id = a.status_id
WHERE 
    c.name LIKE 'Company%' ;

An index on (deleted, company_id, rtime) would make the derived table subquery efficient. I suppose you already have indexes on the columns used for the joins and on the Company (name).

SQL-Fiddle