DATE_TRUNC for rounding dates in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the DATE_TRUNC function do in PostgreSQL?

DATE_TRUNC rounds a timestamp or date to the specified precision, like to the nearest hour, day, or month.

beginner

How would you round a timestamp to the start of the day using DATE_TRUNC?

Use DATE_TRUNC('day', your_timestamp). It sets the time to 00:00:00 of that day.

intermediate

Which of these is a valid precision for DATE_TRUNC?
year, month, minute, second, decade

All are valid precisions for DATE_TRUNC in PostgreSQL.

beginner

What will DATE_TRUNC('hour', '2024-06-15 14:37:22') return?

It returns 2024-06-15 14:00:00, rounding down to the start of the hour.

intermediate

Can DATE_TRUNC be used to round dates to weeks? If yes, how?

Yes. Use DATE_TRUNC('week', your_date). It rounds to the start of the week (Sunday).

What does DATE_TRUNC('month', '2024-06-15 10:20:30') return?

A '2024-06-01 00:00:00'

B '2024-06-15 00:00:00'

C '2024-06-30 23:59:59'

D '2024-01-01 00:00:00'

DATE_TRUNC('month', ...) sets the date to the first day of the month at midnight.

Which precision rounds to the start of the week in DATE_TRUNC?

A month

B day

C week

D hour

'week' rounds to the Sunday of that week.

If you want to remove minutes and seconds from a timestamp, which DATE_TRUNC precision do you use?

A day

B hour

C minute

D second

DATE_TRUNC('hour', ...) sets minutes and seconds to zero.

What happens if you use DATE_TRUNC('second', '2024-06-15 10:20:30.123')?

A It rounds to the nearest second, removing milliseconds

B It rounds to the nearest minute

C It returns the original timestamp unchanged

D It causes an error

second precision removes fractional seconds (milliseconds).

Can DATE_TRUNC be used on a date type (without time)?

A No, it causes an error

B No, it only works on timestamps

C Yes, but only for 'year' precision

D Yes, it works the same as with timestamps

DATE_TRUNC works on both date and timestamp types.

Explain how DATE_TRUNC can be used to round a timestamp to the start of a day and why this might be useful.

Think about removing the time part to compare dates easily.

List at least five different precisions you can use with DATE_TRUNC and describe what each one does.

Consider common time units and how truncating affects the timestamp.

Practice

1. What does the PostgreSQL function DATE_TRUNC('month', timestamp) do?

easy

A. It returns the exact timestamp without any change.

B. It rounds the timestamp down to the first day of the month at 00:00:00.

C. It rounds the timestamp up to the last day of the month at 23:59:59.

D. It converts the timestamp to a string showing only the month.

Solution

1. Step 1: Understand DATE_TRUNC behavior

   DATE_TRUNC cuts off smaller parts of a timestamp to the specified precision, rounding down.

2. Step 2: Apply 'month' precision

   Using 'month' sets the date to the first day of that month at midnight (00:00:00).

3. Final Answer:

   It rounds the timestamp down to the first day of the month at 00:00:00. -> Option B

4. Quick Check:

   DATE_TRUNC('month', timestamp) = first day of month [OK]

Hint: DATE_TRUNC cuts smaller parts, sets time to zero [OK]

Common Mistakes:

• Thinking it rounds up instead of down
• Assuming it returns a string
• Believing it keeps the original timestamp unchanged

2. Which of the following is the correct syntax to truncate a timestamp to the nearest hour in PostgreSQL?

easy

A. DATE_TRUNC('hour', 13:45:30)

B. DATE_TRUNC(hour, '2024-06-15 13:45:30')

C. DATE_TRUNC('hour', 2024-06-15 13:45:30)

D. DATE_TRUNC('hour', '2024-06-15 13:45:30')

Solution

1. Step 1: Check function syntax

   DATE_TRUNC requires the first argument as a string specifying the precision, e.g., 'hour'.

2. Step 2: Validate timestamp format

   The second argument must be a timestamp or string representing a timestamp enclosed in quotes.

3. Final Answer:

   DATE_TRUNC('hour', '2024-06-15 13:45:30') -> Option D

4. Quick Check:

   Correct syntax uses quotes around precision and timestamp [OK]

Hint: Always quote the precision string in DATE_TRUNC [OK]

Common Mistakes:

• Not quoting the precision string
• Passing timestamp without quotes
• Using incomplete timestamp formats

3. Given the query:
SELECT DATE_TRUNC('day', TIMESTAMP '2024-06-15 18:23:45');
What is the output?

medium

A. 2024-06-15 18:23:45

B. 2024-06-01 00:00:00

C. 2024-06-15 00:00:00

D. 2024-06-15 23:59:59

Solution

1. Step 1: Understand 'day' precision in DATE_TRUNC

   Truncating to 'day' sets the time to midnight (00:00:00) of the same date.

2. Step 2: Apply to given timestamp

   For '2024-06-15 18:23:45', truncation results in '2024-06-15 00:00:00'.

3. Final Answer:

   2024-06-15 00:00:00 -> Option C

4. Quick Check:

   DATE_TRUNC('day', timestamp) = date at midnight [OK]

Hint: Truncating to day resets time to 00:00:00 [OK]

Common Mistakes:

• Expecting original time to remain
• Confusing day truncation with month truncation
• Thinking it rounds to nearest day

4. Identify the error in this query:
SELECT DATE_TRUNC('min', '2024-06-15 12:34:56');

medium

A. The precision 'min' is invalid in DATE_TRUNC.

B. The timestamp string is not cast to TIMESTAMP type.

C. The function DATE_TRUNC requires three arguments.

D. The timestamp string should be in single quotes.

Solution

1. Step 1: Check precision argument

   DATE_TRUNC requires the first argument to be a specific precision string (e.g., 'minute', 'hour'). 'min' is invalid.

2. Step 2: Identify the error

   This causes an error like 'invalid value for parameter "field": "min"'.

3. Final Answer:

   The precision 'min' is invalid in DATE_TRUNC. -> Option A

4. Quick Check:

   Valid precisions include 'minute' [OK]

Hint: Use exact precision strings: 'minute' not 'min' [OK]

Common Mistakes:

• Assuming string is auto-converted
• Using invalid precision values
• Adding extra arguments to DATE_TRUNC

5. You want to group sales data by week starting on Monday using DATE_TRUNC. Which query correctly truncates the timestamp to the start of the week?

hard

A. SELECT DATE_TRUNC('week', sale_date + INTERVAL '1 day') - INTERVAL '1 day' FROM sales;

B. SELECT DATE_TRUNC('dow', sale_date) FROM sales;

C. SELECT DATE_TRUNC('month', sale_date) FROM sales;

D. SELECT DATE_TRUNC('week', sale_date) FROM sales;

Solution

1. Step 1: Understand DATE_TRUNC('week') behavior

   In PostgreSQL, DATE_TRUNC('week', timestamp) returns the start of the week on Sunday.

2. Step 2: Adjust to Monday start

   Adding 1 day before truncation shifts the week start to Monday, then subtracting 1 day corrects the date.

3. Final Answer:

   SELECT DATE_TRUNC('week', sale_date + INTERVAL '1 day') - INTERVAL '1 day' FROM sales; -> Option A

4. Quick Check:

   Shift date to align week start Monday [OK]

Hint: Shift date by 1 day to start week on Monday [OK]

Common Mistakes:

• Assuming 'week' starts on Monday by default
• Using invalid precision 'dow'
• Truncating to month instead of week