title: problems defining time when pulling data from sqlite3
body: <p>i have a problem getting the right setup for pulling data from a database.</p>
  
  <p>i need to pull the last 10 minutes of data, and put it into a chart. i have 2 problems with that, i cant seem to get the arguments to work to get a specific time period (last 10 minutes), but i did manage to just grab the last 600 entries.</p>
  
  <p>that works, but my chart X-axis is the wrong way, and i cant seem to find a way to invert the data in the table, after i pull it out of the database. (newest reading left and then getting older as you go further right on the chart)</p>
  
  <p>the code i am using at the moment is this (yes  it's ugly and borrowed from all over) but i hope you can look past it since this is my first time coding anything like this.</p>
  
  <pre><code>#!/usr/bin/env python
  
  import sqlite3
  import sys
  import cgi
  import cgitb
  import time
  
  global variables
  speriod=(15)-1 
  dbname='/var/www/sensors.db'
  
  def printHTTPheader():
      print "Content-type: text/html\n\n"
  
  def printHTMLHead(title, table):
      print "&lt;head&gt;"
      print "    &lt;title&gt;"
      print title
      print "    &lt;/title&gt;"
  
      print_graph_script(table)
  
      print "&lt;/head&gt;"
  
  #current_time=time.time()
  
  def get_data(interval):
  
  conn=sqlite3.connect(dbname)
  curs=conn.cursor()
  
  if interval == None:
      curs.execute("SELECT * FROM readings")
  else:
  #         curs.execute("SELECT * FROM readings")
      curs.execute("SELECT * FROM readings ORDER BY timestamp DESC LIMIT "+str(600))
  #        curs.execute("SELECT * FROM readings WHERE time&gt;=datetime('now','%s minutes')" % interval)
  
  rows=curs.fetchall()
  
  conn.close()
  
  return rows
  
  def create_table(rows):
  chart_table=""
  
  for row in rows[:-4]:
      rowstr="['{0}', {1}],\n".format(str(row[2]),str(row[5]))
      chart_table+=rowstr
  
  row=rows[-1]
  rowstr="['{0}', {1}]\n".format(str(row[2]),str(row[5]))
  chart_table+=rowstr
  
  return chart_table
  
  def print_graph_script(table):
  
  chart_code="""
  &lt;script type="text/javascript" src="https://www.google.com/jsapi"&gt;&lt;/script&gt;
  &lt;script type="text/javascript"&gt;
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
    function drawChart() {
      var data = google.visualization.arrayToDataTable([
        ['windspeed', 'temperature'],
  %s
      ]);
      var options = {
        title: 'windspeed'
      };
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }
  &lt;/script&gt;"""
  
  print chart_code % (table)
  
  def show_graph():
  print "&lt;h2&gt;Windspeed Chart&lt;/h2&gt;"
  print '&lt;div id="chart_div" style="width: 900px; height: 500px;"&gt;&lt;/div&gt;'
  
  def show_stats(option):
  
  conn=sqlite3.connect(dbname)
  curs=conn.cursor()
  
  if option is None:
      option = str(24)
  
  curs.execute("SELECT timestamp,max(windspeed) FROM readings WHERE time&gt;date('now','-%s hour') AND time&lt;=date('now')" % option)
  #    curs.execute("SELECT timestamp,max(windspeed) FROM readings WHERE timestamp&gt;datetime('2013-09-19 21:30:02','-%s hour') AND timestamp&lt;=datetime('2013-09-19 21:31:02')" % option)
  rowmax=curs.fetchone()
  rowstrmax="{0}&amp;nbsp&amp;nbsp&amp;nbsp{1}C".format(str(rowmax[0]),str(rowmax[1]))
  
  curs.execute("SELECT timestamp,min(windspeed) FROM readings WHERE timestamp&gt;time('time','-%s hour') AND timestamp&lt;=time('current_time')" % option)
  #    curs.execute("SELECT timestamp,min(temp) FROM temps WHERE timestamp&gt;datetime('2013-09-19 21:30:02','-%s hour') AND timestamp&lt;=datetime('2013-09-19 21:31:02')" % option)
  rowmin=curs.fetchone()
  rowstrmin="{0}&amp;nbsp&amp;nbsp&amp;nbsp{1}C".format(str(rowmin[0]),str(rowmin[1]))
  
  curs.execute("SELECT avg(windspeed) FROM readings WHERE timestamp&gt;time('now','-%s hour') AND timestamp&lt;=time('current_time')" % option)
  #    curs.execute("SELECT avg(temp) FROM temps WHERE timestamp&gt;datetime('2013-09-19 21:30:02','-%s hour') AND timestamp&lt;=datetime('2013-09-19 21:31:02')" % option)
  rowavg=curs.fetchone()
  
  print "&lt;hr&gt;"
  
  print "&lt;h2&gt;Minumum temperature&amp;nbsp&lt;/h2&gt;"
  print rowstrmin
  print "&lt;h2&gt;Maximum temperature&lt;/h2&gt;"
  print rowstrmax
  print "&lt;h2&gt;Average temperature&lt;/h2&gt;"
  print "%.3f" % rowavg+ "C"
  
  print "&lt;hr&gt;"
  
  print "&lt;h2&gt;In the last hour:&lt;/h2&gt;"
  print "&lt;table&gt;"
  print "&lt;tr&gt;&lt;td&gt;&lt;strong&gt;Date/Time&lt;/strong&gt;&lt;/td&gt;&lt;td&gt; 
  &lt;strong&gt;Temperature&lt;/strong&gt;&lt;/td&gt;&lt;/tr&gt;"
  
  rows=curs.execute("SELECT * FROM readings WHERE timestamp&gt;datetime('now','-1 hour') AND timestamp&lt;=datetime('now')")
  #    rows=curs.execute("SELECT * FROM temps WHERE timestamp&gt;datetime('2013-09-19 21:30:02','-1 hour') AND timestamp&lt;=datetime('2013-09-19 21:31:02')")
  for row in rows:
      rowstr="&lt;tr&gt;&lt;td&gt;{0}&amp;emsp;&amp;emsp;&lt;/td&gt;&lt;td&gt;{1}C&lt;/td&gt;&lt;/tr&gt;".format(str(row[0]),str(row[1]))
      print rowstr
  print "&lt;/table&gt;"
  
  print "&lt;hr&gt;"
  
  conn.close()
  
  def print_time_selector(option):
  
  print """&lt;form action="/cgi-bin/webgui.py" method="POST"&gt;
      Show the temperature logs for  
      &lt;select name="timeinterval"&gt;"""
  
  if option is not None:
  
      if option == "6":
          print "&lt;option value=\"6\" selected=\"selected\"&gt;the last 6 hours&lt;/option&gt;"
      else:
          print "&lt;option value=\"6\"&gt;the last 6 hours&lt;/option&gt;"
  
      if option == "12":
          print "&lt;option value=\"12\" selected=\"selected\"&gt;the last 12 hours&lt;/option&gt;"
      else:
          print "&lt;option value=\"12\"&gt;the last 12 hours&lt;/option&gt;"
  
      if option == "24":
          print "&lt;option value=\"24\" selected=\"selected\"&gt;the last 24 hours&lt;/option&gt;"
      else:
          print "&lt;option value=\"24\"&gt;the last 24 hours&lt;/option&gt;"
  
  else:
      print """&lt;option value="6"&gt;the last 6 hours&lt;/option&gt;
          &lt;option value="12"&gt;the last 12 hours&lt;/option&gt;
          &lt;option value="24" selected="selected"&gt;the last 24 hours&lt;/option&gt;"""
  
  print """        &lt;/select&gt;
      &lt;input type="submit" value="Display"&gt;
  &lt;/form&gt;"""
  
  
  def validate_input(option_str):
  if option_str.isalnum():
      if int(option_str) &gt; 0 and int(option_str) &lt;= 24:
          return option_str
      else:
          return None
  else: 
      return None
  
  def get_option():
  form=cgi.FieldStorage()
  if "timeinterval" in form:
      option = form["timeinterval"].value
      return validate_input (option)
  else:
      return None
  
  def main():
  
  cgitb.enable()
  
  option=get_option()
  
  if option is None:
      option = str(24)
  
  records=get_data(option)
  
  printHTTPheader()
  
  if len(records) != 0:
      table=create_table(records)
  else:
      print "No data found"
      return
  
  print "&lt;html&gt;"
  printHTMLHead("Raspberry Pi Wind Logger", table)
  
  print "&lt;body&gt;"
  print "&lt;h1&gt;Raspberry Pi Wind Logger&lt;/h1&gt;"
      print "&lt;hr&gt;"
      print_time_selector(option)
      show_graph()
      show_stats(option)
      print "&lt;/body&gt;"
      print "&lt;/html&gt;"
  
      sys.stdout.flush()
  
  if __name__=="__main__":
      main()
  </code></pre>
  
  <p>the database contains 7 rows (unix time, date, time, windspeed, direction, temperature, checksum) it's data comming from an ultrasonic wind sensor, i would like to get the "options" in this working aswell like the max wind speed or the average wind speed, but i think that should become clear once i figure out how to actualy get the time sorted.</p>
  
  <p>edit:</p>
  
  <p>here is a reading pulled from the database</p>
  
  <pre><code>1540300313.94403|23/10/2018|21:11:53|0.1|273|24.1|5E*4B
  </code></pre>
  
  <p>and part of the code to store data in the database</p>
  
  <pre><code> cursor.execute('''insert into readings values (?, ?, ?, ?, ?, ?, ?)''',
   (timestamp, current_date, current_time, windspeed, direction, temperature, checksum));
   dbconnect.commit()
  </code></pre>