1 Reply Latest reply on May 5, 2010 5:53 PM by ozwalkerz

    Time discrepancy in sql table versus console

    Rookie
    lloo May 5, 2010 4:05 PM

      I am looking at my ticket details in the console and it is showing closed 5/5/10 2:36pm

      I check in the im_incident_resolution table for same record and it is showing for im_creation_date of 5/5/10 9:41pm

       

      So it looks like in the tables it's 7 hours and 5 minutes ahead of what it is showing in the console.

       

      Please advise as we use a view from the sql table to generate excel reports from data in servicedesk.

       

      I have version 7.2.6 thanks

      • 2707 Views
      • Tags:
        • 1. Re: Time discrepancy in sql table versus console
          Apprentice
          ozwalkerz May 5, 2010 5:53 PM (in response to lloo)

          Hi Leslie,

           

          Time/Date is stored in GMT time, so the difference should correspond to your time zone.

           

          eg, we're in Canberra, Australia with a time zone of +10 so our DB shows time/date as 10 hours behind the console.

           

          You can use a calculation to correct it,  but if you tell it to add 10 hours to the date (in our case) then that is then wrong when we hit daylight savings time, or if you run the query from another timezone.

           

          I have been using the following oracle code - it seems to work for us, but you should test whether it works for your SQL before you trust it.

           

           

           

          tps_date_sent + (To_Number(rTrim(rTrim(To_Char(sessiontimezone), '0'), ':'))/24)

           

           

          Hope this helps.....

           

           

          Stuart