Thread: 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
Attachment
>>>>> "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.
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 >
>>>>> "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.
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
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
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