将时间戳+数据从mysql导入到python并绘制时间序列

I have a mysql table that I want to access and extract data from. The table is structured as seen here:

我有一个我想访问的mysql表并从中提取数据。该表的结构如下所示:

| Id | datestamp |时间戳| bloodSugar |碳水化合物|胰岛素|

| 1 | 2013-08-05 | 18:28:17 | 6.7 | 76 | 6.6 |

NB: timestamp entries are generated using CURTIME() NB2: Please excuse the horrible table--I would have posted an image, but don't have enough rep points to do so yet :(

注意:使用CURTIME生成时间戳条目()NB2:请原谅可怕的表 - 我会发布一张图片,但还没有足够的代表点这样做:(

I am trying to extract the timestamp and the bloodSugar values in order to plot a time-series with: time (HH:MM) on the x-axis, and bloodSugar-value on the y-axis. But I'm running into some issues...

我试图提取时间戳和bloodSugar值,以便绘制时间序列:x轴上的时间(HH:MM)和y轴上的bloodSugar值。但是我遇到了一些问题......

PROBLEM: When fetching the data into a list in python the timestamps appear as datetime.timedelta(0, 58753) entries. I want these in HH:MM so that I can plot the data against them.

问题:当在python中将数据提取到列表中时,时间戳显示为datetime.timedelta(0,58753)条目。我希望这些在HH:MM中,以便我可以根据它们绘制数据。

QUESTION: is there a way to simply re-format the timedelta into a HH:MM format? If this is not possible then please advise on how I could work around this (e.g. use another fetch technique with mysqldb, or replace CURTIME() with something else, etc.).

问题:有没有办法简单地将timedelta格式化为HH:MM格式?如果这是不可能的,那么请告诉我如何解决这个问题(例如,使用mysqldb的另一个获取技术,或用其他东西替换CURTIME()等)。

Disclaimer: I'm a noob, looking to learn, so please provide as through an explanation as you can. Thanks!!

免责声明:我是一个菜鸟,希望学习,所以请尽可能通过解释提供。谢谢!!

code:

import MySQLdb as mdb
import numpy as np
from matplotlib import pyplot as plt
from matplotlib import dates
import datetime

con = mdb.connect('localhost', 'testuser', 'test623', 'test');
with con:
    cur = con.cursor()
    #importing data into a list for analysis
    cur.execute("SELECT * FROM Diabetes")

    rows = cur.fetchall()

    data = []
    for row in rows:
        data.append(row)

    A = data

    d = [a[1] for a in A]
    t = [a[2] for a in A]
    b = [a[3] for a in A]
    c = [a[4] for a in A]
    novo = [a[5] for a in A] 

    #here is my attempt at trying to reformat the time-entries... But doesn't work..
    formatted_time = []    
    for delta_time in t:
        hours, remainder = divmod(delta_time[1], 3600)  
        minutes, seconds = divmod(remainder, 60)   

        duration_formatted = '%s:%s:%s' % (hours, minutes, seconds)
        formatted_time.append(duration_formatted)
print formatted_time


fig, ax = plt.subplots()
ax.plot_date(t, b)
fig.autofmt_xdate()
plt.show()

1 个解决方案

#1

You have bunches of timestamps stored as two disjoint columns called datestamp and timestamp. You didn't tell us the data types of those columns. (Keep in mind that TIMESTAMP is a MySQL reserved word, and thus a pretty poor choice for a column name.)

您有一堆时间戳存储为两个不相交的列,称为datestamp和timestamp。您没有告诉我们这些列的数据类型。 (请记住,TIMESTAMP是一个MySQL保留字,因此对于列名称来说是一个相当糟糕的选择。)

Best practice for this sort of data is to combine date and time into a single column of type DATETIME. You might call it obstime (for observation time) or some such thing. You can populate this field with the NOW() function.

此类数据的最佳实践是将日期和时间组合到DATETIME类型的单个列中。你可以把它称为obstime(观察时间)或某些这样的事情。您可以使用NOW()函数填充此字段。

If this information will relate to multiple health-care facilities, you may want to deal with timezones. But that's another set of questions.

如果此信息与多个医疗机构有关,您可能需要处理时区。但这是另一组问题。

You can rewrite your MySQL query to combine your datestamp and timestamp columns into a single DATETIME column. Try this to retrieve DATETIME values from your table

您可以重写MySQL查询以将datestamp和timestamp列合并到单个DATETIME列中。尝试此操作从表中检索DATETIME值

  SELECT id, bloodsugar, carbohydrates, insuline,
         CAST(CONCAT(`datestamp`, ' ', `timestamp`) AS DATETIME) obstime
    FROM Diabetes
   ORDER BY id

I am not sure from your question what hh:mm information you want. Do you want elapsed time in hh:mm since the first measurement? If you're charting this stuff, maybe you want the observations in hh:mm:ss since the first measurement. Let's work this one out.

我不确定你的问题是什么hh:mm你想要的信息。从第一次测量开始,您想要以hh:mm为单位的经过时间吗?如果您正在绘制这些东西,也许您希望在第一次测量后以hh:mm:ss进行观察。让我们解决这个问题吧。

This query gives you the first measurement -- the first in time.

此查询为您提供第一次测量 - 第一次测量。

    SELECT MIN(CAST(CONCAT(`datestamp`, ' ', `timestamp`) AS DATETIME)) firstobstime
      FROM Diabetes

You can join that query to your other one like so.

您可以将该查询加入到您的其他查询中。

   ...
  FROM Diabetes AS d
  JOIN (
    SELECT MIN(CAST(CONCAT(`datestamp`, ' ', `timestamp`) AS DATETIME)) firstobstime
      FROM Diabetes
  ) AS f

This join doesn't need any ON clause; the second part of the join only yields one row.

此连接不需要任何ON子句;连接的第二部分只产生一行。

So, then you can get a list of observations by minute since the beginning like so.

那么,你可以从一开始就按分钟获得观察列表。

SELECT TIMEDIFF(CAST(CONCAT(d.`datestamp`, ' ', d.`timestamp`) AS DATETIME),
                d.firstobstime) AS obstimeoffset
         d.bloodsugar, d.carbohydrates, d.insuline
  FROM Diabetes AS d
  JOIN (
    SELECT MIN(CAST(CONCAT(`datestamp`, ' ', `timestamp`) AS DATETIME)) firstobstime
      FROM Diabetes
       ) AS f        
 ORDER BY obstimeoffset

I think that's what you want.

我想这就是你想要的。

Pro tip: Avoid SELECT * in queries in programs. It makes your programs break when you add columns to your table.

专业提示:避免在程序中的查询中使用SELECT *。当您向表中添加列时,它会使您的程序中断。

Another pro tip: combine dates and times in DATETIME columns.

另一个专业提示:在DATETIME列中组合日期和时间。

A third pro tip: I don't see any patient_id or experiment_id or any such thing in your table. As you've designed it, it's suitable for one patient and one series of observations. You may wish to make it more general. That's a much better approach than adding another table for each patient or experiment.

第三个专业提示:我没有在您的表格中看到任何patient_id或experiment_id或任何此类内容。正如您设计的那样,它适用于一名患者和一系列观察。您可能希望使其更加通用。这比为每个患者或实验添加另一个表更好。

#1