Organize date/time table with SQL - Postgres

Question

I have a table like this:

+----+------------+-----------------------+
| id |    date    |       date_time       |
+----+------------+-----------------------+
|  1 | 2016-07-01 | 2016-07-01 06:00:00   |
|  2 | 2016-07-01 | 2016-07-01 06:02:00   |
|  3 | 2016-07-01 | 2016-07-01 06:01:00   |
|  1 | 2016-07-01 | 2016-07-01 16:25:00   |
|  2 | 2016-07-01 | 2016-07-01 18:04:00   |
|  3 | 2016-07-01 | 2016-07-01 12:06:00   |
|  3 | 2016-07-01 | 2016-07-01 17:57:00   |
+----+------------+-----------------------+

I wanna do something like this using sql:

+----+------------+---------------------+------------------------+----------------------+
| id |    date    |     date_time1      |       date_time2       |      date_time3      |
+----+------------+---------------------+------------------------+----------------------+
|  1 | 2016-07-01 | 2016-07-01 06:00:00 | 2016-07-01 16:25:00    |                      |
|  2 | 2016-07-01 | 2016-07-01 06:02:00 | 2016-07-01 18:04:00    |                      |
|  3 | 2016-07-01 | 2016-07-01 06:01:00 | 2016-07-01 17:57:00    | 2016-07-01 17:57:00  |
+----+------------+---------------------+------------------------+----------------------+

The table can have two, three or four times for each day. I was trying to do something with postgres, but I didn't have good results.

Can someone help me?

You should look for PIVOT, and probably Dinamic PIVOT Also you should include your RDBMS name so we can provide more specific help. Please read How-to-Ask And here is a great place to START to learn how improve your question quality and get better answers. — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jul 19, 2016 at 16:22
@JuanCarlosOropeza sorry for format code... I've looked for PIVOT, but it is just for SQL Server, isn't it? I'm working with postgres and I've tried with mysql too. — thiagofred
– thiagofred, Commented Jul 19, 2016 at 17:49
@JuanCarlosOropeza Lets say: semantically the desired result can be achieved by select id, date, array_agg(date_time) from ... group by id, date; (BTW it is the ready to use answer if replace array_agg(date_time) by the series of (array_agg(date_time))[1], (array_agg(date_time))[2], ...). So the matter is in the data representation. — Abelisto
– Abelisto, Commented Jul 19, 2016 at 19:11

Feneric · Accepted Answer · 2016-07-19 20:25:00Z

3

The quickest way to get the information you're looking for is to use PostgreSQL's built-in array and aggregation capabilities. For example, the query:

SELECT id, the_date, ARRAY_AGG(the_timestamp ORDER BY the_timestamp) AS date_time FROM dtpair GROUP BY id, the_date;

produces something pretty close to what you specified (and may already solve your problem) and doesn't have any limitation on number of timestamps per date.

If you need the exact format as you specified above, you can use the same approach but complicate things a little further with a nested query to get it like so:

SELECT id, the_date, date_time[1] AS date_time1, date_time[2] AS date_time2, date_time[3] AS date_time3 FROM (SELECT id, the_date, ARRAY_AGG(the_timestamp ORDER BY the_timestamp) AS date_time FROM dtpair GROUP BY id, the_date) AS arrayquery;

Hope this helps.

answered Jul 19, 2016 at 20:25

Feneric

8611 gold badge12 silver badges16 bronze badges

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

1 Comment

thiagofred Over a year ago

It's works fine. In the MySQL, I'd created a sequence between 1 and 4 for each date_time in the day then I made a simple PIVOT. But this example is really fast. Thank you so much.

Collectives™ on Stack Overflow

Organize date/time table with SQL - Postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related