For some applications, time periods are needed that represent quarters of the year. Although quarters could be represented by using an exact date data type and constraining the month and day values (i.e., to the first day of each quarter) or by using date ranges in PostgreSQL, these representations do not allow easy computation of the difference between two dates (in quarters) or the addition of specific number of quarters to a date to produce a new date.
The code below shows the implementation of custom composite data type in Postgres that represents quarters of a year. This data type is named "quarter". The implementation supports addition of a number of quarters to a "quarter" date to produce a new "quarter" date, and supports calculation of the difference between two "quarter" dates. Comparison operators are also defined for use in expressions using "quarter" dates and to allow indexing of data tables by "quarter" values.
The Postgres code to define this data type, and arithmetic and comparison operators, is shown below. Details on the creation of a custom data type and operators can be found in the Postgres documentation.
The definition of the "quarter" data type is simply:
To support addition and subtraction of an integer number of quarters to (or from) a "quarter" data type, a function must be defined to carry out this calculation. The arguments to the function are a "quarter" data type and the number of quarters; the second argument may be either positive or negative.
Subtracting two "quarter" dates can be carried out in a simple expression, but a function is defined to carry out this calculation because it is used for both the mathematical and comparison operators.
To support comparison operators (<, <=, =, <>, >=, and >), a function is defined to compare two "quarter" dates. This function returns -1, 0, or 1 depending on whether its first argument is less than, equal to, or greater than its second argument.
Each comparison operation is carried out by an individual function that uses the 'quarter_comp()' function.
After the functions to carry out the arithmetic and comparison operations have been defined, the operators themselves can be defined.
To allow "quarter" dates to be used in indexes, an operator class must be defined.
The same approach could be used to define a custom "month" data type. The code for a "month" data type would be almost identical, with changes needed only to the data type name used and to the functions 'qtr_add()' and 'qtr_diff()'.