LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 01-10-2013, 02:11 PM   #1
fur
Member
 
Registered: Dec 2003
Distribution: Debian, FreeBSD
Posts: 310

Rep: Reputation: 35
Python matplotlib postgresql xaxis as dates


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
 
Old 01-14-2013, 01:34 PM   #2
audriusk
Member
 
Registered: Mar 2011
Location: Klaipėda, Lithuania
Distribution: Slackware
Posts: 248

Rep: Reputation: 107Reputation: 107
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.
 
Old 01-15-2013, 09:02 PM   #3
fur
Member
 
Registered: Dec 2003
Distribution: Debian, FreeBSD
Posts: 310

Original Poster
Rep: Reputation: 35
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
 
Old 01-16-2013, 01:45 PM   #4
audriusk
Member
 
Registered: Mar 2011
Location: Klaipėda, Lithuania
Distribution: Slackware
Posts: 248

Rep: Reputation: 107Reputation: 107
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.
 
Old 01-16-2013, 04:39 PM   #5
fur
Member
 
Registered: Dec 2003
Distribution: Debian, FreeBSD
Posts: 310

Original Poster
Rep: Reputation: 35
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)

Last edited by fur; 01-16-2013 at 04:50 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unit Circle - Matplotlib Gavin Harper Programming 1 04-18-2011 02:46 PM
matplotlib--graphing against time pgb205 Programming 1 06-01-2009 01:35 PM
matplotlib not displaying graphics in opensuse 10.2 djstrozzi Suse/Novell 0 02-03-2007 11:50 AM
matplotlib rpm OldAl Suse/Novell 2 11-19-2006 01:23 AM
Python with MySQL/PostgreSQL not working iainr Programming 2 02-03-2004 02:50 PM


All times are GMT -5. The time now is 05:19 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration