PostgreSQL 中的 DATE ADD 函数 [英] DATE ADD function in PostgreSQL

问题描述

我目前在 Microsoft SQL Server 中有以下代码来获取连续两天查看的用户.

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

我试图让 DATEADD 函数在 PostgreSQL 中工作，但我一直无法弄清楚如何让它工作.有什么建议吗?

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?

推荐答案

我不认为 PostgreSQL 真的有 DATEADD 函数.相反，只需执行以下操作:

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

+ INTERVAL '1 天'

SQL 服务器:

在当前日期 2012 年 11 月 21 日的基础上增加 1 天
SELECT DATEADD(day, 1, GETDATE()); # 2012-11-22 17:22:01.423

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

PostgreSQL:

PostgreSQL:

在当前日期 2012 年 11 月 21 日基础上增加 1 天
SELECT CURRENT_DATE + INTERVAL '1 天'; # 2012-11-22 17:22:01
SELECT CURRENT_DATE + 1; # 2012-11-22 17:22:01

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

这篇关于PostgreSQL 中的 DATE ADD 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！