Service Desk Practitioners Forum
cancel

SD 4.5(SP-25) - Actual Start & Finish time errors.

SOLVED
Go to solution
Highlighted
Michael Daly_2
Super Contributor.

SD 4.5(SP-25) - Actual Start & Finish time errors.

I have two Work Orders (out of ten) that are related to a Change. The Work Orders have an Actual Start of 31/03/08 15:55 (a Monday and last day of the month) and an Actual Finish of 31/03/08 16:55. A duration of one hour.

If I do a query from our MS SQL 2000 database via Enterprise Manager and do a query on the WorkOrders view for this specific WorkOrder it shows this same WorkOrder as having an Actual Start (of 31/03/08 16:55) and Actual Finish (of 31/03/08 17:55) an 'hour' later. This is an error but I don't know why.

If I change the Actual Start Date+Time on the WorkOrder form back to the 28/03/08 15:55 (a Friday) and the same with the Actual Finish back to the previous Friday of 28/03/08 15:55, the date and time on the SQL database are correct - i.e. The SQL database times match the date and times shown on the WorkOrder form.

Does anyone have any idea why the SQL database times for Actual Start and Actual Finish are one hour forward from what is shown on the WorkOrder form for these fields?

I am pulling data directly from the SQL database for over-time and billing reports and this extra hour then makes the billable time chargable at the higher over-time rate. I want to understand why there is a difference, and how I can work around this error.

Your help is much appreciated... Michael
7 REPLIES
Ruth Porter
Acclaimed Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Hi Michael,

As far as I know, all dates in the database are in UCT whereas when shown via HPSD, they are displayed in local time setting.

So it looks to me as though it is probably due to daylight saving/summer/winter time.

Could you report from DB views as the generate views (see Tools>System>System Panel>Report settings) lets you say what time zone to use?

Hope this helps, Ruth
http://www.teamultra.net
Ben Snell
Super Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Hi

Ruth is correct - all data is stored in UTC on the database and the timezone offset for Daylight Saving is handled at the application server.

I don't belive that the reporting views cater for this either (happy to be proven wrong) - please be aware that the reporting views are not very efficient because of the number of tables that they join.

If you use SQL Server you can workout the current offset by usung the difference between getdate() and getutcdate(). Not sure if you can work out easily if a specific date is in the Daylight Savings range or not. You might have to build some form of control table to work out when the dates fall into one or another.

Regards
Ben
Ken Briscoe
Acclaimed Contributor.
Solution

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Hi Michael,
yes, times in the database are always stored in UTC. But the database Views use a fixed time offsett to convert these to whatever time zone you choose when you generate the views. (ie in Tools/System/System Panel/Report Settings via "(Re)generate database views for reporting" button. )
But the offsett used the applies to all records in the database regardless of whether daylight savings occurred or not. (Have a look at the database view definitions via Ent.Mgr for date fields and you'll see what I mean).
That's why we always regenerate the views just after daylight savings start and stop - so at least they are right for the current period. It's weak I reckon - HP should have some date function in the generated views to correctly show the time. Oh well.

As Ruth said - the client tool always shows the correct time because it converts UTC to your local time taking into account daylight savings or not.

Hope that helps. ...Ken.
My email is kenilian@bigpond.com.au
Michael Daly_2
Super Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Thanks everyone for your replies.

What I am still unclear about is that I have seen on some of the Views in the SQL Enterprise Manager that they use the CAST() function to correct the Date/Time from UTC to the local time, but surely it should be 'consistent' for all times? My problem is that I have the majority of Times are correct and some an Hour ahead. That's the part I don't know how they can be different.

Thanks again.

Michael Daly
Ken Briscoe
Acclaimed Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Yes - should be the same for all generated Views. Unless generation of some individual views failed (you can check the creation date) or you have created some of your own database views and they are not of course regenerated.
Of course if the DST settings were wrong at the time, the UTC time might be actually out by an hour...but I guess you are saying the views (or some of the date fields in them) have different offsets?
The other thing is that because the offset is fixed, any durations you calculate for periods crossing a DST change time will be out. (eg if Start time is 2 days before DST and End time 2 days after). As long as the durations are both inside DST or both outside DST, and you HPSD system was set properly for DST, you shouldn't see errors.
My email is kenilian@bigpond.com.au
Gerry Allardice
Outstanding Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Is not this the traditional end of daylight saving which was actually extended a week this year. I think you need to create a custom timezone for this year. Is it explained by the application server saying daylight saving has ended prematurely and hence only subtracting 10 hours when it should have subtracted 11. With one of the SP along the way you can build your own custom timezone where you nominate the end date for daylight saving. Have a look under the doc\itsm008878 directory on the Service Pack for information how to do it.
Michael Daly_2
Super Contributor.

Re: SD 4.5(SP-25) - Actual Start & Finish time errors.

Thanks All,

I found that the views have a CAST() function using a 11/24 in this function. The Views are in fact wrong and the Client must make some adjustment for local DST offset, which now that we are out of DST time this CAST function should only use a 10/24 offset (because Melbourne, Australia is 10 ahead of GMT without Daylight Saving Time(DST)). All the Enterprise Manager (EM) Views are correct before the end of March, but after the 29 March 2008 all the Start/Finish times are forward by one hour.

Thus within my MS Access program I have created a function which calculates this offset to the UTC time (either 10 - for EST or 11 - for DST for time in Melbourne, Australia), and I then use that in the formula to calculate local times based on the UTC times. Thus I have recreated what functionality must be coded into the SD Client software when it displays Dates on a form. At least the times are correct, and thus my Over-Time calculations for Billing reports are now accurate.

Thank you everyone. Much appreciated. ...Michael