Rounding and Trimming Temporal Data: DATE_TRUNC()

DATE_TRUNC() is a powerful tool that lets you trim temporal values down to a specific time unit. For example, you can round a timestamp to the start of the day, month, year, hour, and so on. This is especially handy when analyzing data by periods (like if you need to group orders by day, month, or year).

Imagine a date and time as a long string of text, where you have hours, minutes, seconds. The DATE_TRUNC() function takes that string and "cuts off" the extra, leaving only the part you need. For example:

• You want to trim the date 2023-10-01 15:30:45 to the start of the day. The result will be 2023-10-01 00:00:00.
• Or you want to keep only the first second of the hour, so 2023-10-01 15:00:00.

Syntax

The syntax for the DATE_TRUNC() function looks like this:

DATE_TRUNC(field, source)

• field — this is the time unit you want to "trim" the date to. For example, year, month, day, hour, minute.
• source — this is the temporal value you want to trim. It can be a column of type TIMESTAMP or the result of another function, like NOW().

Here's a simple call:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Result: 2023-10-01 00:00:00

Supported Fields

Here's a list of some supported time units you can use in DATE_TRUNC():

The smaller the time unit, the more precise the trimming result will be. By the way, the week starts on Sunday :)

Examples of Using DATE_TRUNC()

Trimming to the start of the day. In this example, we'll take a timestamp and round it to the start of the day:

SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Result: 2023-10-01 00:00:00

Trimming to the start of the month. Now we'll trim the date to the start of the month:

SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Result: 2023-10-01 00:00:00

Trimming to the start of the year. Let's try rounding the date to the start of the year:

SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Result: 2023-01-01 00:00:00

Using with current time (NOW()). If you always want to work with the current date and time, you can combine DATE_TRUNC() and NOW():

SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- Result will depend on the current time, for example: 2023-10-01 15:00:00

Grouping orders by months. Now let's move to a more practical example. Suppose we have a table with orders, where each record has an order date. We want to count the number of orders for each month:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date TIMESTAMP NOT NULL
);

INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');

SELECT DATE_TRUNC('month', order_date) AS order_month,
       COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;

Result:

Practical Use Cases

Analyzing temporal data by periods: want to know how many users registered each year, month, or day? Use DATE_TRUNC() to group your data.

Building reports: properly rounding your timestamp makes reports way easier to read.

Comparing date and time: if you have temporal data with high-precision (like milliseconds), trim it to the level you need for correct comparison.

Common Mistakes When Using DATE_TRUNC()

Using unsupported fields. For example, the millisecond field isn't supported, and trying to use it will throw an error.

Wrong data type. The DATE_TRUNC() function only works with temporal data types like TIMESTAMP. If you pass it a string, you'll get an error.

Rounding error. Remember, DATE_TRUNC() always trims the time to the start of the specified time unit. If you want to round time, you should use other approaches.