CODEWITHSUNDEEP
postgresqldate-arithmetic
adura826
adura826

Reputation: 113

DATE ADD function in PostgreSQL

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?

Upvotes: 2

Views: 27029

Answers (3)

user45392
user45392

Reputation: 622

As of postgres 16 DATE_ADD() now exists.

In your example, you could use

DATE_ADD(currentday.date, '1 day')

From the documentation:

date_add ( timestamp with time zone, interval [, text ] ) → timestamp with time zone

Add an interval to a timestamp with time zone, computing times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted. The form with two arguments is equivalent to the timestamp with time zone + interval operator.

date_add('2021-10-31 00:00:00+02'::timestamptz, '1 day'::interval, 'Europe/Warsaw') → 2021-10-31 23:00:00+00

Upvotes: 1

Paul
Paul

Reputation: 3894

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

Upvotes: 17

Mike Organek
Mike Organek

Reputation: 12494

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;

Upvotes: 1

Related Questions