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?<br>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.