[SOLVED] Offset between point and axis (TDbChartSource)

[tudi_x]

Hi!
I am rendering a chart with TAChart in Lazarus 1.6 on Windows 7.
I am having the issue that the points have an offset with the axis as per attached PNG file.
I am not generating the labels for the bottom axis.
I am transforming the dates to Unix time when entering the dataset and converting them again to date when rendering.
Please advise where I could look for not having this offset, I am kind of lost.

Thank you

Dataset values:
"81268"   "6/7/2015"
"84268"   "6/8/2015"
"90842"   "6/9/2015"
"86541"   "6/10/2015"
"101092"   "6/11/2015"
"86012"   "6/12/2015"
"94796"   "6/13/2015"
"94700"   "6/14/2015"
"64700"   "6/15/2015"

Code: Pascal  [Select][+][-]

1. procedure ChartDSet.UpdateBtmAxisDATEValues(var ATextAxis: string; AMark: double);
2. var
3.   new_text: string;
4.   a: aUtil;
5.  
6. begin
7.   new_text := DateToStr(a.UNIXTimeToDateTime(strtoint64(ATextAxis)));
8.   ATextAxis := Format('%s', [new_text]);
9. end;  
10.  

And the series:

Code: Pascal  [Select][+][-]

1. procedure ChartDSet.AddSeries(XFieldName: string; YFieldName: string; AColor: integer);
2. const
3.   COLORS: array[0..4] of TColor = (clBlue, $00000095, $00006A00, $000000D2, $006A0000);
4.   LineStyle: array[0..2] of TFPPenStyle = (psSolid, psSolid, psSolid);   //(psSolid, psDashDot, psDash);
5.  
6. var
7.   new_series: TLineSeries;
8.   DB_datasource: TDataSource;
9.   chart_data_source: TDbChartSource;
10.  
11. begin
12.   new_series := TLineSeries.Create(nil);
13.  
14.   new_series.LinePen.Color := COLORS[AColor];
15.   new_series.LinePen.Style := LineStyle[AColor mod 2];
16.   new_series.LinePen.Width := 2;
17.   (* link between value and line *)
18.   new_series.Marks.LinkPen.Color := clBlack;
19.   new_series.Marks.LinkPen.Width := 1;
20.   new_series.Marks.LinkPen.EndCap := pecRound;
21.   new_series.Marks.LinkPen.JoinStyle := pjsRound;
22.   (* points *)
23.   new_series.Pointer.Brush.Color := clRed;
24.   new_series.Pointer.Visible := True;
25.   new_series.Pointer.Style := psCircle;
26.   new_series.Pointer.HorizSize := 2;
27.   new_series.Pointer.VertSize := 2;
28.   new_series.ShowPoints := True;
29.  
30.   new_series.Marks.Clipped := True;
31.   new_series.Marks.LabelBrush.Color := clSkyBlue;
32.   new_series.Marks.LabelBrush.Style := bsSolid;
33.   new_series.Title := YFieldName;
34.   new_series.Marks.Frame.Color := clBlack;
35.   new_series.Marks.Frame.Width := 1;
36.   new_series.Marks.Frame.Mode := pmCopy;
37.  
38.   DB_datasource := TDataSource.Create(nil);
39.   DB_datasource.DataSet := _dataset;
40.   DB_datasource.DataSet.Open;
41.   if DB_datasource.DataSet.RecordCount <= 50 then
42.   begin
43.     new_series.Marks.Style := smsValue;
44.     new_series.Marks.Shape := clsRoundRect;
45.   end;  
46.  
47.   chart_data_source := TDbChartSource.Create(_chart);
48.   chart_data_source.DataSource := DB_datasource;
49.  
50.   chart_data_source.FieldX := XFieldName;
51.   chart_data_source.FieldY := YFieldName;
52.  
53.   new_series.ListSource.CopyFrom(chart_data_source);
54.   _chart.AddSeries(new_series);
55.  
56.   DB_datasource.Free;            
57.  

[wp]

I don't understand why you are using Unix times here. If the dataset contains values as you list then there is no need to use Unix times, you get the the dates directly from the dataset field.

If I plug your table of values directly into a TMemDataset then the location of the data points is correct - see screen shot and attached demo.

What is the aUtil? It seems to do the UnixTime-to-Datetime conversion. Are you sure that the date values are calculated correctly here and do not contain a fractional part?

[tudi_x]

Regarding the UNIX time values I was basically trying to simulate the fact that I would be having UNIX time values from different queries that I would like to have them displayed as dates for OX scale.

I rewrote the chart conversion unit using same initialization mainly and using the below to convert to from UNIX time.
I am obtaining fractional values.

