2.7.3.3 Date and Time Data

TimeDateWhile the various string formats used for displaying date and time information are useful in conveying information to users, a mathematical basis for these values is needed to support plotting and analysis involving date-time data.

Origin's default date-time system is a modification of the Astronomical Julian Date system to store dates and time. In this system, time zero is 12 noon on January 1, 4713 BCE. The integer part of the number represents the number of days since time zero and the fractional part is the fraction of a 24 hour day. Origin offsets this value by subtracting 12 hours (0.50 days) to put day transitions at midnight, rather than noon. It should also be noted that Origin supports two alternate date-time systems.

The next few examples are dedicated to dealing with date and time data in your LabTalk scripts.

You can opt not to display missing values in your date-time data by setting the value of system variable @JM.

1 Dates and Times
2 Formatting for Output
- 2.1 Available Formats
- 2.2 Custom Formats
  - 2.2.1 Dnn notation
  - 2.2.2 Wks.Col.SetFormat object method

Dates and Times

As an example, say you have Date data in Column 1 of your active sheet and Time data in Column 2. You would like to store the combined date-time as a single column.

/* Since both date and time have a mathematical basis, 
   they can be added: */
Col(3) = Col(1) + Col(2);

// By default, the new column will display as a number of days ...
/* Use format and subformat methods to set 
   the date/time display of your choice: */

// Format #4 is the date format
wks.col3.format = 4; 
// Subformat #11 is MM/dd/yyyy hh:mm:ss              
wks.col3.subformat = 11;

The column number above was hard-coded into the format statement; if instead you had the column number as a variable named cn, you could replace the number 3 with $(cn) as in wks.col$(cn).format = 4. For other format and subformat options, see LabTalk Language Reference: Object Reference: Wks.col (object).

If our date and time column are just text with a MM/dd/yyyy format in Column 1 and hh:mm:ss format in Column 2, the same operation is possible with a few more lines of code:

// Get the number of rows to loop over.
int nn = wks.col1.nrows;                 

loop(ii,1,nn){                           
  string dd$ = Col(1)[ii]$;
  string tt$ = Col(2)[ii]$;
  // Store the combined date-time string just as text
  Col(3)[ii]$ = dd$ + " " + tt$;  
  // Date function converts the date-time string to a numeric date value
  Col(4)[ii] = date(%(dd$) %(tt$));  
};
// Now we can convert column 4 to a true Date column
wks.col4.format = 4; // Convert to a Date column
wks.col4.subformat = 11; // Display as M/d/yyyy hh:mm:ss

Here, an intermediate column has been formed to hold the combined date-time as a string, with the resulting date-time (numeric) value stored in a fourth column. While they appear to be the same text, column C is literally just text and column D is a true Date.

Given this mathematical system, you can calculate the difference between two Date values which will result in a Time value (the number of days, hours and minutes between the two dates) and you can add a Time value to a Date value to calculate a new Date value. You can also add Time data to Time data and get valid Time data, but you cannot add Date data to Date data.

Formatting for Output

Available Formats

Use the Date format notationD notationD notation to convert a numeric date value into a date-time string using one of Origin's built-in Date subformats:

type "$(@D, D10)";

returns the current date and time (stored in the system variable @D) as a readable string:

7/20/2009 10:30:48

The D10 option corresponds to the MM/dd/yyyy hh:mm:ss format. Many other output formats are available by changing the number after the D character, which is the index entry (from 0) in the Date Format drop down list of the Worksheet Column Format dialog box, in the line of script above. The first entry (index = 0) is the Windows Short Date format, while the second is the Windows Long Date format.

Note : The D must be uppercase. When setting a worksheet subformat as in wks.col3.subformat = #, these values are indexed from 1.

For instance

type "$(date(7/20/2009), D1)";

produces, using U.S. Regional settings,

Monday, July 20, 2009

Similarly, for time values alone, there is an analagous T notationTime format notation, to format output:

type "$(time(12:04:14), T5)";   // ANS: 12:04 PM

Formatting dates and times in this way uses one specific form of the more general $() Substitution notation.

Custom Formats

There are three custom date and time formats - two of which are script editable properties and one which is editable in the Column Properties dialog or using a worksheet column object method.

system.date.customformatn$
wks.col.SetFormat object method.

Both methods use date-time specifiers, such as yyyy'.'MM'.'dd, to designate the custom format. Please observe that:

The text portions (non-space delimiters) of the date-time specifier can be changed as required, but must be surrounded by single quotes.
The specifier tokens themselves (i.e., yyyy, HH, etc.) are case sensitive and need to be used exactly as shown— all possible specifier tokens can be found in the Reference Tables: Date and Time Format Specifiers.
The first two formats store their descriptions in local file storage and as such may appear different in other login accounts. The third format stores its description in the column itself.

Dnn notation

Origin has reserved D19 to D21 (subformats 20 to 22, since the integer after D starts its count from 0) for these custom date displays. The options D19 and D20 are controlled by system variables system.date.customformat1$ and system.date.customformat2$, respectively. To use this option for output, follow the example below:

 
system.date.customformat1$ = MMM dd hh'.'mm tt;
type "$(Date(7/25/09 14:47:21),D19)";      // Output: Jul 25 02.47 PM 

system.date.customformat2$ = yy','MM','dd H'.'mm'.'ss'.'####;
type "$(Date(7/27/09 8:22:37.75234),D20)"; // Output: 09,07,27 8.22.37.7523

Wks.Col.SetFormat object method

To specify a custom date display for a date column which is stored in the worksheet column, use the Wks.Col.SetFormat object method. When entering the custom date format specifier, be sure to surround any non-date characters with single quotes. Also note that this object method works on columns of the active worksheet only.

In the following example, column 4 of the active worksheet is set to display a custom date/time format:

// wks.format=4 (date), wks.subformat=22 (custom)
wks.col4.SetFormat(4, 22, yyyy'-'MM'-'dd HH':'mm':'ss'.'###);
doc -uw; // Refresh the worksheet to show the change