Statit Date and Time Data Overview

Products: Statit e-QC, Statit e-Server, Statit Custom QC

We receive many questions about how Statit handles date and time data. Our hope is that with a little explanation here, you will have a better idea of how Statit deals with this data and how to achieve what you need.

Storage

The best place to start is with an explanation of how Statit is storing date and time data. With this basis, you will be able to understand better how everything else works.

Dates are stored as Julian. Julian dates are the number of days from a given starting point, which in Statit is 31 December 1899. The value 1 = 1 January 1900, 2 = 2 January 1900, and so on. Dates prior to 1 January 1900 may be specified as zero and negative values: 0 = 31 December 1899, -1 = 30 December 1899, etc. The minimum date which may be stored is 1 January 1600 and the maximum date is 31 December 2199.

Time values are stored as the number of seconds since midnight. 1 = 00:00:01, 2 = 00:00:02, etc. The maximum number of seconds in a day is 60*60*24 = 86400. In Statit, 0 = 00:00:00, 86400 = 24:00:00, but these both equal Midnight, where Midnight is that time half way between 23:59:59 and 00:00:01.

Can you guess how datetime data are stored? As in date data, an integer value specifies the date. The time is stored as the decimal portion of a double precision value. That is, 1 = 1 January 1900, and 1.5 = 1 January 1900 12:00. The .5 is half of 24 hours, thus the time is 12:00. Since double precision provides 16 digits of precision, and 5 digits are required for the date portion, there are 11 digits of precision for the time portion.

What about 24 December 1998 24:00:00? This is equal to 25 December 1998 00:00:00, because adding 24 hours to the 24th brings the date to the 25th!

Warning About Character Data

In case you have not seen it, date data may be read into Statit as a character string. In this case, the data may "look" like it is date or time data, but Statit works with it as character data. If you are experiencing problems, use the toolbar binoculars or the describe command to be sure you are working with true, numeric date or time data.

Combining Date And Time Data

Now that you know how date, time, and datetime data are stored, how would you combine a date variable and a time variable into a datetime variable? Since the time portion of the datetime data is the decimal portion of the value, you need to convert the number of seconds since midnight into a decimal, and add it to the date value:

let MyDateTime = MyDate + (MyTime/86400)
printformat MyDateTime low to high = "%20j"

Splitting Datetime Data

The reverse of combining date and time data is to split a datetime variable into a date and a time variable.

To get time portion of datetime data, convert the decimal portion of the value into the number of seconds since midnight:

To get date portion of datetime data:

Using Date Constants

In Statit, you may specify a date constant, that is, a single date value, with the "0c" value format. In value lists and expressions, this value format returned the Julian value of the date specified. For example, to create a variable containing the days of the month of June 1995, you would:

You may also specify the date value with dates enclosed in apostrophes. For example:

Using the apostrophe syntax, you may specify datetime values as follows:

You may use these date constants in expressions. For example, to print a selected range of dates, use date constants in the Boolean expression of the /SELECT option:

Remember, the Boolean comparison here is comparing Julian values.

There is a limitation to using apostrophe syntax or the "0c" constant: You cannot enter dates prior to 1 Jan 1600 or after 31 Dec 2199.

Using Aliases

Looking through the functions Statit provides for date and time manipulation, it is somewhat confusing as to how to get at certain values. For example, if you retrieved data and want to display the data for today, how would you do that comparison? Unless you are familiar with all of the functions, it could take some time to get at the Julian value of today.

This is one of the areas in which aliases may be used. Statit provides the ALIAS command, which defines words which are expanded to something else in the command line. You may define an alias for "today", so that in an expression you may use the word "today" in your comparisons. For example, in statit.ini you place the command:

Checking out the Statit Reference Manual, you see that day(4) returns the Julian value of today's date. When you run Statit, this alias is defined as Statit starts running and processes statit.ini. When you enter the command:

Statit actually converts aliases and processes the command:

The select expression takes the date portion of the datetime variable (see above) and when it is equal to today (in Julian), it returns true and all variables are printed.

Other aliases you may find useful:

Warning: Once these aliases are defined, you must use caution in your variable names and whenever you enter these words in Statit commands. Statit will translate these words as defined in these aliases before processing the command.

Statit e-QC Common Issues

Common date issues in Statit e-QC include defaults, periods and creating the dates in SQL formats.

When using a Date Parameter, to set a default date to be today, set the default as day(4); for yesterday set the default to day(4) - 1. The date displayed in the Date control will be the default.

This default will not produce the daily scheduled report you might expect. When a scheduled report that uses the date control is saved it is saved with the date as entered at that time. Each time the scheduled report is run it will use the date that was saved.

Instead, you might set the parameter to a list type with options such as:

Then, in the macro, you can do something like:

Now that we have the begin date we need the date ranges to feed to a database query.

This code needs some explanation.

setintrvl(3,day(4),0) returns the beginning of the current month. This value is then embedded in another setintrvl function that returns the first of the previous month. setintrvl(3,day(4),0) - 1 finds the first of the month and subtract 1 day from it, making it the last day of last month.

SQL takes dates in the form {ts ' 2006-06-07 10:15:13 '}, so we create a begindate and enddate string that we can use with an @v syntax in the Beginsql block.

yearpart() returns the year part of the date, yearc of type 2 returns the 4-digit year as a string. monpart() returns the month part of the date, and putwith converts it to a string of exactly 2 digits ("%2-d").

Calling another macro through URL

A date type parameter does not pass between macros through a URL. You will either need to use a variation of the method above or pass the date to a text parameter in the called macro. In the called macro you can do something like:

Remember how the apostrophes convert a set of characters that look like a date to a date? At that point you can use some of the same functions discussed above to obtain the query parameters.

If you would like additional information, please contact our Support staff at statit.support@acs-inc.com.