dongxinxin7809 2015-06-19 20:59
浏览 71
已采纳

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

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?

  • 写回答

1条回答 默认 最新

  • doucheng5705 2015-06-23 15:51
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题