How to group by 30 minutes interval in Databricks SQL

Question

This is the function I was using to group by 30 mins of intervals in SQL:

convert(time(0),dateadd(minute,(datediff(minute,0,a.Datetime)/30)*30,0))

where for example Datetime is 2023-03-09 00:26:01.6830000 grouped as 00:00:00.

First column values are the Datetime and second is after grouping it to 30 mins intervals

I need equivalent function in Databricks SQL.

Answer 1

The following query works for the data in sql server:

select  *,convert(time(0),dateadd(minute,(datediff(minute,0,t1.dt)/30)*30,0)) from t1;

• Its equivalent in databricks sql is as shown in the below query:

select  *,date_format(dateadd(minute,floor(datediff(minute,'1900-01-01',t1.dt)/30)*30,'1900-01-01'),'HH:mm:ss') from t1;