This is quite straight forward but seems to cause confusion on the internet
Time to the nearest 10 minutes
TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 10),1)*10),’09’)) AS mins10
so you get
12:00 – 12:00
12:01 – 12:00
12:03 – 12:00
12:11 – 12:10
This imports into excel as a time
Time to the nearest 5 minutes
Just change the multiplier
TO_CHAR(DATE,’HH’) || ‘:’ || trim(TO_CHAR((ROUND((DATEPART (MINUTE,DATE) / 5),1)*5),’09’)) AS mins5
Time to the nearest minute
date_trunc(‘minute’, Date)
Time to the nearest hour
date_trunc(‘hour’, Date)
Day of the week
date_trunc(‘week’, Date)
This puts you to the nearest Monday in that week which is more useful than the week number
Time only
to_char(DATE,’HH24:MI’)