Grouping timestamps by day, not by time

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:

SELECTuser_logs.timestampFROMuser_logsWHEREuser_logs.timestamp >= %(timestamp_1)sAND user_logs.timestamp <= %(timestamp_2)sORDER BYuser_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?

1

Best Answer

SELECT date_trunc('day', user_logs.timestamp) "day", count(*) viewsFROM user_logsWHERE user_logs.timestamp >= %(timestamp_1)sAND user_logs.timestamp <= %(timestamp_2)sgroup by 1ORDER BY 1

Random Posts

Export a specific page in wordpressAdministrate gem — Pass custom HTML classes into fieldsHow to stop Python closing immediately when executed in Microsoft WindowsWhat is difference between monolithic and micro kernel?Is it possible to configure mongo-express auth to present a login screen and control access to databases?How to delegate facts to localhost from a play targeting remote hostsApache Kafka and message delivery assuranceCan't use std::format in c++20How should I properly convert mp3 to byte array and then convert it back to mp3Reference map in locals in terraform fileIs there any equivalent to JavaScript's forEach in Rust?Use Material UI autocomplete in freeSolo mode with react-hook-formFiddler not capturing traffic from browsersonMouseOver event not working in ReactWhat's the best way to store a phone number in Django models?How does sortWith in Scala work in terms of iterating a tuple?Why am I getting an missing ')' identifier error in the following code?iPhone apps: Can I open an app from a link in a website?How do you delete files older than specific date in Linux?bash script read line by line and echo to file