thiagofred thiagofred - 4 months ago 10
SQL Question

Organize date/time table with SQL - Postgres

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?

Answer

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.