SQL: PostgreSQL Date/Time Functions
A growing collection of examples relating to Date/Time manipulation in PostgreSQL.
Number of Days Between Dates
Suppose you have the following data in a table - indicating the date at which some form of contract commenced:
chirp=# SELECT startdate FROM contracts ORDER BY startdate;
startdate
------------
2002-11-29
2003-08-01
2004-09-23
2005-06-08
2005-09-28
2006-04-18
Now you want to display the number of days that each contract has run for. One option is to get the dates into PHP, convert to a timestamp, subtract that value from the current timestamp, divide by the number of seconds in a day and then round to the nearest whole number. Does that sound too complicated? Well it is.
Let's see what we can do in SQL to simplify the problem. For a start, we try a simple subtraction. It might seem like a long-shot, but what I like about Postgres is that a logical approach often bears fruit:
chirp=# SELECT NOW() - startdate AS days FROM contracts ORDER BY startdate;
days
---------------------------
1406 days 13:42:10.489465
1161 days 14:42:10.489465
742 days 14:42:10.489465
484 days 14:42:10.489465
372 days 14:42:10.489465
170 days 14:42:10.489465
Wow, it worked almost exactly as hoped. Now we just need to round to the nearest day. Again, the most logical approach pays off and we get a whole number of days for each contract:
chirp=# SELECT DATE_PART('days', NOW() - startdate) AS days FROM contracts ORDER BY startdate;
days
------
1406
1161
742
484
372
170
If instead of just the number of days you wanted a breakdown of years, months and days, then the AGE() function comes in handy:
chirp=# SELECT AGE(startdate) FROM contracts ORDER BY startdate;
age
------------------------
3 years 10 mons 6 days
3 years 2 mons 4 days
2 years 12 days
1 year 3 mons 27 days
1 year 7 days
5 mons 17 days
For future reference, the possible arguments for DATE_PART() are: century, day, decade, dow, doy, epoch, hour, microseconds, millennium, milliseconds, minute, month, quarter, second, timezone, timezone_hour, timezone_minute, week and year. Official documentation can be found under References below.
References
- PostgreSQL: PostgreSQL 7.4: Date/Time Types
- PostgreSQL: PostgreSQL 7.4: Date/Time Functions and Operators