[SOLVED] Python matplotlib postgresql xaxis as dates

fur · 01-10-2013, 02:11 PM

I have data being stored into a postgresql database that I want to retrieve and create graphs from as needed.

In this case there are 2 items I am getting from the db,

times in ms, and a date that I want to graph together.

This is the query I use to get the data I want, and store it to "data"

Code:

cur.execute("SELECT ms, date FROM querytimes WHERE server = (%s) AND date > CURRENT_DATE - (%s)", (server,days,))

# retrieve the whole result set
data = cur.fetchall()

# close connection
cur.close()
conn.close()

Then I use zip and do the following, I think this is what needs fixed. Tried using date2num to convert but had no luck getting the graph to look as expected.

Code:

ms, date = zip(*data)

then graph it

Code:

# graph code
plt.plot(date,ms)
plt.xticks(rotation='vertical')

However whenever I include "date" the xaxis gets messed up, and so does the resulting graph.

If I graph just ms it looks fine

The zip makes two tuples

"ms" data looks like this,

(0.012395, 0.011431999999999999, 0.010031)

"date" data looks like this

(datetime.date(2013, 1, 10), datetime.date(2013, 1, 10),datetime.date(2013, 1, 10))

Basically looking to get a graph that can be from 1+ days of data, and have the xaxis be based on the info from the db, and for it to not be duplicated over and over.

So if there is 1 day of data that includes 500 ms times, I don't want today's date on the zaxis 500 times

audriusk · 01-14-2013, 01:34 PM

You need to calculate daily sums or averages (depends on what you really want) of your data:

Code:

SELECT avg(ms), date
FROM querytimes
WHERE server = (%s) AND date > CURRENT_DATE - (%s)
GROUP BY date
ORDER BY date

I don't see how else you can display your data in the graph, when it doesn't have any times, only dates.

fur · 01-15-2013, 09:02 PM

I don't want daily averages because I would want to see the days worth of data to look for any spikes that happen in the day. I also log the time in the DB if that helps.

Code:

    ms    |    date    |   time
----------+------------+----------
 0.014675 | 2013-01-14 | 00:00:03
 0.012442 | 2013-01-14 | 00:01:02
 0.009466 | 2013-01-14 | 00:02:01
  0.00622 | 2013-01-14 | 00:03:01
 0.007399 | 2013-01-14 | 00:04:01

audriusk · 01-16-2013, 01:45 PM

Great, so you just need to concatenate dates and times in your query. A working example:

Code:

import psycopg2
from matplotlib import pyplot as plot, dates

conn = psycopg2.connect('dbname=foo user=bar')
cur = conn.cursor()
cur.execute("""
SELECT
    ms,
    (date + time) AS dt
FROM querytimes
WHERE
    server = %s AND
    date > CURRENT_DATE - %s
ORDER BY dt
""", ('some-server', 7))
data = cur.fetchall()
cur.close()
conn.close()

ms, dt = zip(*data)
dt = dates.date2num(dt)
plot.plot_date(dt, ms)
plot.xticks(rotation='vertical')
plot.show()

If I understand correctly, datetime formatting is done automatically by matplotlib, but you can set your own format if you wish.

fur · 01-16-2013, 04:39 PM

Thanks, I had to make two updates to get the graph to display as I wanted,

Code:

plt.setp(plt.xticks()[1], rotation=30)

and

Code:

plt.plot_date(dt, ms, fmt="r-")

I actually had similar results at one point trying different thing but the problem was I did not use the "rotation" setting so the z axis labels would overlap and make it unreadable. Setting it to "vertical" would display only part of the day followed by UTC.

edit:

Also had to add this to make room for the x axis labels from being partially cut off,

Code:

plt.gcf().subplots_adjust(bottom=0.20)