Retrieve Records for month,datewise group by day in postgres

Question

I have table time_slot where i have columns like date,start_time,end_time,user.

I want to retrieve records like say if I give the month and year along with user, what is the slots available for a particular user day wise for a month. Say user can have 3slots on a day & 0 on a day.

I am using Postgres and my date column is a date, time column is time. I am trying to do this in a Java web application and the date will be picked using a jquery datepicker. From where I'm sending as month, year and user.

Sample Data of table.

Date           start-time   end-time        user
2019-09-01     12:21:34     13:21:34        user1
2019-09-01     14:21:34     15:21:34        user1
2019-09-01     17:21:34     17:21:34        user1
2019-09-03     12:21:34     13:21:34        user1
2019-09-03     12:21:34     13:21:34        user1

I would like to create a query that gives the time-slots of user concating start-time & end-time column and groups the results by date for a month as follows:

Date                count_of_slots
2019-09-01              3
2019-09-02              0
2019-09-03              2

I have tried the below Query.

select distinct kt.start_time,kt.end_time,DATE(kt.slot_date),count(kt.slot_date)
from time_slot as kt
       WHERE date_trunc('month',to_timestamp(kt.start_time, 'yy-mm-dd HH24:MI:SS.MS') + interval '1 day')
         = date_trunc('month',to_timestamp(:startdate, 'yy-mm-dd HH24:MI:SS.MS') + interval '1 day' )
    group by DATE(kt.slot_date)  order by cb.start_time.

After getting result as expected above format, I need to loop through date to get the time-slots for that day and store in json as below.

{
    "Date" : "2019-09-01",
    "count" : "3",
     "time-slot" : [
    "12:21:34 - 13:21:34","14:21:34 - 15:21:34","17:21:34 - 17:21:34"]
}

Any suggestion and leads are welcomed.

It is not clear to me what you are expecting. You showed two different results. Do you simply want to count the records per day and user (the main problem here would be the zero day)? Or do you simply want to get a string concatenated version of your original table? — S-Man
I have modified my Question, simply want to count the records per day and user, along with the zero time-slot day @S-Man — Ullas Sharma
Ok, is there any date range? Otherwise you would get many zero-days I guess... Because you added the JSON part? Do you want to get the time slots as JSON array as well? Where is the count in the JSON object? — S-Man
Are you reall still using Postgres 9.1? That is no longer supported you should plan an upgrade as soon as possible. — a_horse_with_no_name
for a month i need to get the date values. i have updated my JSON.JSON will be framed in JAVA not from SQL. @S-Man — Ullas Sharma

S-Man S-Man · Accepted Answer · 2019-09-09T11:13:07

Disclaimer: You should really upgrade your Postgres version!

demo:db<>fiddle

You need to join a date series against your data set. This can be done using the generate_series() function.

SELECT
    gs::date,
    COUNT(the_date)
FROM
    time_slot ts
RIGHT JOIN
    generate_series('2019-09-01', '2019-09-05', interval '1 day') gs ON ts.the_date = gs
GROUP BY gs

If you want to get the time_slots as well, simply add:

ARRAY_AGG(start_time || ' - ' || end_time) AS time_slot

Retrieve Records for month,datewise group by day in postgres

1 Answers