How to converting string dates(YYYY-MM-DD) from SQL database to matplotlib dates in python?

Question

I wanna plot a graph for data present in the database of temperature and dates. the data dates is of string type. I tried to convert the string type into matplot date using date2num() function. But, while at the time of plotting graphs I observed that the dates are being shown in the form of time. Why this is being happened and what should I do to make dates of format(YYYY-MM-DD) on X-axis?

import MySQLdb
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from dateutil import parser
from matplotlib import style
import datetime
def graph_data():
con =MySQLdb.connect("***.***.*.**","monitor", "password","temp")
cursor=con.cursor()
style.use('fivethirtyeight')
cursor.execute('select tdate,ttime,temperature from temptb')
rows=cursor.fetchall()
timenow=[]
temperature=[]
for eachRow in rows:
    temperature.append(eachRow[2])
    timenow.append(eachRow[0])
#dates=[mdates.date2num(t) for t in timenow]
fig=plt.figure()
ax1=fig.add_subplot(111)
ax1.set_title("Temperature Analysis")
ax1.set_ylabel("Temperature")
ax1.set_xlabel("Time")
#ax1.plot_date(dates, temperature, '-', label="tempurature", color='r')
ax1.plot_date(timenow, temperature, '-', label="tempurature", color='r')
fig.autofmt_xdate(rotation=60)
fig.tight_layout()
ax1.grid(True)
ax1.legend(loc='best', framealpha=0.5)
plt.savefig("figure.png")

graph_data()

The data fetched from database: 2017-12-14 16 2017-12-14 17 2017-12-13 18 2017-12-14 18 2017-12-14 21 2017-12-13 22 2017-12-14 23 2017-12-13 25 2017-12-14 26

output:

edit 1: There is no change in output though using timenow in the place of date in ax1.plot_date(dates, temperature, '-', label="tempurature", color='r')

Do you want to plot the dates or times? It seems if you only plot the dates you would have a lot of points on the same day. Would that be desired? — ImportanceOfBeingErnest, Dec 16 '17 at 07:50
Then I don't understand what could go wrong. Just using `ax1.plot(timenow, temperature)` should give a plot with the dates correctly labeled. — ImportanceOfBeingErnest, Dec 16 '17 at 13:38
In case it is not, we need a [mcve]. I.e. hardcode some values you would obtain from the database into the code, such that other people can run it. — ImportanceOfBeingErnest, Dec 16 '17 at 13:39

wdlax11 · Answer 1 · 2017-12-16T06:22:58.890

0

Why are you doing dates=[mdates.date2num(t) for t in timenow]?

You have tdate coming from your DB?

I can't replicate your code fully; however, I am assuming the data from your database is coming back like so: 2017-12-13, 2017-12-14, 2017-12-15

If these formats are correct

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from dateutil import parser
from matplotlib import style
import datetime

timenow=["2017-12-15", "2017-12-14", "2017-12-13"]
temperature=[10,20,30]


fig=plt.figure()
ax1=fig.add_subplot(111)
ax1.set_title("Temperature Analysis")
ax1.set_ylabel("Temperature")
ax1.set_xlabel("Time")
ax1.plot_date(timenow, temperature, '-', label="tempurature", color='r')
fig.autofmt_xdate(rotation=60)
fig.tight_layout()
ax1.grid(True)
ax1.legend(loc='best', framealpha=0.5)
plt.savefig("figure.png")

I would just change ax1.plot_date(dates, temperature, '-', label="tempurature", color='r') to what I have in my example. I might be understanding the question wrong but I believe it's pretty straight forward.

EDIT: try adding this in prior to appending into your timenow array.

import datetime
t = eachRow[0]


timenow.append(t.strftime('%m/%d/%Y'))

edited Dec 16 '17 at 06:22

answered Dec 16 '17 at 06:06

wdlax11

822
3
11
29

thanks for this, but there is no change in output though I did as you said – Sri Harsha Dec 16 '17 at 06:12
can you please print out your timenow array and post it for me to see? – wdlax11 Dec 16 '17 at 06:15
here is the timenow array [datetime.date(2017, 12, 14), datetime.date(2017, 12, 14), datetime.date(2017, 12, 13), datetime.date(2017, 12, 14), datetime.date(2017, 12, 14), datetime.date(2017, 12, 13), datetime.date(2017, 12, 14), datetime.date(2017, 12, 13), datetime.date(2017, 12, 14)] – Sri Harsha Dec 16 '17 at 06:19
@SRIHARSHA see edit in post above try converting them from datetime.date into strings then using them. – wdlax11 Dec 16 '17 at 06:24
by making the changes you suggested some errors are raised view the errors at https://jpst.it/18HIk – Sri Harsha Dec 16 '17 at 06:39
I don't think using strings here makes sense. E.g. adding another date to the list would result in a plot like [this one](https://i.stack.imgur.com/EruOd.png), which is again undesired. – ImportanceOfBeingErnest Dec 16 '17 at 07:52

How to converting string dates(YYYY-MM-DD) from SQL database to matplotlib dates in python?

1 Answers1