CODEWITHSUNDEEP
pythonmysql
robbin
robbin

Reputation: 323

extract data between two time range from MySQL using python

I am trying to fetch data from mysql database through python and I want to print it, but it give me nothing. My code is as follow

import time
import datetime
import calendar
import mysql.connector

cnx = mysql.connector.connect(user='xxxx', password='xxxx',
                                  database='robbinDatabase')
    cursor = cnx.cursor()
    tableData = cursor.execute('select * from robtable')
    data = cursor.fetchall()
    print 'data: ', data

the above code works quite well, but I dont want to fetch all data from a table. I am trying to extract in a particular time range, so I try like this

   cnx = mysql.connector.connect(user='xxxx', password='xxxx',
                                  database='robbinDatabase')
   cursor = cnx.cursor()
   rowsdata = cursor.execute("SELECT * FROM robtable WHERE usec BETWEEN UNIX_TIMESTAMP('2016-02-25') AND UNIX_TIMESTAMP('2016-02-26 23:59:59')")
   # rowsdata = cursor.execute('SELECT * FROM robtable WHERE usec BETWEEN UNIX_TIMESTAMP(1456411800000000) AND UNIX_TIMESTAMP(1456416000000000)')
   # rowsdata = cursor.execute('SELECT * FROM robtable WHERE  `robin.col1.data` BETWEEN FROM_UNIXTIME(1456411800) AND FROM_UNIXTIME(145642200)')
   data = cursor.fetchall()
   print data

and the result is 

data: []

It return me nothing.

the data in my table for usec columns and two other columns look like this 

usec               robin.col1.data    robin.col2.data
1456411800000000    10.90             8.02
1456412400000000    22.3              33
.
.
.
.
1469950200000000    12.22             32.43

usec is a column which contains timestamp but in epoch timeformat with microseconds. I also tried with other column e.g robin.col1.data If somebody help me, I would be grateful.

Upvotes: 0

Views: 795

Answers (1)

TzlilSwi
TzlilSwi

Reputation: 94

You should try: date(FROM_UNIXTIME(epoch) instaed of UNIX_TIMESTAMP('2016-02-25')

try run in your sql: SELECT date(FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())));

Upvotes: 1

Related Questions