Edgewall Software
Modify

Opened 10 years ago

Closed 10 years ago

Last modified 10 years ago

#8552 closed defect (worksforme)

Formatted time via SQL command FROM_UNIXTIME fails to replace %*

Reported by: admin@… Owned by:
Priority: low Milestone:
Component: report system Version: 0.10.4
Severity: minor Keywords: FROM_UNIXTIME MySQL report query
Cc: Branch:
Release Notes:
API Changes:

Description

Formatting timestamps (e.g. form the ticket table) via the MySQL command FORM_UNIXTIME sometimes (but then constantly for that special report) fails to replace every %* (like %d or %Y) appearance.

Expected Result (for the 'last change' column): Displayed Date/Time field like 13.05.2009 18:06 by dk

Actual Result: The timestamp is shown as %d.%m.%Y %H:%i by dk

Example: the report with this query (which replaces fine under the mysql CLI) fails to show the date/time:

SELECT DISTINCT p.value AS __colour__,
   (CASE
     WHEN (owner LIKE '%$USER%') THEN 5
     WHEN (cc LIKE '%$USER%') THEN 4
     ELSE 0
     END) AS __color__,
   CONCAT('CMSurf v', version) AS __group__,
   id AS ticket, summary, keywords, t.type AS type, 
   (CASE status WHEN 'assigned' THEN concat(owner, ' *') ELSE owner END) AS owner,
   t.time AS created,
   changetime AS _changetime,
   reporter AS _reporter, 
   (CASE t.changetime
     WHEN t.time THEN CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', t.reporter)
     ELSE CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', c.author)
     END) AS 'last change'
  FROM ticket t, enum p, ticket_change c
  WHERE status IN ('new', 'assigned', 'reopened') AND p.name = t.priority 
    AND p.type = 'priority' AND (t.id = c.ticket OR t.changetime = t.time) 
    AND (c.time IN (SELECT MAX(time) FROM ticket_change GROUP BY ticket))
    AND (milestone LIKE 'CMSurf%' OR summary LIKE 'CMSurf%')
  ORDER BY t.version, __color__ DESC, keywords DESC, summary

while this one runs just as expected:

SELECT DISTINCT p.value AS __color__,
   milestone AS __group__,
   id AS ticket, summary, keywords, t.type AS type, 
   (CASE status WHEN 'assigned' THEN concat(owner, ' *') ELSE owner END) AS owner,
   t.time AS created,
   changetime AS _changetime,
   reporter AS _reporter, 
   (CASE t.changetime
     WHEN t.time THEN CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', t.reporter)
     ELSE CONCAT(FROM_UNIXTIME(t.changetime, '%d.%m.%Y %H:%i'), ' by ', c.author)
     END) AS 'last change'
  FROM ticket t, enum p, ticket_change c
  WHERE status IN ('new', 'assigned', 'reopened') AND p.name = t.priority 
    AND p.type = 'priority' AND (t.id = c.ticket OR t.changetime = t.time) 
    AND (c.time IN (SELECT MAX(time) FROM ticket_change GROUP BY ticket))
    AND milestone != 'CMSurf 1.x'
  ORDER BY __group__, __color__, keywords DESC, summary

This is strange since the query parts are 100% identical and the replacement is supposed to be done by MySQL (i.e. before the table is rendered, though after the variable replacement, maybe that could be the first thing to check)

Attachments (0)

Change History (2)

comment:1 by Christian Boos, 10 years ago

Resolution: worksforme
Status: newclosed

Can't reproduce, both reports are working fine for me.

comment:2 by Christian Boos, 10 years ago

(I was testing with 0.11.6rc1, btw)

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.