Mysql – how to use the same timestamp along several tables

insertMySQLtimestamp

Lets say a have 2 tables and both have a datetime column.

On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP;

The other table, B, the datetime column, lets say, ts2 is non null;

The datetime values on both tables must be the same (A.ts1==B.ts2).

As ts1 is auto-updated on inserts and updates, I don't need to get the current_stamp on advance.

Now, how can I do inserts on table B in such way that B.ts2 has the same timestamp value (A.ts1) from a specific record on table A?

Is there a relation ship between the tables to achieve this or some special insert/select statement ?

sorry if the answer is obvious…I am a new to databases…
PS: Using mySql 5.7 innoDB.

PS2: I found this works, but seems to be awkward.

insert into B (y, ts2) values ('a value for y', (select ts1 from A where x=1));

any other better ideas ?

@Lennart:
Thanks for your comments. Yes I need help in the design. I am open to suggestions.

Developing this a bit more:

On table A, x is a key.
Also, ts1 can not be unique since more than 1 record can have the same ts, because of possible concurrent access to create records on A.

Currently, there is no keys on table B.
The column ts2 can not be unique too, since table B store several y values with the same timestamp ts2;
In fact the excepted usage of table B is something like: 

select * from B where ts2=(select ts1 from A where x=somevalue);

I liked how this sounds "add a trigger that does the update". Can you develop this a bit more, please ?

PS3: @Lennart, here we go:

CREATE TABLE `device` (
  `deviceID` varchar(12) NOT NULL,
  `snmpSet` varchar(30) NOT NULL,
  `firstSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastSeen` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

ALTER TABLE `device`
  ADD PRIMARY KEY (`deviceID`);
COMMIT;

CREATE TABLE `snmpData` (
  `deviceID` varchar(12)  NOT NULL,
  `ts` datetime NOT NULL,
  `oid` varchar(45)  NOT NULL,
  `value` varchar(30)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16 COMMENT='hold all snmp data from each device';

if it is a new device, insert it into table device and add its data to snmpData

INSERT INTO device (deviceID, snmpSet) VALUES ($id, $snmpSet);
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

else, it is a existing device, I need to update the lastSeen on device and then, the same insert on snmpData

UPDATE device SET lastSeen=current_timestamp() WHERE deviceID=$id;
INSERT INTO snmpData (deviceID, ts, oid, value) VALUES ($id, (SELECT lastSeen FROM device WHERE deviceID=$id),$oid,$value);

The general idea:
This DB will support a monitoring tool that store snmp data (oid/value pairs) for several devices (deviceID)
The table device holds basic info about every device and the snmpData holds the data itself for every device.
If I want to check the last values received from a device I plan to do something like this:

select oid, value from snmpData where deviceID=$id and ts=(select lastSeen from device where device=$id);

as result, I will receive a list of 50 or more records, each one for a specific metric and its value.
There is some simplification here. In the real case, both tables have more columns, but you got the idea.

caveats:
The code for writing to tables device and snmpData are not on the same logical block.
It is a PHP program and the operation on table device has its own try/catch block.
If everything it is ok with the INSERT/UPDATE on device table, there is another try/block with the INSERT operation on table snmpData.
Each block has its own prepare/execute staements, so it is not possible to use the variable trick
"SET @ts := NOW();" that @Rick James suggested.
BTW, I loved, its new to me, and I will use it on another context. thanks)

Best Answer

Edit: an alternative approach

-- remove first- last- seen from device
CREATE TABLE device 
( deviceID varchar(12) NOT NULL primary key
, snmpSet varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

-- add p.k. and f.k. 
CREATE TABLE snmpData 
( deviceID varchar(12)  NOT NULL
, ts datetime NOT NULL
, oid varchar(45)  NOT NULL
, value varchar(30)  NOT NULL
,    constraint pk_snmpData primary key (deviceID, ts) -- assumed 
,    constraint fk_snmpData foreign key (deviceID) 
                            references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

Now, what is first- and last- seen? To me, this seems like a kind of aggregation or summary of a device. This kind of construction is sometimes named Incremental Evaluation System

CREATE TABLE device_summary 
( deviceID varchar(12) NOT NULL
, firstSeen datetime NOT NULL
, lastSeen datetime NOT NULL
,   constraint pk_device_summary primary key (deviceID) 
,   constraint fk_device_summary foreign key (deviceID) 
                                 references device (deviceID)
) ENGINE=InnoDB DEFAULT CHARSET=utf16;

The logic for updating device_summary is easily expressed in a trigger:

DELIMITER $$
CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
    -- if device exists, insert will be ignored
    INSERT IGNORE INTO device_summary
        (deviceID, firstSeen, lastSeen)
    VALUES (NEW.deviceID, NEW.ts, NEW.ts);

    -- update lastSeen
    UPDATE device_summary 
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;
END$$
DELIMITER ;

Since a device most likely exists in device_summary, it is more efficient to update first and check for number of updated rows (if 0, insert)

CREATE TRIGGER after_snmpData_insert 
AFTER INSERT ON snmpData
FOR EACH ROW 
BEGIN
    -- update lastSeen
    UPDATE device_summary 
        SET lastSeen = NEW.ts
    WHERE deviceID = NEW.deviceId;

    IF ( ROW_COUNT() = 0 ) THEN
        -- No rows updated, i.e. the device does not exist
        INSERT INTO device_summary
            (deviceID, firstSeen, lastSeen)
        VALUES (NEW.deviceID, NEW.ts, NEW.ts);
    END IF;    
END

Edit: added a fiddle, extended extended fiddle

To get the latest data for each device:

select x.oid, x.value 
from snmpData x 
join device_summary y 
    on x.deviceId = y.deviceId 
   and x.ts = y.lastSeen;
Related Question