RSS Feed

Plugin Developer Discussion

I am making an activity graph plugin, that shows a graph of BugEvents over time. So basically, I need to do an aggregate query to count the number of BugEvents each day. After playing around with this for a while. I realized that I need to convert the dates to CTZ before the aggregation. However, the DateTime SQL functions aren't allowed, and since they are not the same across db engines, it still presents a problem. Something like either of the following would do the trick:


var sdt = api.Database.DateAdd(DateTimeOffset.Seconds,
    "dt",
    api.TimeZone.CTZOffsetFromUTCSeconds());

Or

var sdt = api.TimeZone.QueryCTZFromUTC("dt");

Then This query would be closer to actually being usable.

var q = api.Database.NewSelectQuery("BugEvent");
q.AddGroupBy(string.Format("YEAR({0}), MONTH({0}), DAY({0})", sdt));
q.AddOrderBy(string.Format("YEAR({0}) ASC, MONTH({0}) ASC, DAY({0}) ASC", sdt));
q.AddSelect(string.Format("COUNT(ixBugEvent) AS c, YEAR({0}) AS ixYear, MONTH({0}) AS ixMonth, DAY({0}) AS ixDay", sdt));
q.AddWhere("ixBug IN (" + srgixbug + ")");

Hi Stefan,

Maybe I'm missing something, but can't you use CTimezoneApi.CTZFromUTC() on each date record after you're done with the query?

Dan

Not in this case, since the query involves having the database group and aggregate based on the date part of the datetime stamp. The query gets the number of edits for each day in the client's timezone.

Ah, I see. But you could potentially offset your dates before passing them into the query using CTZOffsetFromUTCSeconds, could you not? (For the time being, the CQuery enhancement would certainly be more elegant.)

Something that works like the following pseudo-code would work, but the DB can handle this MUCH more efficiently.

var end = CTZFromUTC(GetDtOfLastEdit(rgBugs)).Date.AddDays(1);
for (var dt = CTZFromUTC(GetDtOfFirstEdit(rgBugs)).Date;
    dt < end; dt = dt.AddDays(1))
{
    yield [dt, CountForDay(rgBugs, UTCFromCTZ(dt))];
}

Agreed. The query fix would be optimal. I'll open a case and bring it up with the development team.

In the meantime, would you be OK with using the workaround to get your first version up and running?

-Dan

PS- Sounds like an awesome plugin!

Yeah! I already have it working. (I was using a different, but in some cases more efficient, and others more efficient workaround)

https://developers.fogbugz.com/default.asp?pg=pgDownload&pgType=pgFile&ixBugEvent=555&ixAttachment=292&sTicket=&sFileName=screenshot3655.png

Hi Stefan,

Doesn't the DateAdd solution suffer from Daylight Savings Time problems (since the offset seconds aren't always the same)?

Ouch, you are correct. So I suppose the db can't be used for anything but UTC ranges. In that case it seems 1 query per day is the best one can hope for.