Query between two times in redshift

This is something I do regularly, wanting to look at log activity over a 30 minute range

Use datediff and “mins” datepart like this

(datediff(mins,’2016-05-04 15:50′,DATE) between 0 AND 30)

This looks at a 30 minute window around 16:00 hrs

Simple really

Advertisements

In Redshift concatenating null strings

This should be easy

url_domain || url_path || url_query_params  as request

really should work.

BUT if any one of these is null it will deliver a null string

So you need

nvl(url_domain,”) || nvl(url_path,”) || nvl(url_query_params,”) as request

This replaces a Null with a blank.

Why can’t it do this anyway? Who knows.

 

 

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’)