# Using DATE\_TRUNC for Data Aggregation

**URL:** https://meta.discourse.org/t/using-date-trunc-for-data-aggregation/278095
**Category:** Data & reporting
**Tags:** sql-tutorial
**Created:** [September 6, 2023, 11:24pm UTC](https://meta.discourse.org/t/using-date-trunc-for-data-aggregation/278095 "2023-09-06T23:24:37Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [September 6, 2023, 11:24pm UTC](https://meta.discourse.org/t/using-date-trunc-for-data-aggregation/278095/1 "2023-09-06T23:24:38Z")

</div>

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:

```sql
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.

```sql
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**
>
> ```sql
> -- 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.

```sql
-- [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**
>
> ```sql
> -- 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](https://meta.discourse.org/t/discourse-solved/30155) 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.

```sql
-- [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**
>
> ```sql
> -- [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.

```sql
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**
>
> ```sql
> -- 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](https://meta.discourse.org/t/32566?silent=true) queries. Feel free to use any of these queries on your site, and if you have any questions please ask them below. 🙂