CODEWITHSUNDEEP
databricksazure-databricksdatabricks-sqldatabricks-connectdatabricks-community-edition
Rita Kumari
Rita Kumari

Reputation: 53

How to group by 30 minutes interval in Databricks SQL

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.

Upvotes: 2

Views: 1486

Answers (2)

Jian
Jian

Reputation: 11

A better way is to use window grouping function in databricks sql rather than calculating with time funcs: https://docs.databricks.com/en/sql/language-manual/functions/window.html

Upvotes: 1

Saideep Arikontham
Saideep Arikontham

Reputation: 6114

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;

enter image description here

  • 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;

enter image description here

Upvotes: 1

Related Questions