Menu
I've got a table storing user access/view logs for the webservice I run. It tracks the time as a timestamp though I'm finding when I do aggregate reporting I only care about the day, not the time.
I'm currently running the following query:
SELECT user_logs.timestamp FROM user_logs WHERE user_logs.timestamp >= %(timestamp_1)s AND user_logs.timestamp <= %(timestamp_2)s ORDER BY user_logs.timestamp There are often other where conditions but they shouldn't matter to the question. I'm using Postgres but I'd assume whatever feature is used will work in other languages.
I pull the results into a Python script which counts the number of views per date but it'd make much more sense to me if the database could group and count for me.
How do I strip it down so it'll group by the day and ignore the time?
596
When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.
How to Group by Time in MySQL When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc.
Solution #1 – Group Time with a Pivot Table. The quickest and easiest solution would be to use the Group feature in a Pivot Table. This solution is also the most limiting because you will only be able to group the times in 1 hour increments. The first step is to create a pivot table and add the Date field to the Rows area.
VLOOKUP, in its approximate match mode, allows you to group times into custom intervals of any size. Hi John, thanks for this helpful information. I have data taken every five minutes and I need to aggregate the hourly totals.
SELECT date_trunc('day', user_logs.timestamp) "day", count(*) views FROM user_logs WHERE user_logs.timestamp >= %(timestamp_1)s AND user_logs.timestamp <= %(timestamp_2)s group by 1 ORDER BY 1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
