Drawing plots from sql data using datetime

[Pluvius]

Hello everybody!

I've this sql table where I have two columns to draw a plot with their data, the first one is easy, could be converted to double , and placed on the Y axis but the second column is of type "datetime" so it's a bit difficult. I've seen the cpuplot example, it would perfectly suit my needs if I could find a way to display datetime on the X axis, all the examples that I ve checked so far, use an enum for this purpose or a QTimer.

The only hint I could find so far is on this thread but it's a bit vague: http://qt-project.org/forums/viewthread/23752

Any hints would be great and well appreciated

Thanks in advance!

[Uwe]

1) What version of Qwt are you using
2) How are the datetime values stored ( time elapsed since some start point - or valid UTC dates/time as string - or ... )
3) What do you want to display as scale ( similar question to 2 )

Uwe

[Pluvius]

Hello Uwe and thank you for replying

1- I'm using qwt-6.0.1
2- Data is exclusively UTC dates/time as string as you've stated, the user will have to choose two dates, then data will be retrieved and the plot will be populated
3- A plot is worth a thousand words ; here's what I'd like to achieve with qwt, I'm doing it so far with jpgraph (PHP)

graph.png

[Uwe]

In Qwt 6.1 you find a new scale engine for datetime values, that aligns the scale and finds tick positions according to years, months, weeks, days, hours, minutes, seconds, milliseconds. When you always have ticks for every second this is maybe not necessary, but as soon as you want to display scales with more than a couple of seconds only ( IMO the plot of your screen shot looks a bit ugly and needs too much horizontal space because of showing too many tick labels - every 5 or 10 ticks should be enough ) you want to have it aligned properly.

QwtDateScaleEngine and QwtDateScaleDraw use QwtDate, that offers the translations between QDateTime and double. Even if you want to stay with Qwt 6.0 you should have a look at its implementation doing something similar in your code.

Uwe

[Pluvius]

You are right about the the plot in the screenshot, that's one of the reasons, I want to move to qwt, furthermore, I'll add proper alignement.

QwtDateScaleEngine and QwtDateScaleDraw use QwtDate, that offers the translations between QDateTime and double.

This should get me started, thank you !

[RogerWilco77]

Good afternoon,

I am facing a similar situation.
A database of power consumption readings.
Basically it consists of two tables (one with different meters and one with readings).

I have just installed qwt 6.1rc2 because it seems to have some useful features for this.

Actually the code (not elegant but ok for a beginner, I hope) works and gives me a graph.
Unfortunately the x-axis is totally cryptic. I am still struggling with QwtDateScaleEngine and QwtDateScaleDraw.

However, maybe the first part helps and maybe somebody gives me a hint about the usage of the scale engine and scale draw:

model = new QSqlRelationalTableModel;
    model->setTable("tbl_zaehlerstaende");                                                      // table with meter readings      
    model->setRelation(2, QSqlRelation("tbl_zaehler", "zaehler_ID", "name"));    //QSqlRelationalTableModel to link the two tables
    model->setFilter("name = 'my_meter'");                                                      //show only my own consumption
    model->sort(1, Qt::AscendingOrder);                                                          //sort in chronological order
    model->select();
 
    ui->tv_overview->setModel(model);
    ui->tv_overview->setItemDelegate(new QSqlRelationalDelegate(ui->tv_overview));
    ui->tv_overview->show();
 
 
 
 
int k = model->rowCount();                          //number of datapoints, ie readings
        QVector<QPointF> datapoints;
 
 
 
 
        for (int i = 0; i < k; i++)
        {
 
            QDateTime date;                                                             //the format in MySql is actually just DATE, but it does not seem to matter
            date = (model->data(model->index(i,1))).toDateTime();
 
            qreal x;
 
            x = QwtDate::toDouble(date);                                           //qwt needs a double to work
 
 
 
            datapoints.append(QPointF(x, qreal(model->data(model->index(i,3)).toInt())));
 
        }
 
            QwtPlotCurve *curve = new QwtPlotCurve();
 
            curve->setSamples(datapoints);
            curve->attach(ui->qwt_plot);
 
            ui->qwt_plot->replot();

model = new QSqlRelationalTableModel;
    model->setTable("tbl_zaehlerstaende");                                                      // table with meter readings      
    model->setRelation(2, QSqlRelation("tbl_zaehler", "zaehler_ID", "name"));    //QSqlRelationalTableModel to link the two tables
    model->setFilter("name = 'my_meter'");                                                      //show only my own consumption
    model->sort(1, Qt::AscendingOrder);                                                          //sort in chronological order
    model->select();

    ui->tv_overview->setModel(model);
    ui->tv_overview->setItemDelegate(new QSqlRelationalDelegate(ui->tv_overview));
    ui->tv_overview->show();




int k = model->rowCount();                          //number of datapoints, ie readings
        QVector<QPointF> datapoints;




        for (int i = 0; i < k; i++)
        {

            QDateTime date;                                                             //the format in MySql is actually just DATE, but it does not seem to matter
            date = (model->data(model->index(i,1))).toDateTime();

            qreal x;

            x = QwtDate::toDouble(date);                                           //qwt needs a double to work



            datapoints.append(QPointF(x, qreal(model->data(model->index(i,3)).toInt())));

        }

            QwtPlotCurve *curve = new QwtPlotCurve();

            curve->setSamples(datapoints);
            curve->attach(ui->qwt_plot);

            ui->qwt_plot->replot();

To copy to clipboard, switch view to plain text mode 

Really I used google, I read the documentation. But I find it hard to get started.

Many greetings,

RogerWilco

[Uwe]

The code translates the dates into milliseconds since Epoch what is correct, when you want to use QwtDateScaleEngine and QwtDateScaleDraw.

All what is missing is:

ui->qwt_plot->setAxisScaleDraw( QwtPlot::xBottom, new DateScaleDraw() );
ui->qwt_plot->setAxisScaleEngine( QwtPlot::xBottom, new QwtDateScaleEngine() );

ui->qwt_plot->setAxisScaleDraw( QwtPlot::xBottom, new DateScaleDraw() );
ui->qwt_plot->setAxisScaleEngine( QwtPlot::xBottom, new QwtDateScaleEngine() );

To copy to clipboard, switch view to plain text mode 

Uwe

[RogerWilco77]

Thanks a lot. It was really simple.
I tried not to just copy but to understand.
Well, obviously I would have had to look under QwtPlot and fight my way via setAxisScaleDraw to to the usage of DateScaleDraw...
Now that I know it, it appears so logial....