Interval FAQ - please review

Folks,

Please review this for inaccuracies before I post it to pgsql-newbie and
the docs.

-Josh

FAQ:  Working with Dates and Times in PostgreSQL

This FAQ is intended to answer the following questions:

Q: Where are the DATEADD() and DATEDIFF() functions in PostgreSQL?
Q: How do I tell the amount of time between X and Y?

KEYWORDS:  date, datetime, timestamp, operator, dateadd, datediff,
interval

One of PostgreSQL's joys is a robust support of a variety of date and
time data types and their associated operators.  This
has allowed me to write calendaring applications in Postgres that would
have been considerably more difficult on other
platforms.  Before we get down to the nuts-and-bolts, I need to explain
a few things to the many who have come to us from
database applications which are less ANSI 92 SQL compliant than
PostgreSQL (particularly Microsoft SQL Server, SyBase and
Microsoft Access).  If you are already educated, you'll want to skip
down to "Working with DATETIME, DATE, and INTERVAL
values".

(BTW, I am not on an anti-Microsoft tirade here.  I use MS SQL Server as
an example of a non-standards-compliant database
because I am a certified MS SQL Server admin and know its problems quite
well.  There are plenty of other non-compliant
databases on the market.)

ANSI SQL and OPERATORS

In the ANSI SQL world, operators (such as + - * % || NOT) are defined
only in the context of the data types being operated
upon.  Thus the division of two integers ( INT / INT ) does not function
in the same way as the divsion of two float values
(FLOAT / FLOAT).  More dramatically, you may subtract one integer (INT -
INT) from another, but you may not subtract one
string from another  (VARCHAR - VARCHAR), let alone subtract a string
from an integer (INT - VARCHAR).  The subtraction
operator (-) in these two operations, while it looks the same, is in
fact not the same owing to a different datatype context.  In
the absence of a pre-defined context, the operator does not function at
all and you get an error message.

This fundamental rule has a number of tedious consequences.  Frequently
you must CAST two values to the same data type
in order to work with them.  For example, try adding a FLOAT and a
NUMERIC value; you will get an error until you help out
the database by defining them both as FLOAT or both as NUMERIC
(CAST(FLOAT AS NUMERIC) + NUMERIC).  Even more
so, appending an integer to the end of a string requires a type
conversion function (to_char(INT, '00000')).  Further, if you
want to define your own data types, you must spend the hours necessary
to define all possible operators for them as well.

Some database developers, in a rush to get their products to market, saw
the above "user-unfriendly" behavior and cut it
out of the system by defining all operators to work in a
context-insensitive way.  Thus, in Microsoft Transact-SQL, you way
add a DOUBLE and an INTEGER, or even append an INTEGER directly to a
string in some cases.  The database can handle
the implicit conversions for you, because they have been simplified.

However, the Transact-SQL developers disregarded the essential reason
for including context-sensitive operators into
the SQL standard.  Only with real, context-sensitive operators can you
handle special data types that do not follow
arithmatic or concatination rules.  PostgreSQL's ability to handle IP
addresses, geometric shapes, and, most importantly for
our discussion, dates and times, is dependant on this robust operator
implementation.  Non-compliant dialects of SQL, such
as Transact-SQL, are forced to resort to proprietary functions like
DATEADD() and DATEDIFF() in order to work with dates
and times, and cannot handle more complex data types at all.

Thus, to answer the first question:
Q. Where are the DATEADD and DATEDIFF functions in PostgreSQL?
A. There are none.  PostgreSQL does not need them.  Use the + and -
operators instead.  Read on.


WORKING with DATETIME, DATE, and INTERVAL VALUES

Complete docs on date/time data types may be found at:
http://www.postgresql.org/idocs/index.php?datatype-datetime.html
I will not attempt to re-produce them here.  Instead, I will simply try
to explain to the beginner what you need to know to
actually work with dates, times, and intervals.

DATETIME or TIMESTAMP:  Structured "real" date and time values,
containing year, month, day, hour, minute, second and
millesecond for all useful date & time values (4713 BC to over 100,000
AD).
DATE: Simplified integer-based representation of a date defining only
year, month, and day.
INTERVAL: Structured value showing a period of time, including any/all
of years, months, weeks, days, hours, minutes,
seconds, and milleseconds.  "1 day", "42 minutes 10 seconds", and "2
years" are all INTERVAL values.

