Group by day from timestamp

sql

group-by

timestamp

date-formatting

byAlex Kataev·Sep 10, 2024

To compress timestamps into dates and group events by day, use DATE() to isolate the date component:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
GROUP BY DATE(timestamp);

This aggregates timestamps into unique days and counts events for each date.

Time zones and date formats: watch out for traps!

Time zone normalization

If your application caters to global audiences, you'll need to handle time zone differences. Normalize timestamps to a consistent time zone like so:

-- Support Earth! Change to UTC! 
SET time_zone = '+00:00';

SELECT DATE(CONVERT_TZ(timestamp, @@session.time_zone, '+00:00')) AS day_utc, COUNT(*) AS event_count
FROM events
GROUP BY day_utc;

Custom data formatting

Display dates to make humans happy, not machines. Use DATE_FORMAT():

SELECT DATE_FORMAT(DATE(timestamp), '%d/%m/%Y') AS formatted_day, COUNT(*) AS event_count
FROM events
GROUP BY formatted_day;

Hello UNIX, my old friend

UNIX timestamps are integer-based and require a convertor function: FROM_UNIXTIME(). Say hello:

SELECT DATE(FROM_UNIXTIME(unix_timestamp)) AS day, COUNT(*) AS event_count
FROM events
GROUP BY day;

Your rank sir, as ordered

For ranking posts within each day, ROW_NUMBER() is your comrade in arms:

SELECT DATE(timestamp) AS day,
       ROW_NUMBER() OVER(PARTITION BY DATE(timestamp) ORDER BY timestamp) AS rank,
       content
FROM posts;

Visualising timestamp buckets

Envision a calendar as a collection of vacant slots and timestamps as guests, each eagerly seeking a day to belong:

Guests   Arrival Time   Room No.,    Move-in Date
  🕒     12-03 08:33     🚪 12-03
  🕓     12-03 19:20     🚪 12-03
  🕔     12-04 03:15     🚪 12-04
  🕕     12-05 14:48     🚪 12-05

GROUP BY DAY service:

🕒🕓 ➡️ 🚪 12-03  // Guests arriving in the same day move into the same room
🕔 ➡️ 🚪 12-04
🕕 ➡️ 🚪 12-05

Each room offers a unique timestamp for the same date, simplifying the event torrent.

Zen and the art of SQL grouping

Filtering: striving for clarity

Harmonize GROUP BY with a WHERE clause for solace and filtering:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
WHERE timestamp >= '2023-01-01' AND timestamp < '2023-02-01'
GROUP BY day;

The path will be clear, showing only events happening in January 2023.

Guided sorting

Order your universe by date or event count:

SELECT DATE(timestamp) AS day, COUNT(*) AS event_count
FROM events
GROUP BY day
ORDER BY day DESC; -- or ORDER BY event_count DESC

Sorting doesn't have to be a hair-pulling event!

Intricate dance of intervals

Amplify your skills by grouping custom time intervals:

--Every hour is happy hour with SQL Group By!
SELECT DATE(DATE_ADD(timestamp, INTERVAL 1-HOUR(timestamp) HOUR)) AS start_of_hour,
       COUNT(*) AS event_count
FROM events
GROUP BY start_of_hour;