Date Processing in Informix Dynamic Server

IBM Informix Dynamic Server has some date and time-type functions. You can use these functions to better process and analyze business data. You can also add new functions to simplify some date-based business problem solutions.

Introduction

Date is a complex information. It indicates a specific day of the year. You can group dates by week, month, or quarter. This grouping makes it easy to compare the results of different years in a specific period.

Informix Dynamic Server (IDS) provides some date processing functions. This article reviews some existing functions and provides some additional useful functions.

 

Back to Top

IDS date functions

IDS contains two "date" data types. One isDATE, The other isDATETIME.DATEIndicates a day, whileDATETIMEIndicates a specific time point, and its precision can be from year to second. IDS provides the following functions to manipulate these types:

• Date (varchar (10) returns the date type
  This function takes a string variable as a parameter, and its format is environment variableDBDATEAnd returnsDATEType. The default format of the US English region is "mdy4 /".

• Date (datetime) returns the date type
  This function is the same as the previous function, but its input parameters can be of any precision.DATETIME.

• Date (integer) returns the date type
INTEGERThe number of days since January 1, December 31, 1899.

• Day (date) returns the integer type
DAYThe date in the month returned by the function, in the formatINTEGER.

• DAY(DATETIME)
  It is the same as the previous function, but the input parameters of this function are of any precision.DATETIME.

• Extend (date, precision) returns the datetime type
EXTENDFunction adjustmentDATEParameter precision, and return the appropriateDATETIME. Because it is a bit vague, here is an example:
  EXTEND(DATE(1), YEAR TO SECOND)

• Extend (datetime, precision) returns the datetime type
  The operation object is the same as the previous function.DATETIMEInstead ofDATE.

• Month (date) returns the integer type
MONTHSlave ParametersDATEExtract the month.

• Month (datetime) returns the integer type
  This function is based on any precisionDATETIMEExtract the month.

• Weekday (date) returns the integer type
WEEKDAYBased on the specifiedDATEReturnsINTEGERIndicates the day of the week. 0 indicates Sunday, and 6 indicates Saturday.

• Weekday (datetime) returns the integer type
  Same as the previous function, but the operation object isDATETIME.

• Year (date) returns the integer type
  This function starts from the specified parameterDATEExtracted year.

• Year (datetime) returns the integer type
  Same as the previous function, but the operation object isDATETIME.

• Mdy (integer, integer, integer) returns the date type
  This function is based on threeINTEGERCreateDATE. These Parameters specify the month, day, and year respectively. Note that year is a four-digit integer.

• To_char (date, varchar (??)) Returns varchar (??) Type
  This function includesDATEParameters and a format parameter, and return a string that represents the date, the string complies with the required format. The format string can include:

  • % A: Week
  • % B: Month
  • % D: Day in decimal format
  • % Y: 4-digit year
  • % R: 24 hours

   

• To_char (date, varchar (??)) Returns varchar (??) Type
  Same as above.

• To_date (varchar (??), Varchar (??)) Returns the date type.
  This isTO_CHARUsing the same format string as the second parameter.

In addition to the above functions, there are two environmental variables that affect date processing:

• DBDATE: Provides the end user format of the date. This is described in the SQL Reference Manual (page 3rd-25.
• DBCENTURY: Defines how to expand a year when two digits instead of four digits are input. Acceptable values:R,P,FAndC. They indicate current, previous, future, and closest respectively. If noDBCENTURY, R is the default value. Described in the SQL Reference Manual (page 10.0-22 of IDS 3rd)DBCENTURY.

Finally, IDS defines two built-in functions to return the current date value.CURRENTReturnsDATETIMEValue, whileTODAYReturns the date of the current day.

 

Back to Top

Use date functions

The functions described above provide the functions of inputting, outputting, formatting, and extracting information. The first interesting usage I want to discuss is string-based Date input.

FunctionDATE()Receives a string as the input,DBDATEAndDBCENTURY. First, let's take a lookDBCENTURY.

DBCENTURYThe default value is R. This means that the century is determined by the century of the current date. The following example assumes that the system runs in the default us English region:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;(constant)09/02/20921 row(s) retrieved.

 

IfDBCENTURYIf it is set to P, it refers to the century closest to the current date. According to this setting, the previous example becomes:

SELECT date("9/2/92") FROM systables WHERE tabid = 1;(constant)09/02/19921 row(s) retrieved.

 

DBDATEEnvironment variables provide another possible change for date conversion. For the US English region, its default value is "mdy4 /". This means that the parts of the date string are separated by "/", and the order between them is month, day, and year. Note that the expected year is 4 digits, but you can alsoDBCENTURYThe configured rules are supplemented. ModifiableDBDATETo use the international date format. ThenDBDATEThe value will be "y4md -". In addition to the string type input date, this value also affects the conversion from date to string:

select order_date from orders WHERE order_num = 1001;order_date1998-05-201 row(s) retrieved.

 

You can useTO_CHARFunction, 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 19981 row(s) retrieved.

 

You can use some existing functions to extract values such as months and days, and use those values when defining table segments using expressions. You can also use them in SQL statements for grouping. For example, if you want to know how many orders there are every month, you can use the following statement:

SELECT YEAR(order_date) year, MONTH(order_date) month, COUNT(*) countFROM ordersGROUP BY 1, 2ORDER BY 1, 2;

 

This type of grouping is useful in various reports. If you are willing to use some basic scalability features of IDS, you can do more.

 

Back to Top

IDS scalability

IDS is the first database to expand the database function to suit the user's environment. The scalability has been available since IDs version 1997 in 9.01, And the scalability has been improved in IDS version 10. You can create new data types, new functions, and even new aggregates. Functions and aggregation can be written in C, Java, or SPL. For more information about the purpose of scalability, see references provided later in this article.

I usually write user-defined functions in C. However, for this article, I use SPL to write user-defined functions. The advantage of SPL is that it is a language familiar to IDS users. This language is also used when writing stored procedures.

 

Back to Top

Function Index

IDS v9.x and later versions support the function index concept. This means that indexes can be created on the function results. Then, you can use indexes to accelerate the processing of queries containing functions in SQL statements.

Built-in functions are created before IDs adds Extensible features. Indexes cannot be created directly on the result of the internal function creation. However, you can wrap built-in functions in a SPL function. For example, if you want to create an index on a month, you can create an SPL function, as shown below:

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

 

With this package function, you can create an index:

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

 

Then, you can use the SQL statement that utilizes the preceding indexes, for example:

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

Back to Top

New date functions

You can extract more information from the date: Year, week, month, and quarter.

First, let's look at the Chinese and Japanese functions of the year. With such a function, you can report activities by week without having to write a specific stored procedure or custom application code for each report. You can use the built-in functions in IDS to construct this function. In this way, this function looks very simple:

CREATE FUNCTION day_of_year(dt date)RETURNS integerWITH(NOT VARIANT)RETURN(1 + dt - MDY(1, 1, YEAR(dt)) );END FUNCTION;

 

The key to implementing this function is that a date is actually an integer, which indicates the number of days since January 1, December 31, 1899. This means that if you get the date of January 1, January 1, you only need to perform a simple subtraction.

You can use this functionEXECUTE FUNCTIONStatement, or used to set a value in a function or stored procedure, or used 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_y05/20/1998 1401 row(s) retrieved.

 

The week function is a little more complex in the middle of the year. It uses a similar calculation, but it needs to be divided by 7 days per week:

CREATE FUNCTION week_of_year(dt date)RETURNS integerWITH(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 0 for Sunday and 6 for Saturday. If February is Sunday, we know that February is the Sunday of the second week. If we start from China and Japan in another week in July January 1, it means that the first week is shorter. The weekday built-in function provides an offset for us to calculate the week of a date in a year.

The week_of_year () function has problems in the last week of the year and the first week of the next year. For example, February 1 is Friday and February 2 is Saturday. The week_of_year function provides the following results:

EXECUTE FUNCTION week_of_year(date("12/31/2004") );(expression) 531 row(s) retrieved.EXECUTE FUNCTION week_of_year(date("1/1/2005"));(expression) 11 row(s) retrieved.

 

Is this behavior correct? This is up to you. If not, you need to modify the code as needed to solve this problem. Therefore, you only need to add several lines of code to the SPL function.

If you want to calculate the week of a month, you can use the same function, but it is not based on January 1, January 1, the starting date is the 1st day of the month specified by the parameter:

CREATE FUNCTION week_of_month(dt date)RETURNS integerWITH(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;

Back to Top

Quarter () function

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

Many companies need to calculate the quarter based on their own business year, and the business year is often different from the standard year. Some organizations even have to calculate the quarter based on what needs to be done. For example, some schools must calculate calendar quarter, academic year quarter, and business quarter.

First, let's look at a simple implementation of each quarter over the years:

CREATE FUNCTION quarter(dt date)RETURNS integerWITH(NOT VARIANT)RETURN (YEAR(dt) * 100) + 1 + (MONTH(dt) - 1) / 3;END FUNCTION;

 

In this implementation, I also put the year in the quarterly representation. For example, third quarter of 2005 is represented as 200503. This can simplify the processing of SQL statements that span multiple years. You can also create different implementations, such as returning a string instead of an integer. You must make decisions based on your needs.

As mentioned above, you may need to calculate the quarter based on a period not the start date of January 1, January 1. This increases complexity in years different from quarterly years. For example, assume that a company starts its accounting year from January 1, September 1. This means that September 1, 2005 is actually the beginning of first quarter of 2006, and December 1 is the beginning of the second quarter, and so on.

The following code demonstrates the implementation of the new year from January 1, September 1. This code can be easily adjusted based on different start dates:

CREATE FUNCTION bizquarter(dt date)RETURNS integerWITH(NOT VARIANT)DEFINE yr int;DEFINE mm int;LET yr = YEAR(dt);LET mm = MONTH(dt) + 4; -- sept. to jan. is 4 monthsIF mm > 12 THEN LET yr = yr + 1; LET mm = mm - 12;END IFRETURN (yr * 100) + 1 + (mm - 1) / 3;END FUNCTION;

 

Compared with the quarter () function, the added processing in this function is to move the current month forward for a few months to calculate the quarter matching the business year.

 

Back to Top

Use new functions

With these functions, you can use them in SQL statements, just as they are built-in functions in IDS. For example:

SELECT quarter(order_date) quarter, count(*) countFROM ordersGROUP BY 1ORDER BY 1; quarter count 199802 16 199803 72 row(s) retrieved.

 

You can also create indexes on these functions:

CREATE INDEX orders_week_idsON orders(week_of_year(order_date));

 

This allows the database to return the information you want flexibly. IDS scalability is also useful in many other fields. See references for other articles on this topic.

 

Back to Top

Conclusion

We can easily extend the IDS feature to provide better data manipulation. The result is that the number of code to be written is reduced, and the number of SQL statements to be executed may be reduced to improve performance. The database is not a commodity. It is a strategic tool that provides you with business advantages.

By using the date control technology in this article, you can adjust IDs to suit your environment. If the date functions provided here are not fully suited to your environment, you can easily modify these functions. The flexibility provided by IDS means that IDs functions should be considered during the application design phase. In this way, we can get better performance, scalability, and simpler implementation.