Nearest 5 or 10 minutes in redshift sql

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



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s