hourly click stats on 12-hour clock instead of 24

[jhagstrand]

In Mailings - title - Statistics - Total Clicks - choose one day, the bar graph displays hourly counts for a 24-hour day. The display is incorrect. The sql query uses a 12-hour format, so the AM and PM times get added together and displayed on the first half of the bar graph. The afternoon times are all zero.

The problem is in MailingStatImpl.java line 699:
sqlQuery += " = '" + formatter.format(startDate) + "' group by " + AgnUtils.sqlDateString("rdir.change_date", "%h");

In MySql date formatting, "%h" is a 12-hour hour. It needs to be capital "%H" to get a 24-hour hour.

John

[maschoff]

Thank you for your bug report + fix - we will include it in our next release!

[jhagstrand]

Thanks for the reply. When is the next release? In the meantime can I fix it myself? I see there is a build.xml file in the src. Should I install Ant and do a build all? Is there any documentation on how to build OpenEMM from the source?

[maschoff]

First we have to look deeper into this problem since all of our installations seem to work correctly. As soon as we have identified the real problem we will post a new class file to the SourceForge bug tracker and instructions on how to replace the old file. What version of MySQL do you use?

[jhagstrand]

MySql 4.1.20

[maschoff]

Strange, exactly the same version we use on most of our servers. I think we have to dig a little bit deeper ...

[jhagstrand]

Here is the actual data:

Hour count
3 18
4 33
5 98
6 89
7 84
8 94
9 107
10 105
11 92
12 94
13 57
14 106
15 54
16 47
17 48
18 34
19 35
20 23
21 22
22 11
23 7

Here is how it displays:

Hour count
3 72
4 80
5 146
6 123
7 119
8 117
9 129
10 116
11 99
12 94
13 57
14 106

See how it wraps?:

Hour AM + PM = count
3 18 + 54 = 72
4 33 + 47 = 80
5 98 + 48 = 146
6 89 + 34 = 123
7 84 + 35 = 119
8 94 + 23 = 117
9 107+ 22 = 129
10 105+ 11 = 116
11 92 + 7 = 99
12 94 + 0 = 94
13 57 + 0 = 57
14 106+ 0 = 106

John

[maschoff]

Well, we have no idea why it works with us, but anyway, according to the MySQL manual your suggestion is the right solution. We posted the bug fix to the SourceForge bugtracker here:

http://sourceforge.net/tracker/index.ph ... tid=848488

Could you please confirm that the fix works for you? Thank you!

[jhagstrand]

Yes it works!
Thanks very much.
John

[bacacosta]

Hello!

I'm still facing the same problem, but on the Statistics/Open rate over time page. I can only see open rates until 12 o'clock (after that all hours are empty). I believe it is the same problem, but i changed the class suggested on bugtracker and it didn't solve the problem for me. And the strange thing is, i'm using OpenEMM 5.5.0 already (which is free of this bug, according to SourceForge). My MySQL version is 5.0.18.

Could you please help me to face this issue?

Thanks in advance!
Rodrigo

[maschoff]

Please see here:

http://sourceforge.net/tracker/index.ph ... tid=848488

The bug is in OpenEMM 5.5 (the feature did not exist before) but can be fixed by replacing the buggy class with the attached one.

[bacacosta]

Thanks for your help! This new class worked just fine!

Rodrigo

[yvonboulianne]

seems to have the sames bug in the 6.1 version
how to fix ?

thanks
Yvon