Date processing in Informix Dynamic Server

Let's make a date

Jacques Roy
Published on October 13, 2005/Updated: December 22, 2005

A date is a complex piece of information. It represents a specific day of the year. You can group dates together by day of the week, month, quarter, and so on. The grouping allows you to compare results for specific periods from year to year.

Informix Dynamic Server (IDS) provides some facilities to process dates. This article reviews the current functions and provides additional functions that can be useful.

IDS date functions

IDS includes two "date" data types. They are DATE and DATETIME. DATE represents a day, and DATETIME represents a specific moment with a precision in the range of year to fraction of a second. IDS provides the following functions to manipulate these types:

• DATE(VARCHAR(10)) returning DATE
  This function takes a character string in the format specified by the environment variable DBDATE and returns a DATE type. The default format for the US English locale is "MDY4/".

• DATE(DATETIME) returning DATE
  This is the same function as above, but taking as input a DATETIME of any precision.

• DATE(INTEGER) returning DATE
  The INTEGER argument represents a number of days since December 31, 1899.

• DAY(DATE) returning INTEGER
  The DAY function returns the day of the month as an INTEGER.

• DAY(DATETIME)
  This is the same function as above, but taking as input a DATETIME of any precision.

• EXTEND(DATE, precision) returning DATETIME
  The EXTEND function adjusts the precision of the DATE argument and returns the appropriate DATETIME. Since this is a little vague, here is an example:
  EXTEND(DATE(1), YEAR TO SECOND)

• EXTEND(DATETIME, precision) returning DATETIME
  Same as above, but operating on a DATETIME instead of a DATE.

• MONTH(DATE) returning INTEGER
MONTH extracts the month number from the DATE specified as an argument.

• MONTH(DATETIME) returning INTEGER
  This function extracts the month from a DATETIME of any precision.

• WEEKDAY(DATE) returning INTEGER
  The WEEKDAY function returns an INTEGER that represents the day of the week for the specified DATE. It starts with zero representing Sunday, and goes on up to six representing Saturday.

• WEEKDAY(DATETIME) returning INTEGER
  Same as the previous function but operating on a DATETIME.

• YEAR(DATE) returning INTEGER
  This function extracts the year from the DATE specified as an argument.

• YEAR(DATETIME) returning INTEGER
  Same as the previous function but operating on a DATETIME.

• MDY(INTEGER, INTEGER, INTEGER) returning DATE
  This function creates a DATE based on the three INTEGER arguments. These arguments specify the month, day, and year, respectively. Note that the year is a four-digit integer.

• TO_CHAR(DATE, VARCHAR(??)) returning VARCHAR(??)
  This function takes a DATE and a format argument and returns a character string representing the date formatted as requested. The format string can include the following:

  • %A: weekday name
  • %B: month name
  • %d: day of the month as a decimal number
  • %Y: year as a 4-digit number
  • %R: time in 24-hour notation

• TO_CHAR(DATE, VARCHAR(??)) returning VARCHAR(??)
  Same as above.

• TO_DATE(VARCHAR(??), VARCHAR(??)) returning DATE
  This is the reverse operation from TO_CHAR using the same format string as the second argument.

In addition to the functions above, there are two environment variables that impact the processing of dates:

• DBDATE: Provides the end-user format of a date. It is described in the SQL reference manual (page 3-25).
• DBCENTURY: Defines how to expand the year when a date is entered as a two-digit number instead of four. The acceptable values are R, P, F, and C. They stand for Current, Previous, Future, and Closest ,respectively. The value R is the default if DBCENTURY is not set. DBCENTURY is described in the SQL Reference manual (page 3-22 for IDS 10.0).

Finally, IDS defines two built-in functions that return the current date value. CURRENT returns a DATETIME value, and TODAY returns today's date.

Using the date functions

The functions described above provide functionality for input, output, formatting, and information extraction. The first interesting usage I want to discuss is the input of dates based on character strings.

