针对highstock的增量MySQL时间戳转换

Question

I'm using the highstock library to show the output data from an magnetometer, but I have a little problem. I store the data in two fields in the bd. The first value is the datetime where the data was saved, and the second the sensor value:

2015-06-10 01:29:43 | 15

but because the sensor send more than one value in one second, I need to convert this time, to a UNIX in a incremental way

let's suppose it's the raw data:

2015-06-10 01:29:43 | 15 2015-06-10 01:29:43 | 16 2015-06-10 01:29:43 | 40 2015-06-10 01:29:43 | 50 2015-06-10 01:29:43 | 15 2015-06-10 01:29:43 | 11

I convert it to timestamp:

1444094983 | 15 1444094983 | 16 1444094983 | 40 1444094983 | 50 1444094983 | 15 1444094983 | 11

the last step is convert this time to milliseconds. It's not a problem. The thing is, I need every second that is repeated, have to be an incremental millisecond like this

1444094983001 | 15 1444094983002 | 16 1444094983003 | 40 1444094983004 | 50 1444094983005 | 15 1444094983006 | 11

but when a new second begins, the incremental number must be restarted and start from 0 again.

I'm working with php and this would be my way to solve it

$i = 1;
foreach ($row as $result){
    $row['data_logged'] * 1000 + $i; // assuming I converted it in mysql with UNIX_TIMESTAMP
    if($prev_data != $row['data_logged']) $i = 1;
    $prev_data = $row['data_logged'];
    $i++;
}

there is a better/simplest way to do it?

Answer 1

Alternative design proposal based on the discussion:

There is an auto increment option in MySQL, this option takes care that every inserted record has a unique key. Using that option, the data can be stored without having to have alternate code in php:

CREATE TABLE magnoMeterData(
mmdata_id INT NOT NULL AUTO_INCREMENT,
mmrecordtime TIMESTAMP,
value DOUBLE
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4K;

Inserting data in this table, leads to a record as follows:

1,1444094983001,{your measured value}

2,1444094983002,{another measured value} etc

So the key keeps it unique. The key also has no other function in your data then to keep it unique and run an order by on it.