This discussion is archived
4 Replies Latest reply: Nov 16, 2012 11:19 AM by StewStryker RSS

AnyGantt APEX chart wrong Time Format

972561 Newbie
Currently Being Moderated
I'm trying to create an anyGantt chart within APEX but the XML keeps misconverting the time section of date. Thus, where an activity should start on 05-11-2012 15:16:17 (DD-MM-YYYY HH24:MI:SS), it actually starts on 05-11-2012 12:11:00. It seems to take the Year as the Hour and the Month as Minutes, but assigns nothing to the minutes. The Year is also random, it's either 0, 1, 12, or 13.

So say I have this Record:
ID___________NAME______________DATE_START_____________________DATE_END_____________________________DRIVERID
2____________Driving_____________21-9-2012 12:04:40_______________22-9-2012 13:05:50______________________228
And complete this statement:
SELECT null link,
t.driverid resource_id,
TRIM(r.lastname) || ', ' || r.firstname resource_name,
NULL parent_id,
t.date_start actual_start,
t.date_end actual_end
FROM "user"."gantt" t, "user"."drivers" r
WHERE r.driverid = t.driverid
The Gantt chart translates this activity to:
RESOURCE_NAME______RESOURCE_ID_______________ACTUAL_START______________ACTUAL_END
Sleeman,_Bob_________228________________________21-9-2012 *01:09:00*___________22-9-2012 *01:09:00*
Thus giving me a wrong gantt chart, as these records would overlap, and start at the wrong time.


The XML settings are as following:
<settings>
________<title_enabled="False"_/>
<outer_margin_left="0"_top="0"_right="0"_bottom="0"_/>
____<background>
__________<fill_type="Solid"_color="0xFFFFFF"_/>
__________<border_enabled="false"/>
__________<corners_type="Square"/>
____</background>
____<navigation_enabled="true"_/>
____<locale>
_______<date_time_format_week_starts_from_monday="True">
_________<months>
___________<names>Januari,Februari,Maart,April,Mei,Juni,Juli,Augustus,September,Oktober,November,December</names>
___________<short_names>Jan,Feb,Mar,Apr,Mei,Jun,Jul,Aug,Sep,Okt,Nov,Dec</short_names>
_________</months>
_________<week_days>
___________<names>Zondag,Maandag,Dinsdag,Woensdag,Donderdag,Vrijdag,Zaterdag</names>
___________<short_names>Sun,Mon,Tue,Wed,Thu,Fri,Sat</short_names>
_________</week_days>_
_______<format>
__________<full><![CDATA[%yyyy.%MM.%dd.%HH.%mm.%ss]]></full>
__________<date><![CDATA[%yyyy.%MM.%dd]]></date>
__________<time><![CDATA[%HH.%mm.%ss]]></time>
________</format>
_____</date_time_format>
____</locale>
_</settings>
I know the default format for the 24-hour notation in Oracle is "HH24" and the minute notation is "mi" and not "mm", but the notation in AnyGantt says minutes are %mm (lowercase) and months are %MM. The notation in anyGantt is different from the Oracle-notation. Who can explain this problem, and better, solve it?
  • 1. Re: AnyGantt APEX chart wrong Time Format
    Hilary Expert
    Currently Being Moderated
    Hi 969558,

    Would you mind creating a testcase on apex.oracle.com, and updating this thread with the login credentials so that I can take a closer look at your chart? In your gantt chart query, are you referencing DATE or TIMESTAMP columns?

    The date notation used in the chart XML is specific to AnyChart, and outlined in the Formatting Parameters List in their online User's Guide, here: http://anychart.com/products/anygantt/docs/users-guide/locale.html?fromtree. So using AnyChart notation, 24 hours would be applied using %HH, as seen in the <time> tags of your XML.

    Regards,
    Hilary
  • 2. Re: AnyGantt APEX chart wrong Time Format
    972561 Newbie
    Currently Being Moderated
    http://apex.oracle.com/pls/apex/f?p=13798:8:5628553353460::NO:::

    Username "VIEW"
    password "Trust"

    I am referencing a DATE column. I am indeed using %HH, but it doesn't take the hour.

    As you can see in this column, all dates have time values. However, on mouseover and on zooming in, you'll notice that the time values are lost in the conversion!
  • 3. Re: AnyGantt APEX chart wrong Time Format
    Hilary Expert
    Currently Being Moderated
    Hi Rik,

    Thanks for providing the login credentials to your example. I hope you don't mind but I've created a second Gantt chart on the page, to illustrate how to display the time component for the tasks on the chart.

    In order to represent the time component on your charts, you need to use TIMESTAMP columns in your chart query. If you run your existing query from the chart in SQL Workshop, you'll see that the results only return the date component, and do not include the time. In order to retrieve the time component, you would need to use the TO_CHAR function on your DATE columns, which you have done for your report on the same page. Given that the chart query syntax must either reference DATE or TIMESTAMP columns, you therefore need to use the TO_TIMESTAMP function in this situation to ensure that you're capturing the date and time. I've got an example on my sample application, here: http://apex.oracle.com/pls/apex/f?p=36648:83.

    So to get the chart displaying the correct information, I did the following:
    1) Updated the chart series query to use TO_TIMESTAMP on your DATE columns
    2) Updated the Application Timestamp Format via the Edit Application Properties page, changing it from DS to DD-MON-YYYY HH:MIPM, otherwise your timestamp values would still not display the time component.

    Let me know if you've any further questions. I hope this helps.

    Regards,
    Hilary
  • 4. Re: AnyGantt APEX chart wrong Time Format
    StewStryker Journeyer
    Currently Being Moderated
    Hilary,

    Thanks for providing your example, including the sample query. I've tried to create Gantt charts before but can never get past entering the SQL query. I just tried to create a new Gantt chart (not Resource type) and I copied and pasted the last section of your example into Apex, but it's saying the syntax is wrong. I've tried adjusting to either of the two formats the error message demands, but it still doesn't validate. I turned validation off, but when I run it, it complains "No project data was found."

    We're running Apex 4.0.1. Could that be the problem?

    TIA,

    Stew

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points