The function DATE() receives a character string as input, but processes it differently depending on the setting of DBDATE and DBCENTURY. Let's start with DBCENTURY.

The default value for DBCENTURY is R. This means that the century is determined by the century of the current date. The following example assumes the default US English locale:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;

(constant)

09/02/2092

1 row(s) retrieved.

If DBCENTURY is set to P, the inferred century will be whichever is closest to the current date. With this setting, the previous example becomes:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;

(constant)

09/02/1992

1 row(s) retrieved.

The other possible variation on date conversion is provided by the DBDATE environment variable. For the US English locale, it defaults to "MDY4/". This means that the elements of a date string are separated by "/" and their order is month, day, and year. Note that the year is expected to be a four-digit year, but can be completed according to the rules set by DBCENTURY. You can change the value of DBDATE to use an international date format. The DBDATE value would be "Y4MD-". In addition to affecting the input of dates as character strings, this value also affects how dates are converted back into character strings:

select order_date from orders WHERE order_num = 1001;

order_date

1998-05-20

1 row(s) retrieved.

For a more elaborate date display, you can use the TO_CHAR function and provide a format as described in the previous section:

select to_char(order_date, "%d %B %Y") from orders WHERE order_num = 1001;


(expression)  20 May 1998

1 row(s) retrieved.

You can use some of the provided functions to extract values such as the month and the day, and use those values when defining table fragmentation by expression. You can also use them for grouping in SQL statements. For example, if you want to find out how many orders you have per month, you can use the following statement:

SELECT YEAR(order_date) year, MONTH(order_date) month, COUNT(*) count
FROM orders
GROUP BY 1, 2
ORDER BY 1, 2;

This type of grouping can be useful in all sorts of reporting. You can do a lot more if you are willing to take advantage of some basic extensibility features of IDS.

IDS extensibility

IDS is the premier database for extending the capability of a database to fit your environment. Extensibility became available with IDS version 9.01 in 1997, and continues to be available and improved in IDS version 10. You can create new data types, new functions, even new aggregates. The functions and aggregates can be written in C, Java™, or SPL. If you want to find out more about what you can do with extensibility, please take a look at the references provided at the end of this article.

I usually write user-defined functions in C. For the purpose of this article, I wrote them in SPL. The advantage of SPL is that it is a well known language to IDS users. It is the same as writing stored procedures.

Functional index

IDS V9.x and above supports the concept of a functional index. This means that you can create an index on the result of a function. Then you can use that index to speed up the processing of the queries that include the function in their SQL statements.

The built-in functions were created before IDS added extensibility features. It happens that you cannot create an index on the result of a built-in function. To make it work, you can wrap the built-in function in an SPL function. For example, if you wanted to create an index on the month, you could create an SPL function such as:

CREATE FUNCTION udr_month(dt date)
RETURNING integer
WITH (NOT VARIANT)
RETURN MONTH(dt);
END FUNCTION;

With this wrapper function, you can create an index such as:

CREATE INDEX orders_month_ids ON orders(udr_month(order_date));

Then you can use an SQL statement such as the following that takes advantage of the index:

SELECT * FROM orders WHERE udr_month(order_date) = 6;

New date functions

You can extract more information from a date: the week of the year, the week of the month, and the quarter.

Let's start with the day of the year function. Having such a function allows you to report your activities by week, without having to write specific stored procedures for each report or write custom application code. You can build this function using the built-in functions included in IDS. This makes it surprisingly simple:

CREATE FUNCTION day_of_year(dt date)
RETURNS integer
WITH(NOT VARIANT)

RETURN(1 + dt - MDY(1, 1, YEAR(dt)) );

END FUNCTION;

The key to the implementation of this function is to know that a date is actually an integer that represents the number of days since December 31, 1899. This means that if we have the date for January 1st, it becomes a simple subtraction.

You can use the function in an EXECUTE FUNCTION statement, setting a value in a function or stored procedure, or use it in an SQL statement.

SELECT order_date, day_of_year(order_date) d_o_y FROM orders WHERE order_num = 1001;

