Using PostgreSQL's Interval to mimic SQL Server's DATEADD function

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