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.
- PostgreSQL: PostgreSQL 7.4: Date/Time Types
- PostgreSQL: PostgreSQL 7.4: Date/Time Functions and Operators