09-17-08, 20:34 #1Registered User
- Join Date
- Sep 2008
Unanswered: Reporting Services and Epoch Time (MySQL DB)
The company I work for has recently purchased some Help Desk software which runs on a MySQL Database. Now, I would like to generate reports using Reporting Services 2005.
The problem I am facing is that the date field (integar) within the MySQL DB is a set of numbers (E.g. 1220560003) – this set of numbers (seconds) is based on Unix Time (Epoch time), starting from 01 01 1970.
I have managed to convert this time into a readable format using the SQL function DATEADD.
Here is the query I am running:
Select t1.Ticketid, Contents, t3.title as Module, t2.subject, t1.fullname as Staff,
cast(dateadd(ss,-14440, Dateadd(ss,t1.dateline,'19700101') +1) as smalldatetime) AS LoggedDate, t5.Title as Client
From swtickets t1, swticketposts t2, swdepartments t3, swusergroups t5, swusers t6
Where t1.ticketstatusid = @TicketStatus
and t5.usergroupid = @Client
and (t1.dateline >= @StartDate) and (t1.dateline-1 <=@EndDate)
and t1.ticketid = t2.ticketid
and t2.userid = t6.userid
and t6.usergroupid = t5.usergroupid
and t3.departmentid = t1.departmentid
and t2.dateline = (select min(dateline) from swticketposts t4
where t4.ticketid = t2.ticketid)
order by t2.dateline desc
The LoggedDate field then displays as 2008-07-21 12:15:00
Without the cast function it currently displays as 2008-07-21 12:15:13.000
The problem I am facing is within Reporting Services for users to have the ability to select a date using the Calendar boxes the fields need to be datetime. When I setup the parameters within Reporting Services for @StartDate and @Enddate and make them datetime, the report fails to run – with the error:
Arithmetic overflow error converting expression to data type datetime
So I guess what I am asking is:
Would anyone know what expression I could place in my Report for @StartDate and @EndDate to enable my report to work?
I tried this, but it didn't seem to work -
=DateTime.Parse(Parameters!StartDate.Value).ToStri ng("dd MMM yyy")
After researching on the net, the general consenus is to utilise the DateTime object which is part of .net to get my expression to work.
I have created multiple reports so far, but not based on any type of date range.
09-18-08, 04:39 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
There are easy ways to do this and efficient ways to do it, but I don't think that there are any solutions that are both easy and efficient at the same time. The easy way to do this is to build a MySQL view that does the conversion to a DATETIME for you. The efficient way is to mangle the SSRS (SQL Server Reporting Services) code to recognize and manage the epoch dating (Unix seconds count).
While it is a lot more work, I'd be willing to bet that you need to pursue the epoch dating solution to get decent performance. My luck with getting MySQL to cooperate with the DATETIME conversion efficiently has been somewhat sub-optimal.
09-19-08, 23:02 #3Registered User
- Join Date
- Jul 2003
- San Antonio, TX
If you can afford not to go after "live data", then you can establish a "reporting" environment by creating a nightly export from this mySQL thing into a real RDBMS, and then re-point your reports to it. Seconds from 01/01/1970 sounds very cute, but really impractical in the business world...wait, do I smell books cooking again?"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
09-20-08, 04:17 #4SQL Consultant
- Join Date
- Apr 2002
- Toronto, Canada