Abstract:
A process data capture and reporting system captures process data values at sensors (S 1  . . . Sn). A client computer (C) appends absolute-value time stamps to the values to complete records, which are uploaded to a server (S). The server (S) writes the records to a persistent database (DB). At a later stage, the server (S) retrieves selected records, and performs a very fast conversion of the time stamps to a calendar format with “granular” values for units such as day, month, or minute. The conversion is performed in an optimised manner with use of look-up tables in memory. This minimises processor overhead, and is thus very advantageous where data volumes are high and/or near real time reporting is required.

Description:
FIELD OF THE INVENTION  
         [0001]    The invention relates to processing of time and date data in data processing equipment.  
         PRIOR ART DISCUSSION  
         [0002]    At present there are two time/date (henceforth “time”) representation approaches in common use. One approach involves generating a data word representing time as an “absolute” value which is a count of intervals since a point in time. In a second, “granular”, approach time is represented as a data word for each field for progressively smaller granularity: year, month, . . . milliseconds.  
           [0003]    Examples of these two approaches are to be found in Windows™ operating systems, in which “FILETIME” uses the first approach and “SYSTEMTIME” uses the second approach.  
           [0004]    The FILETIME representation of time is a signed 64-bit counter of 100 nanosecond intervals, using January 1 1601CE as the epoch. The FILETIME permits a very wide range of dates, 30000 years before and after the epoch, accurate to within +/− 0.00000005 seconds. The FILTIME structure is represented (in the C programming language) as:  
           [0005]    typedef struct _FILETIME { 
           [0006]    DWORD dwLowDateTime;  
           [0007]    DWORD dwHighDateTime;  
           [0008]    } FILETIME, ★PFILETTME, ★LPFILETIME;  
           [0009]    The SYSTEMTIME representation of time is a C programming language structure (see below) containing eight fields of granular values corresponding to the year, month, day, hour, minute, second, millisecond and day of the week. Each of the fields is an unsigned 16-bit integer. The SYSTEMTIME representation permits a very wide range of dates, from the year 0 to 65535CE inclusive, with an accuracy of +/− 0.0005 seconds. The structure is as follows.  
           [0010]    typedef struct _SYSTEMTIME { 
           [0011]    WORD wYear;  
           [0012]    WORD wMonth;  
           [0013]    WORD wDayOfWeek;  
           [0014]    WORD wDay;  
           [0015]    WORD wHour;  
           [0016]    WORD wMinute;  
           [0017]    WORD wSecond;  
           [0018]    WORD wMilliseconds;  
           [0019]    } SYSTEMTIME, ★PSYSTEMTIME, ★LPSYSTEMTIME;  
           [0020]    Because of its greater conciseness and higher accuracy relative to the SYSTEMTIME, the FILETIME is frequently used as a persisted time format in software systems (i.e. the format in which times are stored to secondary storage). For similar reasons, it is also widely used for the collection and exchange of scientific and engineering data. However, for processing and manipulation of times, including presentation to a human user, the SYSTEMTIME format is more commonly used because it has a direct mapping to real-world time.  
           [0021]    It is therefore advantageous to have a mechanism available to the developer for conversion between the two different representations. Such a mechanism is provided on Windows™ operating systems as a standard part of the Win32™ API through the FileTimeToSystemTime( ) and SystemTimeToFileTime( ) functions. While this mechanism is effective, it suffers from the problem of being processor-intensive. This can be particularly problematic for data capture systems which capture large quantities of data in real time from environments such as production processes. In such an environment there are secondary storage aspects with high data volumes in real-time, and also processing and human output aspects.  
           [0022]    The invention addresses this problem.  
         SUMMARY OF THE INVENTION  
         [0023]    According to the invention, there is provided a data processing method carried out by a data processor connected to a memory, the method comprising the steps of:— 
           [0024]    (a) receiving an input time value represented in an absolute format,  
           [0025]    (b) processing the input value to determine an index for a look-up table comprising entries of date or time period values,  
           [0026]    (c) retrieving from the look-up table a date or time period value corresponding to the index, and  
           [0027]    (d) determining from said period value an output value being a granular representation of the input value.  
           [0028]    In one embodiment, the step (b) comprises generating a value for the number of periods elapsed since a start time, and using this value as the index.  
           [0029]    In another embodiment, said index value is determined by:  
           [0030]    (b1) determining an initial value for the number of low-granularity periods elapsed since the start time, and  
           [0031]    (b2) dividing said initial value to determine a number of higher-granularity periods elapsed since the start time.  
           [0032]    In a further embodiment, the higher-granularity periods are days.  
           [0033]    In one embodiment, the initial value represents the numbers of milliseconds elapsed since the start time.  
           [0034]    In another embodiment, the look-up table entries span an optimised most-likely subset of the complete range of the absolute format, and the method is terminated before processing the input value if the input value falls outside of the optimised range.  
           [0035]    In a further embodiment, the method normalises the input value to the start time of the optimised range before performing the step (b).  
           [0036]    In another embodiment, the period value retrieved from the look-up table is a calendar date.  
           [0037]    In a further embodiment, step (d) is repeated for each of a succession of decreasing granular values until a granular representation requirement is met.  
           [0038]    In one embodiment, the potential successive granular values are year, month, day, hour, minutes, seconds, and milliseconds.  
           [0039]    In another embodiment, the invention comprises the further steps of capturing process data, writing a record containing the process data and a time stamp in a universal format to a database, subsequently retrieving the record, and performing steps (a) to (d) to provide a granular-representation output value time stamp in which the universal format time stamp is the input value for the steps (a) to (d).  
           [0040]    In one embodiment, the method comprises the further steps of processing the output value time stamp to generate a process report.  
           [0041]    In one embodiment, the process data is captured from a manufacturing production line.  
           [0042]    In another aspect, the invention provides a data processing system comprising means for performing a method as defined above.  
         DETAILED DESCRIPTION OF THE INVENTION 
     
    
    
     BRIEF DESCRIPTION OF THE DRAWINGS  
       [0043]    The invention will be more clearly understood from the following description of some embodiments thereof, given by way of example only, with reference to the accompanying drawings in which:— 
         [0044]    [0044]FIG. 1 is an illustration of a data capture and reporting system of the invention; and  
         [0045]    FIGS.  2 ( a ) to  2 ( c ) are together a flow diagram illustrating a time and date conversion process implemented by the system. 
     
    
     DESCRIPTION OF THE EMBODIMENTS  
       [0046]    Referring to FIG. 1 a process data capture system comprises n sensors S 1  . . . Sn mounted in a pharmaceutical production line. The sensors measure parameters such as vessel pressures and temperatures, pump speeds, and valve on/off status values. This process data is captured by a computer C which appends a FILETIME universal-format time stamp to each process data value.  
         [0047]    Operating as a client, the computer C uploads records, each containing a process data value and a FILETIME time stamp to a server S. The server S writes these records, and records from other client computers, to a persistent database DB. Because an absolute-format time stamp is used the data capture is very concise, which is advantageous for high-frequency data capture scenarios. It is also important for accuracy in highly regulated manufacturing environments, such as the pharmaceutical industry.  
         [0048]    At a later time, the server S retrieves selected records and generates output reports for viewing by operators. In doing so it converts the time stamp to a SYSTEMTIME “granular” representation, which is easily understood by humans. This format is used for routing reports to workstations W/S on a local area network LAN. This format is also used for data processing in general by the server S.  
         [0049]    The process for conversion between FILETIME and SYSTEMTIME uses two optimisation features, as follows:  
         [0050]    (a) It determines the date range most likely to be encountered (i.e. dates in and around the year 2002CE). It is possible to use a lookup table to determine the date component of the time, without intensive processing operations. Also, the table may be of a relatively small size. This optimisation technique is referred to as table-based date determination. The date range from 1970 to 2038 inclusive is encoded in the look up table.  
         [0051]    (b) For converting from FILETIME to SYSTEMTIME, the process is frequently not interested in all fields of the latter, and the granularity of the conversion is determined by the application based on the context in which the data is being used. For example, for sorting purposes, it may only be necessary to determine the year and month to which a FILETIME belongs. This optimisation technique is referred to as context-dependent partial conversion.  
         [0052]    Table-based date determination has an important benefit because dates can be represented by a 32 bit value rather than 64 bits, and thus operations on these 32 bits can be executed very efficiently.  
         [0053]    The following sets out the conversion process in detail.  
         [0054]    Steps  1 - 3 . The 64-bit FILETIME value (V 1 ) is compared with the start and end of the optimised date range for which a lookup table has been calculated. If it is found to be outside the optimised range (i.e. it has a value which places it before Jan. 1, 1970 or after Jan. 1, 2038) the prior art Win32™ function is invoked to perform the conversion and this procedure exits.  
         [0055]    Step  4 . By subtracting the 64-bit FILETIME value for Jan. 1, 1970, the 64-bit FILETIME value V 1  is normalised with respect to the optimised range (V 2 ).  
         [0056]    Step  5 . The resulting normalised 64-bit value V 2  is divided by 10000. The resulting 64-bit quotient (V 3 ) represents the number of milliseconds since Jan. 1, 1970. The remainder of this division is discarded, as it cannot be represented in a SYSTEMTIME structure (it represents the time component of the FILETIME less than 1 millisecond).  
         [0057]    Step  6 . The 64-bit count of milliseconds since Jan. 1, 1970 (V 3 ) is divided by 86400000. The resulting 32-bit quotient (V 4 ) represents the number of days since Jan. 1, 1970. The 32-bit remainder of this division (V 5 ) represents the hours/minutes/seconds/milliseconds component of the original FILETIME.  
         [0058]    Step  7 . The 32-bit count of days since Jan. 1, 1970 (V 4 ) acts as an index to a pre-calculated in-memory lookup array of 16-bit values. Each entry in the array represents the date of the corresponding array index. Thus from the lookup array a single 16-bit value is obtained (V 6 ) corresponding to the date of the original FILETIME.  
         [0059]    Step  8 . The normalised year is extracted from bits 9-15 of the 16-bit value V 6 . 1970 is added to this value to obtain the actual Gregorian year of the original FILETIME, and the resulting value is stored in the wYear field of the SYSTEMTIME structure.  
         [0060]    Steps  9  &amp;  10 . The conversion requirements are examined to determine if the application requires conversion accuracy greater than year. If not, the procedure is complete and exits.  
         [0061]    Step  11 . The month is extracted from bits  5 - 8  of the 16-bit value V 6 , and the resulting value is stored in the wMonth field of the SYSTEMTIME structure.  
         [0062]    Steps  12 ,  10 . The conversion requirements are examined to determine if the application requires conversion accuracy greater than month. If not, the procedure is complete and exits.  
         [0063]    Step  13 . The day is extracted from bits  0 - 4  of the 16-bit value V 6 , and the resulting value is stored in the wDay field of the SYSTEMTIME structure.  
         [0064]    Steps.  14 ,  10 . The conversion requirements are examined to determine if the application requires conversion accuracy greater than day. If not, the procedure is complete and exits.  
         [0065]    Step  15 . The 32-bit time component in milliseconds (V 5 ) is divided by 3600000. The resulting 32-bit quotient represents the time in hours and this value is stored in the wHour field of the SYSTEMTIME structure. The 32-bit remainder of this division (V 7 ) represents the minutes/seconds/milliseconds component of the original FILETIME, expressed in milliseconds.  
         [0066]    Steps  16 ,  10 . The conversion requirements are examined to determine if the application requires conversion accuracy greater than hour. If not, the procedure is complete and exits.  
         [0067]    Step  17 . The 32-bit minutes/seconds/milliseconds component (V 7 ) is divided by 60000. The resulting 32-bit quotient represents the time in minutes and this value is stored in the wMinute field of the SYSTEMTIME structure. The 32-bit remainder of this division (V 8 ) represents the seconds/milliseconds component of the original FILETIME, expressed in milliseconds.  
         [0068]    Steps  18 ,  10 . The conversion requirements are examined to determine if the application requires conversion accuracy greater than minute. If not, the procedure is complete and exits.  
         [0069]    Step  19 . The 32-bit seconds/milliseconds component V 8  is divided by 1000. The resulting 32-bit quotient represents the time in seconds and this value is stored in the wSecond field of the SYSTEMTIME structure. The 32-bit remainder of this division (V 9 ) represents the milliseconds component of the original FILETIME and is stored in the wMilliseconds field of the SYSTEMTIME structure.  
         [0070]    Step  20 . The 32-bit count of days since Jan. 1, 1970 (V 4 ) has 4 added to it, and the result is divided by 7. The remainder resulting from this division represents the day-of-the-week of the original FILETIME (where 0=Sun, 1=Mon, etc.) and is stored in the wDayOfWeek field of the SYSTEMTIME structure.  
         [0071]    Tests  
         [0072]    Tests were performed over 1,000,000 iterations and the average of each run taken. Times are in nanoseconds.  
                                                                                                                                                                                         Test #1 converting all levels                Prior Art process:   1562 ns           Invention process:    610 ns           Time reduction:    61%           Speedup:   256%                Test #2 converting to minute level                Prior Art process:   1563 ns           Invention process:    453 ns           Time reduction:    71%           Speedup:   345%                Test #3 converting to hour level                Prior Art process:   1562 ns           Invention process:    375 ns           Time reduction:    76%           Speedup:   417%                Test #4 converting to day level                Prior Art process:   1562 ns           Invention process:    313 ns           Time reduction:    80%           Speedup:   499%                Test #5 converting to month level                Prior Art process:   1563 ns           Invention process:    312 ns           Time reduction:    80%           Speedup:   501%                Test #6 converting to year level                Prior Art process:   1562 ns           invention process:    297 ns           Time reduction:    81%           Speedup:   526%                      
 
         [0073]    It will be appreciated that the invention provides very effective and efficient conversion to the required format with minimum processor execution time. This is particularly advantageous where near real-time reporting is required and/or where the volume of process data is great. For example, a typical chemical production line generates many megabytes of process data per hour. While the invention has been described for use with a FILETIME input and a SYSTEMTIME output format any other similar types of format may be used.  
         [0074]    The invention is not limited to the embodiments described but may be varied in construction and detail.