I am working on a question which has the schema as below:

user_id int
created_at datetime
number_of_comments int

and the question is, Return the total number of comments received for each user in the last 30 days. Assume today is 2020-02-10.

I figured out that one way of doing this problem is:

SELECT 
    user_id, 
    sum(number_of_comments) as comments 
FROM fb_comments_count
WHERE created_at >= '2020-02-10'::date - '30 day'::interval  
AND created_at <= '2020-02-10'::date  
GROUP BY 1

but I am struggling understand the logic behind it. For example, what does this keyword INTERVAL is and how it helps? and how are we subtracting a date from an interval?

I tried writing a small query: select 30 * INTERVAL '1 day' and it return a number which is 2592000. What is this number? Any help would be appreciated.

CodePudding user response：

The notation 'string literal'::datatype is a type cast in PostgreSQL proprietary syntax.
It is the same as the SQL standard CAST ('string literal' AS datatype).

The statement uses typecasts to avoid ambiguity, since there are many - operators in PostgreSQL (timestamp - timestamp, timestamp - interval, ...).

CodePudding user response：

Interval is a data type used store and deploy Time in years, months, days, hours, minutes, seconds.

In your query, 'interval' is being used to cast days and substract them from an specific date, similar than the DATEADD function in SQL server, to substract 30 days.

To see result try testing this in PostgreSQL:

SELECT '2020-02-10'::date - '30 day'::interval

and this in SQL server:

SELECT DATEADD(DAY,-30, '2020-02-10')