Code: Pascal  [Select][+][-]

1. function RenderChart.TryUNIXTimeToDateTime(UNIXTime: string; var dt: TDateTime): boolean;
2. const
3.   UnixStartDate: TDateTime = 25569.0; // 01/01/1970
4.  
5. var
6.   d: int64;
7.  
8. begin
9.   if trystrtoint64(UNIXTime, d) and (Length(UNIXTime) = 10) and (AnsiLeftStr(UNIXTime, 2) = '14') then
10.   begin
11.    dt :=d / 86400 + UnixStartDate;
12.     Result := True;
13.   end
14.   else
15.     Result := False;
16. end;  
17.  

and the OX marks conversion as per the below:

Code: Pascal  [Select][+][-]

1. procedure RenderChart.FormatOXMarks(var ATextAxis: string; AMark: double);
2. var
3.   dt: TDateTime;
4.  
5. begin
6.   if _oxIsUNIXTime and TryUNIXTimeToDateTime(ATextAxis, dt) then
7.   begin
8.     ATextAxis := Format('%s', [DateTimeToStr(dt)]);
9.     exit;
10.   end;
11.  
12.   if _oxIsDate then
13.   begin
14.     ATextAxis := Format('%s', [DateTimeToStr(StrToInt(ATextAxis))]);
15.     exit;
16.   end;
17. end;
18.  

The dataset is populated as:

Code: Pascal  [Select][+][-]

1. procedure TForm1.FormCreate(Sender: TObject);
2. var
3.   c: ChartInfo;
4.   r: RenderChart;
5.  
6. begin
7.   c.ADataset := TVirtualTable.Create(nil);
8.   with c.ADataset.FieldDefs do
9.   begin
10.     Add('ID', ftInteger);
11.     Add('VALS', ftInteger);
12.     Add('TheDate', ftDateTime);
13.     Add('UNIXtime', ftInteger);
14.   end;
15.  
16.   c.ADataset.Open;
17.  
18.   c.ADataset.AppendRecord([1, 500, '5/5/2016', 1462406400]);
19.   c.ADataset.AppendRecord([2, 600, '5/6/2016', 1462492800]);
20.   c.ADataset.AppendRecord([3, 450, '5/7/2016', 1462579200]);
21.   c.ADataset.AppendRecord([4, 800, '5/15/2016', 1463270400]);
22.   {
23.   c.ADataset.AppendRecord([1, 500, '5/5/2016']);
24.   c.ADataset.AppendRecord([2, 600, '5/6/2016']);
25.   c.ADataset.AppendRecord([3, 450, '5/7/2016']);
26.   c.ADataset.AppendRecord([4, 800, '5/15/2016']); }
27.  
28.   c.OXTitle := 'Title OX';
29.   c.Title := 'Title Test';
30.   c.MultipleSeries := False;
31.   c.SpoolPath := 'test.png';
32.  
33.   r := RenderChart.Create(c);
34.   r.Render(1);
35.   r.CleanUp;
36. end;      
37.  

Rendering, for dates I have no offset as per attached.
The offset appears for when converting from UNIX time values and most probably is because of the fractional values.

I saw that there is an option of generating my own labels.
As UNIX time values would be a requirement for me, do you think that generating my own labels would be the way to go?

Thank you.

[wp]

Please post a small standalone compilable project which shows the issue (only pas, lfm, lpi, lpr files, packed to a single zip).

BTW, do you know that there is a Unix-to-DateTime conversion in DateUtils?

[tudi_x]

I have switched to the dateutils function. Using TMemDataset.
Please find the project attached.
The dataset values are showing the issue when spanning many days.
When spanning few minutes I cannot distinguish an offset.

Thank you!

[wp]

OK, I see now what's wrong: TAChart tries to seek "nice" values for the axis labels. But: you are using UNIX-Times for the x values - therefore, TAChart seeks "nice" unix labels. Since unix time and date time are not coincident the "nice" unix labels do not lie on the date boundaries.

The solution is that you must convert the unix times to date times before plotting. There are several ways to do this:

• Add a calculated dataset field which converts the Unix times to date times.
• Or: In your code, you copy the DbChartSource to the internal listsource of the series. You could iterate through all values and convert the x values from unix to date time.

Code: Pascal  [Select][+][-]

1. for i := 0 to new_series.Count-1 do
2.   new_series.SetXValue(i, UnixToDateTime(new_series.GetXValue(i));

• Or: add a user-defined ChartAxisTransformation which you assign to the bottom axis. Provide event handlers for conversion from unix to date time and vice versa. Since you do all the chart design by code, this is more complex than the other two solutions, though

[tudi_x]

I implemented the second solution and it worked.

Thank you so much.