DATE ADD function in PostgreSQL

Question

I currently have the following code in Microsoft SQL Server to get users that viewed on two days in a row.

WITH uservideoviewvideo (date, user_id) AS (
  SELECT  DISTINCT date, user_id 
  FROM clickstream_videos
  WHERE event_name ='video_play'  
    and user_id IS NOT NULL
) 
SELECT currentday.date AS date, 
       COUNT(currentday.user_id) AS users_view_videos, 
       COUNT(nextday.user_id) AS users_view_next_day 
FROM userviewvideo currentday
  LEFT JOIN userviewvideo nextday 
         ON currentday.user_id = nextday.user_id AND DATEADD(DAY, 1, 
currentday.date) = nextday.date
GROUP BY currentday.date

I am trying to get the DATEADD function to work in PostgreSQL but I've been unable to figure out how to get this to work. Any suggestions?

Is the column `date` defined as `date` or `timestamp`? If it's `date`, then `date + 1` will do it for you. — Mike Organek, Jul 28 '20 at 20:03
https://www.postgresql.org/docs/current/functions-datetime.html — , Jul 28 '20 at 20:38

Paul · Accepted Answer · 2021-11-02T14:08:16.063

I don't think PostgreSQL really has a DATEADD function. Instead, just do:

+ INTERVAL '1 day'

SQL Server:

Add 1 day to the current date November 21, 2012
SELECT DATEADD(day, 1, GETDATE()); # 2012-11-22 17:22:01.423

PostgreSQL:

Add 1 day to the current date November 21, 2012
SELECT CURRENT_DATE + INTERVAL '1 day'; # 2012-11-22 17:22:01
SELECT CURRENT_DATE + 1; # 2012-11-22 17:22:01

http://www.sqlines.com/postgresql/how-to/dateadd

EDIT:

It might be useful if you're using a dynamic length of time to create a string and then cast it as an interval like:

+ (col_days || ' days')::interval

score 1 · Answer 2 · answered Jul 28 '20 at 20:33

You can use date + 1 to do the equivalent of dateadd(), but I do not think that your query does what you want to do.

You should use window functions, instead:

with plays as (
  select distinct date, user_id
    from clickstream_videos
   where event_name = 'video_play' 
     and user_id is not null
), nextdaywatch as (
  select date, user_id, 
         case
           when lead(date) over (partition by user_id
                                     order by date) = date + 1 then 1
           else 0
         end as user_view_next_day
    from plays
)
select date, 
       count(*) as users_view_videos,
       sum(user_view_next_day) as users_view_next_day
  from nextdaywatch
 group by date
 order by date;

DATE ADD function in PostgreSQL

2 Answers2

Linked