Working with Dates and Times in PostgreSQL

by Josh Berkus

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

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 exist, 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.

Types

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.

What about TIMESTAMP WITH TIME ZONE?

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.

Which do I want to use: DATE or TIMESTAMP? I don't need minutes or hours in my value

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 :

1. The difference between two TIMESTAMPs is always an INTERVAL

TIMESTAMP '1999-12-30' - TIMESTAMP '1999-12-11' = INTERVAL '19 days'

2. 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'

3. You may add or subtract two INTERVALS

INTERVAL '1 month' + INTERVAL '1 month 3 days' = INTERVAL '2 months 3 days'

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.

As not all divisions of intervals can be answered distinctly, it might never be implemented. If you only want to divide intervals consisting of days, hours, minutes and seconds (such as results of timestamp subtractions), the way to go is:

 EXTRACT(EPOCH FROM INTERVAL '4 hours') / EXTRACT(EPOCH FROM INTERVAL '2 hours') = 2

5. You may NOT (ever) perform Addition, Multiplication, or Division operations with two TIMESTAMPS

TIMESTAMP '2001-03-24' + TIMESTAMP '2001-10-01' = OPERATION ERROR

6. Many larger INTERVAL values, like the calendar values they reflect, are not constant in length when expressed in smaller INTERVAL values

For example (differences bolded):

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'

This makes the TIMESTAMP/INTERVAL combination ideal, for example, for scheduling an event which must reoccur 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.

Operations with DATEs

1. 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'

DATE/INTEGER cannot figure out the varying lengths of months and years

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.

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

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.

I need to display a DATE as text, or convert text into a DATE or INTERVAL

You want the to_date(), to_char(), and interval() functions.

What if I want to get the month as an integer out of a date?

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 ))