4

I'm trying to determine age of post and make a graph based upon it:

Stats per time

I made this query:

select sum(ViewCount), DATE() - CreationDate AS age
    FROM Posts
    WHERE
        PostTypeId = 1
    GROUP BY age
    ORDER BY age

I'm using DATE() but that doesn't work:

'DATE' is not a recognized built-in function name.

How should I calculate post age?

1 Answer 1

8

Since Data Explorer exposes data stored in SQL Server, use T-SQL's DATEDIFF() function. For example, grouping by age in days*:

SELECT SUM(ViewCount), DATEDIFF(day, CreationDate, CURRENT_TIMESTAMP) AS age
  FROM Posts
 WHERE PostTypeId = 1
 GROUP BY DATEDIFF(day, CreationDate, CURRENT_TIMESTAMP)
 ORDER BY age

* Keeping in mind that for subsequent runs the values may be skewed due to caching, though this has no impact on the results other than shifting the time points

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .