Using DATE_TRUNC for Data Aggregation

Data & reporting
1

The date_trunc function is a powerful tool in SQL. It allows you to truncate a TIMESTAMP or an INTERVAL value based on a specified date part, which makes it an invaluable function when you want to aggregate or group data based on a specific time period.

Syntax

The syntax for the date_trunc function is as follows:

date_trunc('date_part', field)
  • date_part: This is a string that specifies the part of the date or timestamp to truncate to. It can be one of the following values:
    • millennium
    • century
    • decade
    • year
    • quarter
    • month
    • week
    • day
    • hour
    • minute
    • second
    • milliseconds
    • microseconds
  • field: This is the timestamp or interval to be truncated.

Example Usage In DE Queries

Let’s take a look at a few example queries that utilize date_trunc:

Count of New Topics by Month

Complexity Level: Beginner

This SQL query is used to count the number of topics created in each month in the Discourse database.

SELECT date_trunc('month', created_at)::DATE AS month, count(id) FROM topics GROUP BY month ORDER BY month DESC

In this query, date_trunc('month', created_at)::DATE truncates the created_at timestamp to the month and then casts it to display a date, effectively grouping the topics by the month they were created.

The count(id) function then counts the number of topics created in each month. The results are ordered by month in descending order, so the most recent month will be first.

Example Results:

month count
2023-09-01 1
2023-08-01 6
2023-07-01 10
Detailed Explanation with Inline Comments 
-- Select the month when the topic was created and the count of topics SELECT -- Truncate the 'created_at' timestamp to the month and cast it to a date -- This groups the topics by the month they were created date_trunc('month', created_at)::DATE AS month, -- Count the number of topics created in each month count(id) -- From the 'topics' table FROM topics -- Group the results by the month GROUP BY month -- Order the results by the month in descending order -- This means the most recent month will be first ORDER BY month DESC

Cumulative Total Users

Complexity Level: Intermediate

This query will provide a weekly report of user signups on a Discourse forum, along with a running total of users. It uses a WITH clause to create a temporary result set (daily_signups ), and then selects from that result set.

-- [params] -- date :start_date = 2000-01-01 -- date :end_date WITH daily_signups AS( SELECT to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date, Count (id) as Signups FROM users u WHERE u.created_at::date BETWEEN :start_date::date AND :end_date::date GROUP BY Date ) SELECT Date, Signups, SUM(Signups) OVER (ORDER BY Date) as total_users FROM daily_signups ORDER BY Date Asc

Here’s a breakdown of how this query is functioning:

  • The WITH clause creates a temporary result set named daily_signups. This result set contains the number of user signups for each week between the start and end dates specified by the parameters :start_date and :end_date.
  • Within the daily_signups result set, date_trunc('week', u.created_at)::date truncates the created_at timestamp to the week and then casts it to a date. This effectively groups the users by the week they signed up.
  • Count(id) then counts the number of users who signed up in each week.
  • In the main SELECT statement, SUM(Signups) OVER (ORDER BY Date) calculates a running total of users. The OVER (ORDER BY Date) clause specifies that the sum should be calculated over the rows ordered by date, so it gives a cumulative sum of signups up to each date.
  • The results are then ordered by date in ascending order.

Example Results:

date signups total_users
2013-01-28 20 20.0
2013-02-04 2136 2156.0
2013-02-11 442 2598.0
Detailed Explanation with Inline Comments 
-- Define parameters for the start and end dates -- [params] -- date :start_date = 2000-01-01 -- date :end_date -- Create a common table expression (CTE) to count the number of user signups each week WITH daily_signups AS( SELECT -- Truncate the 'created_at' timestamp to the week and format it as a date string to_char(date_trunc('week', u.created_at)::date,'YYYY-MM-DD') AS Date, -- Count the number of users who signed up Count (id) as Signups FROM users u WHERE -- Only include users who signed up between the start and end dates u.created_at::date BETWEEN :start_date::date AND :end_date::date GROUP BY Date ) -- Select the date, number of signups, and running total of signups SELECT Date, Signups, -- Calculate the running total of signups SUM(Signups) OVER (ORDER BY Date) as total_users FROM daily_signups -- Order the results by date in ascending order ORDER BY Date Asc

Number of Solved and Unsolved Questions per Month

Complexity Level: Intermediate / Requires Discourse Solved Plugin

This query will provide a monthly report of the number of solved and unsolved questions on a Discourse forum. This query assumes all topics on a site can be solved.

-- [params] -- date :start_date -- date :end_date WITH monthly_questions AS ( SELECT date_trunc('month', created_at)::DATE AS month, COUNT(id) as total_questions FROM topics WHERE created_at::date BETWEEN :start_date::date AND :end_date::date GROUP BY month ), solved_questions AS ( SELECT date_trunc('month', created_at)::DATE AS month, COUNT(id) as solved FROM user_actions WHERE created_at::date BETWEEN :start_date::date AND :end_date::date AND action_type = 15 GROUP BY month ) SELECT mq.month, mq.total_questions, COALESCE(sq.solved, 0) as solved, mq.total_questions - COALESCE(sq.solved, 0) as unsolved FROM monthly_questions mq LEFT JOIN solved_questions sq ON mq.month = sq.month ORDER BY mq.month ASC

In this query, the monthly_questions CTE counts the total number of questions (topics) created each month. The solved_questions CTE counts the number of questions marked as solved each month by counting the number of id from the user_actions table WITH action type = 15

The main SELECT statement then calculates the number of unsolved questions by subtracting the number of solved questions from the total number of questions. The results are ordered by month in ascending order, so the earliest month will be first.

Example Results:

month total_questions solved unsolved
2023-07-01 10 3 7
2023-08-01 6 0 6
2023-09-01 1 1 0
Detailed Explanation with Inline Comments 
-- [params] -- date :start_date = 2000-01-01 -- date :end_date -- Create a CTE to count the total number of questions (topics) created each month WITH monthly_questions AS ( SELECT -- Truncate the 'created_at' timestamp to the month date_trunc('month', created_at)::DATE AS month, -- Count the number of topics created in each month COUNT(id) as total_questions FROM topics WHERE -- Only include topics created between the start and end dates created_at::date BETWEEN :start_date::date AND :end_date::date GROUP BY month ), -- Create a CTE to count the number of questions marked as solved each month solved_questions AS ( SELECT -- Truncate the 'created_at' timestamp to the month date_trunc('month', created_at)::DATE AS month, -- Count the number of solved questions in each month COUNT(id) as solved FROM user_actions WHERE -- Only include actions taken between the start and end dates created_at::date BETWEEN :start_date::date AND :end_date::date -- Only consider actions where the action type is 15 (indicating a solved question) AND action_type = 15 GROUP BY month ) -- Select the month, total number of questions, number of solved questions, and number of unsolved questions SELECT mq.month, mq.total_questions, -- If there are no solved questions in a month, display 0 COALESCE(sq.solved, 0) as solved, -- Subtract the number of solved questions from the total number of questions to get the number of unsolved questions mq.total_questions - COALESCE(sq.solved, 0) as unsolved FROM monthly_questions mq -- Join the 'monthly_questions' and 'solved_questions' CTEs on the month LEFT JOIN solved_questions sq ON mq.month = sq.month -- Order the results by month in ascending order ORDER BY mq.month ASC

Topic Reply Statistics

Complexity Level: Advanced

This complex SQL query provides a weekly report on topic activity in a Discourse forum. It breaks down topic data into several key metrics: the number of topics with at least one response, the number of topics without a response, the maximum number of days a topic has gone without a response, and the average time to the first response.

WITH posts_list AS ( SELECT t.id topic_id, p.post_number, p.created_at, ROW_NUMBER () OVER ( PARTITION BY p.topic_id ORDER BY p.post_number) AS post_order FROM posts p INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL WHERE p.post_type = 1 AND p.deleted_at ISNULL AND t.deleted_at ISNULL AND t.archetype = 'regular' ORDER BY p.topic_id, p.post_number ), atleast_1_response AS ( SELECT date_trunc('week', t.created_at::date)::date AS "week", COUNT(*) AS "topics with atleast one response" FROM topics t WHERE t.archetype = 'regular' AND t.deleted_at ISNULL AND t.posts_count >= 2 GROUP BY "week" ), no_response AS( SELECT date_trunc('week', t.created_at::date)::date AS "week", COUNT(*) AS "topics without response" FROM topics t WHERE t.archetype = 'regular' AND t.deleted_at ISNULL AND t.posts_count = 1 GROUP BY "week" ), max_days_without_response AS( SELECT date_trunc('week', t.created_at::date)::date AS "week", CURRENT_DATE::date-MIN(created_at)::date AS "max days without response" FROM topics t WHERE t.archetype = 'regular' AND t.deleted_at ISNULL AND t.posts_count = 1 GROUP BY "week" ), avg_time_first_response AS ( SELECT date_trunc('week', pl.created_at::date)::date AS "week", EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)" FROM posts_list pl INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2 WHERE pl.post_order = 1 GROUP BY "week" ) SELECT ar.week, "topics without response", "max days without response", "topics with atleast one response", "avg time first response (h)" FROM atleast_1_response ar INNER JOIN no_response nr ON nr.week = ar.week INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week ORDER BY "week" DESC

Here’s a summary of how this query works:

  • The posts_list common table expression (CTE) selects a list of all regular posts from the posts and topics tables, ordered by topic_id and post_number. It also assigns a row number (post_order) to each post within its topic.
  • The atleast_1_response CTE counts the number of regular topics with at least one response (i.e., posts_count is greater than or equal to 2) for each week.
  • The no_response CTE counts the number of regular topics without a response (i.e., posts_count equals 1) for each week.
  • The max_days_without_response CTE calculates the maximum number of days a topic without a response has been left unanswered for each week.
  • The avg_time_first_response CTE calculates the average time to the first response for each topic in hours, for each week.
  • The main SELECT statement then joins these CTEs together on the week and selects the relevant columns. The results are ordered by week in descending order.
week topics without response max days without response topics with atleast one response avg time first response (h)
2023-09-04 15 2 47 2.6778684519444444
2023-08-28 30 9 138 8.7899938238888889
2023-08-21 22 16 130 9.3280889688888889
Detailed Explanation with Inline Comments 
-- Create a temporary table (CTE) of all regular posts, ordered by topic_id and post_number WITH posts_list AS ( SELECT t.id topic_id, -- Topic ID p.post_number, -- Post number p.created_at, -- Post creation date -- Assign a row number to each post within its topic ROW_NUMBER () OVER ( PARTITION BY p.topic_id ORDER BY p.post_number) AS post_order FROM posts p -- Join with topics table, only considering regular topics that are not deleted INNER JOIN topics t ON t.id = p.topic_id AND archetype = 'regular' AND t.deleted_at ISNULL WHERE p.post_type = 1 AND p.deleted_at ISNULL -- Exclude deleted posts AND t.deleted_at ISNULL -- Exclude deleted topics AND t.archetype = 'regular' -- Only consider regular topics ORDER BY p.topic_id, p.post_number ), -- Create a CTE to count the number of regular topics with at least one response for each week atleast_1_response AS ( SELECT -- Truncate the created_at timestamp to the week date_trunc('week', t.created_at::date)::date AS "week", COUNT(*) AS "topics with atleast one response" FROM topics t WHERE t.archetype = 'regular' -- Only consider regular topics AND t.deleted_at ISNULL -- Exclude deleted topics AND t.posts_count >= 2 -- Only consider topics with at least one response GROUP BY "week" ), -- Create a CTE to count the number of regular topics without a response for each week no_response AS( SELECT -- Truncate the created_at timestamp to the week date_trunc('week', t.created_at::date)::date AS "week", COUNT(*) AS "topics without response" FROM topics t WHERE t.archetype = 'regular' -- Only consider regular topics AND t.deleted_at ISNULL -- Exclude deleted topics AND t.posts_count = 1 -- Only consider topics without a response GROUP BY "week" ), -- Create a CTE to calculate the maximum number of days a topic without a response has been left unanswered for each week max_days_without_response AS( SELECT -- Truncate the created_at timestamp to the week date_trunc('week', t.created_at::date)::date AS "week", -- Calculate the number of days from the topic creation date to the current date CURRENT_DATE::date-MIN(created_at)::date AS "max days without response" FROM topics t WHERE t.archetype = 'regular' -- Only consider regular topics AND t.deleted_at ISNULL -- Exclude deleted topics AND t.posts_count = 1 -- Only consider topics without a response GROUP BY "week" ), -- Create a CTE to calculate the average time to the first response for each topic in hours, for each week avg_time_first_response AS ( SELECT -- Truncate the created_at timestamp to the week date_trunc('week', pl.created_at::date)::date AS "week", -- Calculate the average time to the first response in hours EXTRACT(epoch FROM AVG(pl2.created_at-pl.created_at))/3600 AS "avg time first response (h)" FROM posts_list pl -- Join with the posts_list CTE, only considering the second post in each topic INNER JOIN posts_list pl2 ON pl.topic_id = pl2.topic_id AND pl2.post_order = 2 WHERE pl.post_order = 1 -- Only consider the first post in each topic GROUP BY "week" ) -- Select the week, the number of topics without a response, the maximum number of days without a response, the number of topics with at least one response, and the average time to the first response SELECT ar.week, "topics without response", "max days without response", "topics with atleast one response", "avg time first response (h)" FROM atleast_1_response ar -- Join the CTEs together on the week INNER JOIN no_response nr ON nr.week = ar.week INNER JOIN max_days_without_response mdwr ON mdwr.week = ar.week INNER JOIN avg_time_first_response atfr ON atfr.week = ar.week -- Order by week in descending order ORDER BY "week" DESC

These are just a few examples of the way you can use date_trunc in your Data Explorer queries. Feel free to use any of these queries on your site, and if you have any questions please ask them below. :slight_smile:

5 Likes