I wanted to do some date calculations in PostgreSQL and was doing some research on if something like the DATEDIFF function that exists in SQL Server is available in PostgreSQL. These notes are mostly for me so I can refer back to them..but maybe they are useful for someone else as well
In SQL Server to add dates, minutes and other fractions of a date to a date, you can use the DATEADD function
Here are some quick examples, if you want to run this in SQL Server, create this table with one row first
CREATE TABLE test(SomeDate date); INSERT INTO test VALUES('20120101');
And here is a simple DATEADD query, that adds 1 or 2 days to a date by using both the datepart and the abbreviated datepart
SELECT SomeDate, DATEADD(dd,1,SomeDate) as Interval1dd, DATEADD(dd,2,SomeDate) as Interval2dd, DATEADD(day,1,SomeDate) as Interval1Day, DATEADD(day,2,SomeDate) as Interval2Day FROM test
That will give us the following output
SomeDate Interval1dd Interval2dd Interval1Day Interval2Day
2012-01-01 2012-01-02 2012-01-03 2012-01-02 2012-01-03
If you want to go negative, all you have to do is place a minus sign in front of the number
SELECT SomeDate, DATEADD(dd,-1,SomeDate) as Interval1dd, DATEADD(dd,-2,SomeDate) as Interval2dd, DATEADD(day,-1,SomeDate) as Interval1Day, DATEADD(day,-2,SomeDate) as Interval2Day FROM test
Here is the output of that query
SomeDate Interval1dd Interval2dd Interval1Day Interval2Day
2012-01-01 2011-12-31 2011-12-30 2011-12-31 2011-12-30
Here are all the valid datepart arguments in SQL Server
| datepart | Abbreviations |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw, w |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| microsecond | mcs |
| nanosecond | ns |
Now let's take a look at PostgreSQL
In PostgreSQL, there is no DATEPART function, but you can use interval literals to accomplish something that behaves the same
Let's do something similar like we did with the SQL Server queries, first create this temp table with one row
CREATE Temp TABLE test(SomeDate date); INSERT INTO test VALUES( to_date('20120101','YYYYMMDD'));
Now let's run this query
SELECT SomeDate,(SomeDate + 1 * INTERVAL '1 Day' ) as Interval1Day, (SomeDate + 1 * INTERVAL '1 D') as IntervalD, (SomeDate + 2 * INTERVAL '1 Day' ) as Interval2Times1Day, (SomeDate + 1 * INTERVAL '2 Day' ) as Interval2Days, (SomeDate + 2 * INTERVAL '2 Days' ) as Interval2Times2Days, (SomeDate + INTERVAL '1 Day' ) as IntervalD FROM test
Here is the output
"2012-01-01";"2012-01-02 00:00:00";"2012-01-02 00:00:00";"2012-01-03 00:00:00";"2012-01-03 00:00:00";"2012-01-05 00:00:00";"2012-01-02 00:00:00"
When you copy from pgAdmin , you don't get the column aliases, so below is a screenshot of what it looks like(Click on the image for a bigger sized image)
As you can see there are two parts where you can supply a number
I think I prefer the top one from the query below, since it resembles the DATEPART function more
(SomeDate + 2 * INTERVAL '1 Day' ) as Interval2Times1Day, (SomeDate + 1 * INTERVAL '2 Day' ) as Interval2Days,
But as you saw,it was possible to add 4 days by using a one in both places like shown below
(SomeDate + 2 * INTERVAL '2 Days' ) as Interval2Times2Days,
And of course if you want, you can just use the number inside the string like in this example below
(SomeDate + INTERVAL '1 Day' ) as IntervalD
It's up to you, but I don't like changing numbers inside a string
To do negative numbers, you just change the positive number to a negative number, here is the same query from before but now with negative numbers
SELECT SomeDate,(SomeDate + -1 * INTERVAL '1 Day' ) as Interval1Day, (SomeDate + -1 * INTERVAL '1 D') as IntervalD, (SomeDate + -2 * INTERVAL '1 Day' ) as Interval2Times1Day, (SomeDate + -1 * INTERVAL '2 Day' ) as Interval2Days, (SomeDate + -2 * INTERVAL '-2 Days')as Interval2Times2Days, (SomeDate + INTERVAL '-1 Day' ) as IntervalD FROM test
Here is the output
"2012-01-01";"2011-12-31 00:00:00";"2011-12-31 00:00:00";"2011-12-30 00:00:00";"2011-12-30 00:00:00";"2012-01-05 00:00:00";"2011-12-31 00:00:00"
As you can see that all works as expected, did you notice that the we get +4 when we do -2 * -2?
Here is the output also from pgAdmin so that you can see the column aliases
Besides using days, you can also use these parts of a date
| Abbreviation | Meaning |
|---|---|
| Y | Years |
| M | Months (in the date part) |
| W | Weeks |
| D | Days |
| H | Hours |
| M | Minutes (in the time part) |
| S | Seconds |
Let's take a look by using some of these in a query
SELECT SomeDate,(SomeDate + 1 * INTERVAL '1 Day' ) as Interval1Day, (SomeDate + 1 * INTERVAL '1 Week' ) as Interval1Week, (SomeDate + 1 * INTERVAL '1 Month' ) as Interval1Month, (SomeDate + 1 * INTERVAL '1 Year' ) as Interval1Year FROM test UNION ALL SELECT SomeDate,(SomeDate + 1 * INTERVAL '1 D' ) as Interval1Day, (SomeDate + 1 * INTERVAL '1 W' ) as Interval1Week, (SomeDate + 1 * INTERVAL '1 M' ) as Interval1Month, (SomeDate + 1 * INTERVAL '1 Y' ) as Interval1Year FROM test
Here is the output
"2012-01-01";"2012-01-02 00:00:00";"2012-01-08 00:00:00";"2012-02-01 00:00:00";"2013-01-01 00:00:00"
"2012-01-01";"2012-01-02 00:00:00";"2012-01-08 00:00:00";"2012-01-01 00:01:00";"2013-01-01 00:00:00"
Here is the output also from pgAdmin so that you can see the column aliases
As you can see when using M it used minute not month. I would recommend to always use the full name and not the abbreviated part so as not to create confusion
That's all for this post
