CODEWITHSUNDEEP
mariadb
DD DD
DD DD

Reputation: 1238

How to group by after date add in mariadb

I have data below.

id: 1, created_at: "2022-01-01 02:00:00"
id: 2, created_at: "2022-02-02 23:00:00",
...

I need to group by created_at ("%Y-%m") after shift DateTime by adding 2hours.
Therefore after shifting hours, the date must be changed.

id: 1, created_at: "2022-01-01 04:00:00"
id: 2, created_at: "2022-02-03 01:00:00",
...

I can add hours using date_add function. But I don't know how to group by "%Y-%m" using created_at added hours in one query. Could you help me this?

select *, date_add(created_at, interval 2 hour) cr from clients where created_at between "${fromUtc}" and "${toUtc} order by created_at

Upvotes: 1

Views: 42

Answers (1)

Asgar
Asgar

Reputation: 2422

Does this help?


SELECT 
  *,
  DATE_ADD(created_at, INTERVAL 2 HOUR) cr 
FROM
  clients 
WHERE created_at BETWEEN "${fromUtc}" 
  AND "${toUtc}" 
GROUP BY YEAR(cr), 
  MONTH(cr)  
  ORDER BY created_at

Upvotes: 1

Related Questions