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**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Jul 19 '16 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, Jul 19 '16 at 17:49
Check this for [postgres](http://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql) and for [mysql](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — Juan Carlos Oropeza, Jul 19 '16 at 18:32
Actually PIVOT relates to the data representation, not to the data selection. IMO... — Abelisto, Jul 19 '16 at 18:47
@Abelisto Dont understand how your comment is related to this question. — Juan Carlos Oropeza, Jul 19 '16 at 18:52
@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, Jul 19 '16 at 19:11
There isn't a specific question here, but I would look at Postgres ARRAYs. — Andrew Lazarus, Jul 19 '16 at 20:06

score 3 · Accepted Answer · answered Jul 19 '16 at 20:25

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.

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. — thiagofred, Jul 19 '16 at 20:46

Organize date/time table with SQL - Postgres

1 Answers1