extract data between two time range from MySQL using python

Question

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.

@TzlilPha yes sure, I will edit my question in a momen, and will add some values of the columns in my table. — robbin
– robbin, Commented Apr 27, 2017 at 10:20

TzlilSwi · Accepted Answer · 2017-04-27 10:22:31Z

1

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())));

answered Apr 27, 2017 at 10:22

TzlilSwi

946 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

robbin Over a year ago

I am trying to fetch data in the python.

TzlilSwi Over a year ago

I know but you are sending you'r query like that: rowsdata = cursor.execute("SELECT * FROM robtable WHERE usec BETWEEN UNIX_TIMESTAMP('2016-02-25') AND UNIX_TIMESTAMP('2016-02-26 23:59:59')") so just change to FROM_UNIXTIME()

TzlilSwi Over a year ago

Don't forget to put inside FROM_UNIXTIME(epoch). epoch is the date in timestamp

robbin Over a year ago

But I if put (epoch) then how date or time range can be given?........I tried like this but results is still None . .'rowsdata = cursor.execute("SELECT * FROM robtable WHERE usec BETWEEN FROM_UNIXTIME('2016-02-25') AND FROM_UNIXTIME('2016-02-26 23:59:59')")'

TzlilSwi Over a year ago

It will not help you. the values that you save in usec are not good. they are long too much. how do you get the timestamp?

|

Collectives™ on Stack Overflow

extract data between two time range from MySQL using python

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related