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
– Mike Organek, Commented Jul 28, 2020 at 20:03

Paul · Accepted Answer · 2021-11-02 14:08:16Z

19

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

edited Nov 2, 2021 at 14:08

answered Jul 28, 2020 at 20:08

Paul

3,9741 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Organek · Accepted Answer · 2020-07-28 20:33:37Z

1

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;

answered Jul 28, 2020 at 20:33

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Comments

user45392 · Accepted Answer · 2024-02-23 20:20:42Z

1

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

answered Feb 23, 2024 at 20:20

user45392

6248 silver badges18 bronze badges

Collectives™ on Stack Overflow

DATE ADD function in PostgreSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related