order_date       d_o_y

05/20/1998         140

1 row(s) retrieved.

The week of the year function is slightly trickier. It is a similar calculation except that we need to divide by seven days per week:

CREATE FUNCTION week_of_year(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE day1 date;
DEFINE nbdays int;

LET day1 = MDY(1, 1, YEAR(dt));
LET nbdays = dt - day1;

RETURN 1 + (nbdays + WEEKDAY(day1)) / 7;

END FUNCTION;

The key to this function is to understand the offset provided by the WEEKDAY built-in function. The WEEKDAY function returns zero for Sunday up to six for Saturday. If January 1st is a Sunday, we know that the 8th is then the following Sunday, week 2. If January 1st starts any other day, it means that the 1st week is shorter. The WEEKDAY built-in function gives us that offset that allows us to figure out the week of the year.

The week_of_year() function has a problem with the last week of one year and the first week of the next year. For example, December 31st, 2004 was a Friday and January 1st, 2005 was Saturday. The week_of_year function provides the following:

EXECUTE FUNCTION week_of_year(date("12/31/2004") );

(expression)

          53

1 row(s) retrieved.


EXECUTE FUNCTION week_of_year(date("1/1/2005"));

(expression)

           1

1 row(s) retrieved.

This problem has been addressed in the ISO 8601 standard. Jonathan Leffler of IBM has written stored procedures that implements the standard. His stored procedured can be found on the International Informix Users Group Web site at //www.iiug.org. Here is the week of the year implementation converted to a user-defined function:

CREATE FUNCTION day_one_week_one(yyyy INTEGER)
  RETURNING DATE
  WITH(NOT VARIANT)
  DEFINE jan1 DATE;
  LET jan1 = MDY(1, 1, yyyy);
  RETURN jan1 + MOD(11 - WEEKDAY(jan1), 7) - 3;
END FUNCTION;

CREATE FUNCTION iso8601_weeknum(dateval DATE DEFAULT TODAY)
RETURNING CHAR(8)
WITH(NOT VARIANT)
    DEFINE rv CHAR(8);
    DEFINE yyyy CHAR(4);
    DEFINE ww CHAR(2);
    DEFINE d1w1 DATE;
    DEFINE tv DATE;
    DEFINE wn INTEGER;
    DEFINE yn INTEGER;
    -- Calculate year and week number.
    LET yn = YEAR(dateval);
    LET d1w1 = day_one_week_one(yn);
    IF dateval < d1w1 THEN
        -- Date is in early January and is in last week of prior year
        LET yn = yn - 1;
        LET d1w1 = day_one_week_one(yn);
    ELSE
        LET tv = day_one_week_one(yn + 1);
        IF dateval >= tv THEN
            -- Date is in late December and is in the first week of next year
            LET yn = yn + 1;
            LET d1w1 = tv;
        END IF;
    END IF;
    LET wn = TRUNC((dateval - d1w1) / 7) + 1;
    -- Calculation complete: yn is year number and wn is week number.
    -- Format result.
    LET yyyy = yn;
    IF wn < 10 THEN
        LET ww = "0" || wn;
    ELSE
        LET ww = wn;
    END IF
    LET rv = yyyy || "-W" || ww;
    RETURN rv;
END FUNCTION;

Jonathan also provides a standard-compliant procedure to calculate the day of the week. Here is the procedure converted to a user-defined function:

CREATE FUNCTION iso8601_weekday(dateval DATE DEFAULT TODAY)
RETURNING CHAR(10)
WITH(NOT VARIANT)
  DEFINE rv CHAR(10);
  DEFINE dw CHAR(4);
  LET dw = WEEKDAY(dateval);
  IF dw = 0 THEN
    LET dw = 7;
  END IF;
  RETURN iso8601_weeknum(dateval) || "-" || dw;
END FUNCTION;

Let's coninue our discussion on date manipulation. We'll ignore the ISO 8601 standard to keep the functions simple. As you can see above, you can easily adapt the functions to be compliant with the standard.

If you want to calculate the week of the month, use the same function but instead of using January 1st as the starting date, use the first day of the month coming from the date passed as argument:

CREATE FUNCTION week_of_month(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE day1 date;
DEFINE nbdays int;

LET day1 = MDY(MONTH(dt), 1, YEAR(dt));
LET nbdays = dt - day1;

RETURN 1 + (nbdays + WEEKDAY(day1)) / 7;

END FUNCTION;

The quarter() functions

Some database products provide a quarter() function. It usually returns a number between one and four. The problem with providing a quarter() function is that it assumes a specific calendar: the standard calendar year.

Many companies want to calculate the quarter based on their business year that is different from the calendar year. There are even some organizations that must calculate the quarter differently based on what needs to be done. For example, some school districts have to calculate a calendar quarter, a school year quarter, and a business quarter.

Let's start with a simple implementation of a calendar year quarter:

CREATE FUNCTION quarter(dt date)
RETURNS integer
WITH(NOT VARIANT)

RETURN (YEAR(dt) * 100) + 1 + (MONTH(dt) - 1) / 3;

END FUNCTION;

In this implementation, I include the year as part of the quarter. For example, the third quarter of 2005 is represented by 200503. This simplifies the processing when an SQL statement spans more than one year. You could decide to create a different implementation, such as returning a character string instead of an integer. You have to decide based on your requirements.

As mentioned earlier, you may want to calculate a quarter based on a starting date that is not January 1st. This adds some complication where the calendar year may be different from the quarter year. Take a corporation that starts its fiscal year on September 1st. This means that September 1st, 2005, is really the start of the first quarter 2006, December 1st is the start of the second quarter, and so on.

The following code shows the implementation for a year starting on September 1st. It would be very easy to adapt this code for any starting date:

CREATE FUNCTION bizquarter(dt date)
RETURNS integer
WITH(NOT VARIANT)

DEFINE yr int;
DEFINE mm int;

LET yr = YEAR(dt);
LET mm = MONTH(dt) + 4; -- sept. to jan. is 4 months
IF mm > 12 THEN
  LET yr = yr + 1;
  LET mm = mm - 12;
END IF

RETURN (yr * 100) + 1 + (mm - 1) / 3;

END FUNCTION;

The additional processing in this function compared to the quarter() function is that it moves the current month forward a number of months in order to do the quarter calculation that matches the business year.

Using the new functions

Now that you have these functions available, you can use them in SQL statements just like if they were built into IDS. For example:

SELECT quarter(order_date) quarter, count(*) count
FROM orders
GROUP BY 1
ORDER BY 1;

    quarter            count

     199802               16
     199803                7

2 row(s) retrieved.

And you can create indexes on the functions:

CREATE INDEX orders_week_ids
ON orders(week_of_year(order_date));

This gives you the flexibility to have the database return the information you are looking for. IDS extensibility can be useful in many other areas. See the resource section for other articles on the subject.

Conclusion

It is easy to extend the capabilities of IDS to provide better date manipulation. The result is less code to write, and potentially less SQL statements to execute which leads to better performance. A database is not a commodity. It is a strategic tool that can give you a business advantage.

Using the date manipulation techniques in this article, you can adapt IDS to fit your environment. If the date functions provided here do not exactly fit your environment, you can easily modify them. The flexibility provided by IDS means that the IDS capabilities should be considered as early as the design phase of an application. The result could be greater performance and scalability, and simpler implementation.

Downloadable resources

• PDF of this content

Related topics

• ISO 8601 functions download: datespl_jl
• Generating XML from IDS 9.x (developerWorks, February 2003): Use functions to generate XML from Informix Dynamic Server.
• Event-driven fine-grained auditing with Informix Dynamic Server (developerWorks, October 2004): Perform detailed auditing, and generate events based on these auditing records.
• Using GUIDs with IDS 9.x (developerWorks, January 2004): Implement a new type that represents a globally unique identifier. Learn how you can generate these GUIDs from a user-defined routine.