• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

graphing database entries over a 24 hour period

I'm trying to graph, via a bar graph and some code I found here on the forums, the number of database entries (in this case news stories) in 30 minute increments over a 24 hour period. For some reason it's graphing everything twice (for example, it's graphing an entry that was made today at 3pm on the graph at BOTH 3PM and 3AM!) Please help! It's driving me crazy!! The date/time entry (DateTimePosted) was entered into the database using CreateODBCDateTime(Now()). If you need to know any other information please let me know!
<cfparam name="URL.author" default="all" type="any">
<cfif URL.author IS "all">
<cfset AuthorName = "Big Radio News">
<cfelse>
<cfquery name="NewsUsers" datasource="WEKZdata">
SELECT * FROM NewsUsers
WHERE UserID = #URL.Author#
</cfquery>
<cfset AuthorName = NewsUsers.FirstName>
</cfif>
<CFPARAM name="URL.DATE" Default="#CreateDate(Year(Now()),Month(Now()),Day(Now()))#">
<cfset DATE = #CreateODBCDate(URL.DATE)#>
<CFPARAM NAME="timeslices" DEFAULT="48">
<CFPARAM NAME="slice_minutes" DEFAULT="#1440/timeslices#">
 
<CENTER>
<cfchart format="jpg" chartwidth="900" chartheight="350" showlegend="yes" xaxistitle="Vote time: " yaxistitle="Number of Stories: " >
    <cfchartseries type="bar" itemcolumn="Stories Posted on #DateFormat(DATE,"YYYY/MM/DD")#" >
    <cfchartseries type="bar" itemcolumn="Stories Posted By #AuthorName#" >
 
<!--- Loop through DAYS and find stories for each day --->
<CFLOOP INDEX="preiod_num" FROM="1" TO="#timeslices#">
   <!--- Set date variables --->
   <CFSET PERIOD_START = DateAdd("n", preiod_num*slice_minutes, DATE)>
   <CFSET PERIOD_END = DateAdd("n", slice_minutes, PERIOD_START)>
 
      <!--- The Graph gets its data from the NewsStory table  --->
 
<!-- if the user wants the data from all users -->
 
  <cfif URL.author IS "all">
    <CFQUERY NAME="DownloadInfo" DATASOURCE="WEKZdata">
      SELECT * FROM NewsStory
      WHERE DateTimePosted BETWEEN #PERIOD_START# AND #PERIOD_END#
   </CFQUERY>
   
  <cfelse>
<!-- if the user only wants the data for one specific user (author) -->  
 
  <CFQUERY NAME="DownloadInfo" DATASOURCE="WEKZdata">
      SELECT * FROM NewsStory
      WHERE DateTimePosted BETWEEN #PERIOD_START# AND #PERIOD_END# 
      	AND Author = '#URL.Author#'
   </CFQUERY>
 </cfif>
 
   <cfchartdata item="#TimeFormat(PERIOD_START, "short")#" value="#NumberFormat(Val(DownloadInfo.recordcount),"0.00")#">
 
</CFLOOP>
 
    </cfchartseries>
</cfchart>

Open in new window

0
benthompson22
Asked:
benthompson22
  • 3
1 Solution
 
ZvonkoSystems architectCommented:
The script seams to be generated by me.
Change please the cfchartdata statement to this:
<cfchartdata item="#LSTimeFormat(PERIOD_START, "short")#" value="#NumberFormat(Val(DownloadInfo.recordcount),"0.00")#">

Open in new window

0
 
ZvonkoSystems architectCommented:
By the ay, what format is URL.DATE?
0
 
ZvonkoSystems architectCommented:
I have tested again the upper code and it works for me.
So my assumption is that it has to be something with your DB data.

What you can do is to simplyfy your cfquery.
Like this:
   <CFQUERY NAME="DownloadInfo" DATASOURCE="WEKZdata">
      SELECT * FROM NewsStory
      WHERE DateTimePosted BETWEEN #CreateODBCDateTime(PERIOD_START)# AND #CreateODBCDateTime(PERIOD_END)#
    <cfif NOT URL.author IS "all">
        AND Author = '#URL.Author#'
    </cfif>
   </CFQUERY>

Open in new window

0
 
benthompson22Author Commented:
Thanks for your help! The field in my database for DateTimePosted was a text field and not a Date/Time field so it was storing all the entries in a 12 hour format, not a 24 hour format!

Thanks for you help again!

Ben
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now