Postgresql: Looping through a date_trunc generated group

Question

I've got some records on my database that have a 'createdAt' timestamp.

What I'm trying to get out of postgresql is those records grouped by 'createdAt'

So far I've got this query:

SELECT date_trunc('day', "updatedAt") FROM goal GROUP BY 1

Which gives me:

+---+------------+-------------+ | date_trunc | +---+------------+-------------+ | Sep 20 00:00:00 | +---+------------+-------------+

Which are the days where the records got created.

My question is: Is there any way to generate something like:

| Sep 20 00:00:00 | | id | name | gender | state | age | |----|-------------|--------|-------|-----| | 1 | John Kenedy | male | NY | 32 | | | | Sep 24 00:00:00 | | | | id | name | gender | state | age | |----|-------------|--------|-------|-----| | 1 | John Kenedy | male | NY | 32 | | 2 | John De | male | NY | 32 |

That means group by date_trunc and select all the columns of those rows?

Thanks a lot!

Please try SELECT date_trunc('day', "updatedAt"), name, gender, state, age FROM goal GROUP BY 1,2,3 . It will not provide as the structure, you expect, but will "group by date_trunc and select all the columns ". — Arihant
– Arihant, Commented Oct 1, 2018 at 3:56
Just tried it and it works! Thanks a lot! Is there any way to make it look like the structure I described above or is that something postres just does not do. — XAE
– XAE, Commented Oct 1, 2018 at 4:44
You won't get that structure in a simple and single query. This is the default. — Arihant
– Arihant, Commented Oct 1, 2018 at 5:01

Arihant · Accepted Answer · 2018-10-01 05:03:35Z

1

Please try SELECT date_trunc('day', "updatedAt"), name, gender, state, age FROM goal GROUP BY 1,2,3. It will not provide as the structure, you expect, but will "group by date_trunc and select all the columns ".

answered Oct 1, 2018 at 5:03

Arihant

7455 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

XAE Over a year ago

Thanks a lot! Can you point me to where should I look for to get something like the structure I described?

Arihant Over a year ago

Which programming language are you using? You can loop through the distinct date and get the set of data you require.

XAE Over a year ago

NodeJS and yes that is what I was thinking, was just wondering if there was a way of doing something like that via postgres.

Collectives™ on Stack Overflow

Postgresql: Looping through a date_trunc generated group

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related