Thread: Interval FAQ - please review

Interval FAQ - please review

From
"Josh Berkus"
Date:
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

Attachment

Re: Interval FAQ - please review

From
Vivek Khera
Date:
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:


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

Those docs are lacking an explanation that there is no such thing in
PostgreSQL as a timestamp *without* time zone.



Re: Interval FAQ - please review

From
"Jeff Eckermann"
Date:
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
>



Re: Re: Interval FAQ - please review

From
Vivek Khera
Date:
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:

>> Those docs are lacking an explanation that there is no such thing in
>> PostgreSQL as a timestamp *without* time zone.

JB> Hmmm .... OK, I'll revise the A: but I *don't* want to go into Time Zone
JB> issues in this intro.   Any docs I can link to?

The only good explanation came from the mailing list, from Tom Lane,
naturally ;-).  Basically, nobody wants to change it, and using a view
or a client-side function to strip the timezone info is the way to do
it.



Re: Interval FAQ - please review

From
"Josh Berkus"
Date:
Jeff,

> 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';

Not a bad idea.  Unfortunately, DATEDIFF & DATEADD are more complicated
than that.  More importantly, I don't want to get into Function design
in this article, which is aimed at newbies.

If, however, anyone *does* have an online sample of one or both of these
two functions, I'd love to link to it from the article.

-Josh

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


Re: Re: Interval FAQ - please review

From
"Josh Berkus"
Date:
Vivek,

> JB> Q. What about TIMESTAMP WITH TIME ZONE?  
> JB> A. An important topic, and datatype, that I don't want to get
> into here.
> JB> See the PostgreSQL docs.
> 
> Those docs are lacking an explanation that there is no such thing in
> PostgreSQL as a timestamp *without* time zone.

Hmmm .... OK, I'll revise the A: but I *don't* want to go into Time Zone
issues in this intro.   Any docs I can link to?

-Josh

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


Re: Interval FAQ - please review

From
Peter Eisentraut
Date:
Josh Berkus writes:

> 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

You should probably use the correct literal syntax here, e.g., TIMESTAMP
'1999-12-30' and INTERVAL '3 days'.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter