Date and time expressions

A date/time expression resolves to a value of type DATE, INT, INTERVAL, TIME, or TIMESTAMP, depending on the context. A date/time expression must include one or more of the following components:

The next table summarizes the types of arithmetic operations that are valid in a date/time expression. Note that you cannot use a TIME variable in an arithmetic expression. For an explanation of the format symbols used, see "Date, time, and timestamp format specifiers."

Table 1. Arithmetic operations in a date/time expression
Type of Operand 1 Operator Type of Operand 2 Type of Result Comments
DATE - DATE INT INT is a number of days.
DATE +/- NUMBER DATE NUMBER can be a variable, a literal, or a numeric expression, and is assumed to be a number of days.
NUMBER + DATE DATE NUMBER can be a variable, a literal, or a numeric expression, and is assumed to be a number of days.
TIMESTAMP - TIMESTAMP INTERVAL See "Using INTERVAL variables" below.
DATE - TIMESTAMP INTERVAL See "Using INTERVAL variables" below.
TIMESTAMP - DATE INTERVAL See "Using INTERVAL variables" below.
TIMESTAMP +/- INTERVAL TIMESTAMP  
INTERVAL + TIMESTAMP TIMESTAMP  
DATE +/- INTERVAL TIMESTAMP  
INTERVAL + DATE TIMESTAMP  
INTERVAL +/- INTERVAL INTERVAL INTERVALs must be of the same span, that is, either month- or second-span INTERVALs.
INTERVAL *// NUMBER INTERVAL For example, you can divide an INTERVAL of 3 years 3 months by 2. The result is truncated to 1 year 7 months.

Using INTERVAL variables

The tricky concept here is the INTERVAL type. The INTERVAL comes into play only when expressions involve a TIMESTAMP or an INTERVAL as one of the operands. There are actually two different versions of the INTERVAL type. One stores month-span intervals (intervals greater than one month), and the other stores second-span intervals (intervals less than a month). You must provide a format pattern when you declare an INTERVAL variable, and that format must tell EGL whether the INTERVAL is a month-span or a second-span.

The following are examples of legal formats for a month-span INTERVAL:
diff01 INTERVAL("yyyy");
diff02 INTERVAL("MM");
diff03 INTERVAL("yyyyMM");
The following are examples of legal formats for a second-span INTERVAL:
diff04 INTERVAL ("ddhhmmssffff");
diff05 INTERVAL ("hhmmss");
diff06 INTERVAL ("ddhhmm");
diff07 INTERVAL ("ss");
You cannot combine the two formats, so the following formats are illegal:
diff08 INTERVAL ("yyyyMMdd");    // illegal
diff09 INTERVAL ("MMddhhmmss");  // illegal
Here are two examples of the INTERVAL in action:
//month-span interval 
diff01 INTERVAL ("yyyyMM");
t1 TIMESTAMP ("yyyyMM") = "200504";
t2 TIMESTAMP ("yyyyMM") = "200604";

diff01 = t2 - t1;
// diff01 is "+000100" (1 year)

// second-span interval
diff02 INTERVAL ("ddhhmmss");
t3 TIMESTAMP ("yyyyMMddhhmm") = "200604201027";
t4 TIMESTAMP ("yyyyMMddhhmm") = "200604201555";

diff02 = t4 - t3;
// diff02 is "+00052800" (5 hours, 28 mins)

Compatibility considerations

Table 2. Compatibility considerations
Platform Issue
JavaScript generation The following types are supported: ANY, BIGINT, BIN (but only in the absence of decimal places), Boolean, DataItem, DATE, DECIMAL, Delegate, Dictionary, FLOAT, INT, NUM, NUMBER, SMALLFLOAT, SMALLINT, STRING (but only in the absence of a size limit) , TIME, TIMESTAMP, NUM, MONEY, Service parts, Interface parts, External types (stereotype JavaScript), arrays of supported types, and non-structured Basic, Exception, and SQL Record parts.

The following types are not supported: ArrayDictionary, BIN (with decimal places), BLOB, CHAR, CLOB, DBCHAR, HEX, INTERVAL, MBCHAR, NUMC, STRING (with a size limit), PACF, UNICODE, and structured Record parts.


Feedback