MySql question

[dverleysen]

Hey,

I present my temperatures values with Highcharts, it's very nice and easy.
But i have a question about my mysql query

Code: Select all

Select time from value order by date desc, time desc limit 5

normally the limit is 288 = 24u overview (every 5 mins new value in database)

When i run this query i got this result
11:05:01
11:00:02
10:55:01
10:50:02
10:45:02

But i what to have the results in reserve order
expected result =
10:45:02
10:50:02
10:55:01
11:00:02
11:05:01

What do I have to change?

I have these columns in my table
table = value
columns = date, time, temp, humd

[r3d4]

IDK but! .... take alook at Understanding how my Pi stores the current Datetime perhaps something in that could be relevent

[DougieLawson]

How is the column defined? Is it char(...), varchar(...), timestamp or datetime?

[dverleysen]

date = text
time = text
temp = varchar(10)
humd = varchar(10)

see attachment

[Paeryn]

Your query asks for the sorted data to be descending which is what you got is it not? If you want ascending order (which is the default) then don't use desc.

[DougieLawson]

If you're storing date and/or time values you should use a timestamp column. That will order by correctly.

[dverleysen]

If you're storing date and/or time values you should use a timestamp column. That will order by correctly.

Thank you, I've changed the structure of the fields time and date
time = time
date = date

But i will have to make some change to my Python code, to write a correct date en time stamp

i've got an error now

time is stored correctly but the date is 0000-00-00
I think i have to change the format of the date, now i use

Code: Select all

datenow = time.strftime('%x')

Code: Select all

import Adafruit_DHT as dht
import MySQLdb
import time

conn = MySQLdb.connect(host="localhost", user="xxxx", passwd="xxxx", db="RASPDB")
curs=conn.cursor()
timenow = time.strftime('%X')
datenow = time.strftime('%x')

h,t = dht.read_retry(dht.DHT22, 26)
#print 'Temp={0:0.1f}C Humidity={1:0.1f}%'.format(t, h)
#print 'Temp={0:0.1f}C'.format(t, h)
#print 'Humidity={1:0.1f}%'.format(t, h)
#print t
#print h

var_temp = '{0:0.1f}'.format(t, h)
var_humd = '{1:0.1f}'.format(t, h)

#print datenow
#print timenow
#print var_temp
#print var_humd

curs.execute ("""INSERT INTO value VALUES(%s, %s, %s, %s)""", (datenow,timenow,var_temp,var_humd))
conn.commit()

#curs.execute ("""SELECT * FROM value;""")
#result = curs.fetchall()
#print result

curs.close()
conn.close()

[Paeryn]

Doesn't mysql want the date to be formatted as YYYY-MM-DD which probably isn't what python's %x gives,? Try

Code: Select all

datenow = time.strftime('%Y-%m-%d')

[dverleysen]

Doesn't mysql want the date to be formatted as YYYY-MM-DD which probably isn't what python's %x gives,? Try

Code: Select all

datenow = time.strftime('%Y-%m-%d')

Thanks, did this already
I deleted the whole table, so i have to wait now for some clean data to try again.

[DougieLawson]

This code works

Code: Select all

#!/usr/bin/python

import MySQLdb
import datetime

db = MySQLdb.connect(host="localhost", user="tester", passwd="testpw", db="testdb")
cur = db.cursor()

cur.execute("SELECT c1, c5, c6 FROM t1 ;")

for row in cur.fetchall():
    print "Row number:", row[0]
    c1 = int(row[0]) + 1
    print "Date:", row[1]
    print "Time:", row[2]

db.commit()

c5 = datetime.datetime.now().date()
c6 = datetime.datetime.now().time()

cur.execute ("INSERT into t1 (c1, c5, c6) values(%s, %s, %s);", (c1, c5, c6,))
db.commit()

note the extra comma after the third column to ensure that clause is always a tuple. That program will always insert a new row, re-run it and it displays all rows including the latest row and inserts a new one (not good programming practice, but OK for an example).

[dverleysen]

This code works

Code: Select all

#!/usr/bin/python

import MySQLdb
import datetime

db = MySQLdb.connect(host="localhost", user="tester", passwd="testpw", db="testdb")
cur = db.cursor()

cur.execute("SELECT c1, c5, c6 FROM t1 ;")

for row in cur.fetchall():
    print "Row number:", row[0]
    c1 = int(row[0]) + 1
    print "Date:", row[1]
    print "Time:", row[2]

db.commit()

c5 = datetime.datetime.now().date()
c6 = datetime.datetime.now().time()

cur.execute ("INSERT into t1 (c1, c5, c6) values(%s, %s, %s);", (c1, c5, c6,))
db.commit()

note the extra comma after the third column to ensure that clause is always a tuple. That program will always insert a new row, re-run it and it displays all rows including the latest row and inserts a new one (not good programming practice, but OK for an example).

Thanks!

[dverleysen]

I changed the type of the field time and data
time = type time
date = type data

But I have the same result as before

When I use this query

Code: Select all

Select time from value order by date desc, time desc limit 5

I get this result
14:35:02
14:30:01
14:25:02
14:20:01
14:15:01

But I want this result in reverse order
14:15:01
14:20:01
14:25:02
14:30:01
14:35:02

Don't know if it's possible.

Every 5 minutes I write the date/time/temperature en humidy values in a database
the database structure table looks like this
date = date
time = time
temp = varchar(10)
humd = varchar(10)

To have a nice graph overview of one day I make a query with on the end "limit 288", but i always get the results backward.
When i see the graph, the newest results are on the left instead of the right.

[Paeryn]

When I use this query

Code: Select all

Select time from value order by date desc, time desc limit 5

I get this result
14:35:02
14:30:01
14:25:02
14:20:01
14:15:01

