1. 

   ishmael, Member

   Hello!

   I ran into some issue I dont know why happens.
   Its probably due to some user error on my behalf but I cant obviously see what..

   When I run the following query in the database using HeidiSQL or any other client I get the results I want but when I view it in myDBR the displayed results are different.

   select
   date_format(t2.created_at, '%Y-%v') Week,
   count(t1.someid) as 'Number of replies',
   avg(t1.x_sometime) as 'Minutes',
   (avg(t1.x_sometime)/60) as 'Hours',
   sec_to_time(avg(t1.x_sometime)*60) as 'hh:mm:ss'
   from database.table1 t1
   inner join database.table2 t2 on t1.x_ticket = t2.someid
   inner join database.table3 t3 on t2.x_service = t3.someid
   where t2.created_at between '2009-01-01' and '2011-01-01'
   and t2.status < 4
   and t2.filter_id > 0
   and t2.category != 16
   and t2.created_by = 1
   and ( t3.x_cola like 'Something%' )
   and t3.x_cola not like '%/Something else/%'
   and t1.x_sometime is not null
   group by Week
   order by Week asc;

   From myDBR GUI:
   2009-51 85 1,020.86 17.01 17:00:52
   2009-52 59 1,881.90 31.36 01:00:00
   2009-53 41 2,667.10 44.45 01:00:00
   2010-01 132 1,113.36 18.56 18:33:21
   2010-02 67 508.97 8.48 08:28:58
   2010-53 32 1,266.59 21.11 21:06:36

   From SQL Client:
   "Week";"Number of replies";"Minutes";"Hours";"hh:mm:ss"
   "2009-52";"59";"1881.8983";"31.36497175";"31:21:54"
   "2009-53";"41";"2667.0976";"44.45162602";"44:27:06"
   "2010-01";"132";"1113.3561";"18.55593434";"18:33:21"
   "2010-02";"67";"508.9701";"8.48283582";"08:28:58"
   "2010-53";"32";"1266.5938";"21.10989583";"21:06:36"

   As you can see myDBR reported 01:00:00 several places the SQL client does not.
   The report is a simple:

   select 'dbr.title', 'Title';
   select 'dbr.subtitle', 'Subtitle';
   select 'dbr.keepwithnext';
2. 

   myDBR Team, Key Master

   When used as regular time, column is formatted based on the format defined in preferences. Regular time has range of 00:00:00 - 23:59:59.

   If you apply a style '%s' to a time column or use aggregate function sum or avg, a time column is then treated as duration and the format is always H:M:S.

   So just adding

   select 'dbr.colstyle', 5, '%s';

   before the query should give you the result you are expecting.

   --
   myDBR Team

   P.S Using '%x-%y' as a parameter for your date_format will keep your weeks / years in sync even when the week is spanning across two years.
3. 

   ishmael, Member

   Thanks!
   As excepted.. stupid user ;o)

   Side note:

   %x (week) as a parameter under date_format works best with %v and not %y.

   http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
   %X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
   %x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
4. 

   myDBR Team, Key Master

   We'll try to see that the time duration is bit clearer in the documentation. myDBR will automatically adapt for duration if aggregate functions are applied to column.

   Side note:

   %x (week) as a parameter under date_format works best with %v and not %y.

   Yes, use either %X or %x depending on your preference.

   Compare:
   mysql> select date_format('2010-01-01', '%Y-%v'); -> 2010-53

   To:
   select date_format('2010-01-01', '%x-%v'); -> 2009-53

   --
   myDBR Team

Reply

You must log in to post.