Re: Interval FAQ - please review - Mailing list pgsql-sql

Why not just include examples of DATEDIFF and DATEADD functions?
For example:
CREATE FUNCTION datediff(timestamp, timestamp)
RETURNS integer AS '
BEGIN
RETURN $2 - $1;
END;
' LANGUAGE 'plpgsql';
And similarly with DATEADD.
You will increase the scope (and length) of your article, but only slightly:
and add some other useful education, as well.

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, August 16, 2001 11:22 AM
Subject: 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
>


----------------------------------------------------------------------------
----


>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>