As I said earlier, that is what you asked for. Your query specifically states to sort time (and date) in descending order - i.e. the biggest one first to the smallest last. 14:35:02 is greater than 14:30:01. To have the time in ascending order change your query to

Code: Select all

select time from value order by date desc, time limit 5

That should give you the dates from the newest first (descending), but time from the earliest in that day (ascending).

[rpdom]

I would have stored the date and time in a Timestamp field, then I could

Code: Select all

select timestamp, reading from log where timestamp > date_sub( now(), interval 1 day )

which gives me the last 288 values from my DB in ascending order.

[dverleysen]

When I use this query

Code: Select all

Select time from value order by date desc, time desc limit 5

I get this result
14:35:02
14:30:01
14:25:02
14:20:01
14:15:01

As I said earlier, that is what you asked for. Your query specifically states to sort time (and date) in descending order - i.e. the biggest one first to the smallest last. 14:35:02 is greater than 14:30:01. To have the time in ascending order change your query to

Code: Select all

select time from value order by date desc, time limit 5

That should give you the dates from the newest first (descending), but time from the earliest in that day (ascending).

Your query is working fine...
but...

With the limit 5 on the end of the query it shows me first 5 results, but i need the 5 last records.
How can I do that?

When I use your query "select time from value order by date desc, time limit 5"
I get the first results of the table see green frame (see screenshot), but as result I want the values in the red frame.
All the new data will be added at the end of the table

2016-01-31 15_40_44-Films en tv.jpg (63.67 KiB) Viewed 2819 times

I also added a general timestamp with date and time, see timestamp

[Paeryn]

When I use your query "select time from value order by date desc, time limit 5"
I get the first results of the table see green frame (see screenshot), but as result I want the values in the red frame.
All the new data will be added at the end of the table

2016-01-31 15_40_44-Films en tv.jpg

I also added a general timestamp with date and time, see timestamp

By doing a sub query? I think something like :-

Code: Select all

SELECT time FROM (SELECT time FROM value ORDER BY date DESC, time DESC LIMIT 5) AS sub ORDER BY time ASC

The inner select is as you originally had it, picks the last 5 entries. The outer select then re-sorts those in ascending time.

Edit: Doing something like what rpdom said is probably better, less hacky.

[DougieLawson]

I would have stored the date and time in a Timestamp field, then I could

Code: Select all

select timestamp, reading from log where timestamp > date_sub( now(), interval 1 day )

which gives me the last 288 values from my DB in ascending order.

I'd do it with a between clause and calculate the timestamp range in python.

Code: Select all

startTime = datetime.datetime.now() - datetime.timedelta(minutes=1440)
endTime = datetime.datetime.now()

with SQL like

Code: Select all

cur.execute ("SELECT col1,col2,col3 FROM table_with_timestamp where date_time_column BETWEEN %s AND %s", (startTime, endTime,))

if you have an index on the date_time_column it will be a quick running query. Because the timestamp is monotonically ascending that index can be unique.

[dverleysen]

When I put the query of "rpdom" in phpmyadmin, I get an error
see attachment

[rpdom]

Well, obviously, because I was giving you an example that works on my DB which has different names to yours.

It won't work on yours because you you have separate time and date fields rather than a timestamp like I use.

[dverleysen]

No, I added this afternoon an extra field 'timestamp' to my tabel, and it has already some data

timestamp = 2016-01-31 15:18:40
date = 2016-01-31
time = 15:18:40
temp = 23.5
humd = 85.0

[rpdom]

But your table is called "value" while mine is called "log".

[DougieLawson]

It's a Very Bad Idea™ to give columns or tables created with any SQL engine names that match that system's data types, that will bite you on the a*** one day.

Also your temp and humidity columns are numeric values, so why have you defined them as varchar(10)? Define them as float with a sensible mantissa & decimal. That way you can get SQL to do the rounding for you. For example with float(10,2) insert values 12.552 and 9.1 and they'll come out as 12.55 (rounded) and 9.10 (complete with trailing zero).

[dverleysen]

It's a Very Bad Idea™ to give columns or tables created with any SQL engine names that match that system's data types, that will bite you on the a*** one day.

Also your temp and humidity columns are numeric values, so why have you defined them as varchar(10)? Define them as float with a sensible mantissa & decimal. That way you can get SQL to do the rounding for you. For example with float(10,2) insert values 12.552 and 9.1 and they'll come out as 12.55 (rounded) and 9.10 (complete with trailing zero).

@DougieLawson
I will change the field names, indeed this will be a good start

@rpdom
I tried this also

[DougieLawson]

Combining Dom's SQL with mine

Code: Select all

SELECT timestamp, temp, humd FROM value WHERE timestamp 
    BETWEEN date(date_sub(now() , INTERVAL 1 DAY))
    AND date(now())

that's probably easier than doing the date maths in python.

date_sub() works like date_add()
https://dev.mysql.com/doc/refman/5.5/en ... n_date-add

[dverleysen]

Combining Dom's SQL with mine

Code: Select all

SELECT timestamp, temp, humd FROM value WHERE timestamp 
    BETWEEN date(date_sub(now() , INTERVAL 1 DAY))
    AND date(now())

that's probably easier than doing the date maths in python.

date_sub() works like date_add()
https://dev.mysql.com/doc/refman/5.5/en ... n_date-add

Thanks DougieLawson, did some little changes and everything works fine now!

Code: Select all

SELECT timestamp, var_temp, var_humd FROM value WHERE timestamp 
    BETWEEN timestamp(date_sub(now() , INTERVAL 24 HOUR))
    AND timestamp(now())

Thanks guys for all the advice and tips!!
I will share my project when it's finished - Greenhouse automation