# 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