Skip site navigation (1)
Skip section navigation (2)
Q: How do I tell the amount of time between X and Y?
KEYWORDS: date, datetime, timestamp, operator, dateadd, datediff, interval
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.)
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" behaviour and cut it out of the system by defining all operators to work in a context-insensitive way. Thus, in Microsoft Transact-SQL, you may 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 arithmetic or concatenation 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.
DATETIME or TIMESTAMP : Structured "real" date and time values, containing year, month, day, hour, minute, second and millisecond 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 milliseconds. "1 day", "42 minutes 10 seconds", and "2 years" are all INTERVAL values.
Q. What about TIMESTAMP WITH TIME ZONE?
A. An important topic, that I don't want to get into here. Eventually someone will document this. Suffice it to say that all TIMESTAMP values carry TIMEZONE data as well which you may safely ignore if you don't need to handle different time zones.
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 arithmetic (e.g. something reoccurring 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 or the 2nd Thursday of leap years). More below.
Now, to work with TIMESTAMP and INTERVAL, you need to understand these few simple rules :
The DATE datatype, however, is simpler to deal with if less powerful.
Here's your rules:
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.
Q. I need to display a DATE as text, or convert text into a DATE or INTERVAL.
A. You want the to_date(), to_char(), and interval() functions. See "functions and operators" in the PostgreSQL docs: http://www.postgresql.org/docs/current/interactivefunctions.html
Q. What if I want to get the month as an integer out of a date?
A. You want the extract() function. This function also works to give you other numeric intervals from a timestamp, including the Unix system datetime (e.g. EXTRACT ( epoch from some_date ))
Peripheral Links
Header And Logo
|
Site Navigation
| This page is user contributed documentation. See the bottom of the page for information about the author. | |
date time pgsql
Technical documentation > Community Generated Articles, Guides, and Documentation > FAQ: Working with Dates and Times in PostgreSQL
FAQ: Working with Dates and Times in PostgreSQL
by Josh BerkusThis 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
First, the legalese
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, or alternately in any published for-fee work if 1% or more of the proceeds of such work are donated or paid to benefit PostgreSQL development. This advice is provided with no warranty whatsoever, including any warranty of fitness for a particular purpose. Use at your own risk.INTRODUCTION
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 PostgreSQL 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 + - * % || !) 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 division 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 predefined 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" behaviour and cut it out of the system by defining all operators to work in a context-insensitive way. Thus, in Microsoft Transact-SQL, you may 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 arithmetic or concatenation 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/docs/current/interactivedatatype-datetime.html. I will not attempt to reproduce 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 millisecond 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 milliseconds. "1 day", "42 minutes 10 seconds", and "2 years" are all INTERVAL values.
Q. What about TIMESTAMP WITH TIME ZONE?
A. An important topic, that I don't want to get into here. Eventually someone will document this. Suffice it to say that all TIMESTAMP values carry TIMEZONE data as well which you may safely ignore if you don't need to handle different time zones.
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 arithmetic (e.g. something reoccurring 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 or the 2nd Thursday of leap years). More below.
Now, to work with TIMESTAMP and INTERVAL, you need to understand these few simple rules :
- The difference between two TIMESTAMPs is always an INTERVAL :
TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' = INTERVAL '19 days'
- You may add or subtract an INTERVAL to a TIMESTAMP to produce another TIMESTAMP
:
TIMESTAMP '1999-12-11' + INTERVAL '19 days' = TIMESTAMP '1999-12-30'
- You may add or subtract two INTERVALS :
INTERVAL '1 month' + INTERVAL '1 month 3 days' = INTERVAL '2 months 3 days'
- 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.
- You may NOT (ever) perform Addition, Multiplication, or Division operations
with two TIMESTAMPS:
TIMESTAMP '2001-03-24' + TIMESTAMP '2001-10-01' = OPERATION ERROR
Finally, the most important rule to keep in mind :
- While minutes and hours are relatively constant, many larger INTERVAL values,
like the calendar values they reflect, are *not* constant in length when expressed
in smaller INTERVAL values. For example :
TIMESTAMP '2001-07-02' + INTERVAL '1 month' = TIMESTAMP '2001-08-02'
TIMESTAMP '2001-07-02' + INTERVAL '31 days' = TIMESTAMP '2001-08-02'
but :
TIMESTAMP '2001-02-02' + INTERVAL '1 month' = TIMESTAMP '2001-03-02'
TIMESTAMP '2001-02-02' + INTERVAL '31 days' = TIMESTAMP '2001-03-05'
The DATE datatype, however, is simpler to deal with if less powerful.
Here's your rules:
- The difference between two DATES is always an INTEGER, representing the number
of DAYS difference:
DATE '1999-12-30' - DATE '1999-12-11' = INTEGER 19
- You may add or subtract an INTEGER to a DATE to produce another DATE:
DATE '1999-12-11' + INTEGER 19 = DATE '1999-12-30'
- 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.
- As with TIMESTAMP, you may NOT perform Addition, Multiplication, Division,
or other operations with two DATES.
- Because DATE differences are always calculated 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.
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.
Q. I need to display a DATE as text, or convert text into a DATE or INTERVAL.
A. You want the to_date(), to_char(), and interval() functions. See "functions and operators" in the PostgreSQL docs: http://www.postgresql.org/docs/current/interactivefunctions.html
Q. What if I want to get the month as an integer out of a date?
A. You want the extract() function. This function also works to give you other numeric intervals from a timestamp, including the Unix system datetime (e.g. EXTRACT ( epoch from some_date ))