Q. What about TIMESTAMP WITH TIME ZONE?
A. An important topic, and datatype, that I don't want to get into here.
See the PostgreSQL docs.

Q. Which do I want to use: DATE or TIMESTAMP?  I don't need minutes or
hours in my value.
A. That depends.  DATE is easier to work with for arithmatic (e.g.
something re-occuring at a random interval of days), takes
less storage space, and doesn't trail "00:00:00" strings you don't need
when printed.  However, TIMESTAMP is far better for
real calendar calculations (e.g. something that happens on the 15th of
each month).  More below.

Now, to work with TIMESTAMP and INTERVAL, you need to understand these
few simple rules:

1. The difference between two TIMESTAMPs is always an INTERVAL
    '1999-12-30'::TIMESTAMP - '1999-12-11'::TIMESTAMP = '19 days'::INTERVAL
2. You may add or subtract an INTERVAL to a TIMESTAMP to produce another
TIMESTAMP:
    '1999-12-11'::TIMESTAMP + '19 days'::INTERVAL = '1999-12-30'::TIMESTAMP
3. You may add or subtract two INTERVALS:
    '1 month'::INTERVAL + '1 month 3 days'::INTERVAL = '2 months 3 days'
INTERVAL
4. Multiplication and division of INTERVALS is under development and
discussion at this time; it is suggested that you avoid it until
implementation is complete or you may get unexpected results.
5. You may NOT (ever):  Perform Addition, Multiplication, or Division
operations with two TIMESTAMPS:
    '2001-03-24' + '2001-10-01' = ERROR

Finally, the most important rule to keep in mind:
6. While minutes and hours are relatively constant, like the calendar
values they reflect, many larger INTERVAL values are *not* constant in
length when expressed in smaller INTERVAL values.  For example:
    '2001-07-02'::TIMESTAMP + '1 month'::INTERVAL = '2001-08-02'::TIMESTAMP
    '2001-07-02'::TIMESTAMP + '31 days'::INTERVAL = '2001-08-02'::TIMESTAMP
    but:
    '2001-02-02'::TIMESTAMP + '1 month'::INTERVAL = '2001-03-02'::TIMESTAMP
    '2001-02-02'::TIMESTAMP + '31 days'::INTERVAL = '2001-03-05'::TIMESTAMP

This makes the TIMESTAMP/INTERVAL combination ideal, for example, for
scheduling an event which must re-occur every month on the 8th
regardless of the length of the month, but problematic if you are trying
to figure out the number of days in the last 3.5 months.  Keep it in
mind!


The DATE datatype, however, is simpler to deal with if less powerful.
Here's your rules:

1. The difference between two DATES is always an INTEGER, representing
the number of DAYS difference:
    '1999-12-30'::DATE - '1999-12-11'::DATE = 19::INTEGER
2. You may add or subtract an INTEGER to a DATE to produce another DATE:
    '1999-12-11'::DATE + 19::INTEGER = '1999-12-30'::DATE
3. Because the difference of two DATES is an INTEGER, this difference
may be added, subtracted, divided, multiplied, or even modulo (%) to
your heart's content.
4. As with TIMESTAMP, you may NOT perform Addition, Multiplication,
Division, or other operations with two DATES.
5. Because DATE differences are always calcualted as whole numbers of
days, DATE/INTEGER cannot figure out the varying lengths of months and
years.   Thus, you cannot use DATE/INTEGER to schedule something for the
5th of every month without some very fancy length-of-month calculating
on the fly.

This makes DATE ideal for calendar applications involving a lot of
calculating based on numbers of days (e.g. "For how many 14-day periods
has employee "x" been employed?") but poor for actual calendaring apps.
Keep it in mind.


Q. All that is teriffic, but I'm porting an app from MS SQL Server, and
I need to support the DATEDIFF and DATEADD functions so that my stored
views will work under PostgreSQL.  What do I do?
A. Proceed to PostgreSQL TechDocs (http://techdocs.postgresql.org/)
There are many porting resources there, and I'd be surprised if someone
hasn't already re-created these functions under PostgreSQL.

Copyright 2001 Josh Berkus (http://www.agliodbs.com).  Permission
granted to use in any public forum for which no fee is charged if this
copyright notice appears in the document.  This advice is provided with
no warranty whatsoever, including any warranty of fitness for a
particular purpose.  Use at your own risk.


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco