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

to_char(DATE,’HH24:MI’)

 

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s