Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    1

    Unanswered: Reporting Services and Epoch Time (MySQL DB)

    Hi There,

    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.

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rdjabarov
    ... a nightly export from this mySQL thing into a real RDBMS...
    elitist bastard
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •