Please help with a query.. - Mailing list pgsql-sql

I'm stucked..

Say a car travels from X to Y then from Y to Z (and then from Z back
to X but that's not relevant here).

In the table below are the timestamps for each point in various
dates. The complete trip X-Y-Z-X is in the table but each leg on a
separate row.

I'd need to get the time interval X-Y-Z on each date but I cannot rely
on the date (can't make any joins based on the date part of
timestamps). Instead I'd need to find out X-Y and Y-Z pairs with a
minimal "stop" -time at Y and calculate total travel time for those.

The output would be something like this:

dep_date    X_Y_Z_time
2005-01-11  6 hours 15 mins
2005-01-12  5 hours 49 mins
2005-01-13  6 hours 05 mins

(the times above are not correct)

Any help would be greatly appreciated.

Thanks,
Timo



CREATE temp TABLE foo (   pta text,   atime timestamp without time zone,   ptb text,   btime timestamp without time
zone
);



COPY foo (pta, atime, ptb, btime) FROM stdin using delimiters ',';
X,2005-01-11 06:06:00,Y,2005-01-11 08:00:00
X,2005-01-12 06:10:00,Y,2005-01-12 08:00:00
X,2005-01-13 06:14:00,Y,2005-01-13 08:20:00
X,2005-01-14 06:32:00,Y,2005-01-14 08:17:00
X,2005-01-17 06:14:00,Y,2005-01-17 08:02:00
X,2005-01-18 06:10:00,Y,2005-01-18 07:57:00
X,2005-01-19 06:14:00,Y,2005-01-19 08:06:00
X,2005-01-20 06:26:00,Y,2005-01-20 08:13:00
X,2005-01-21 07:52:00,Y,2005-01-21 09:45:00
X,2005-01-24 06:09:00,Y,2005-01-24 07:56:00
X,2005-01-25 06:18:00,Y,2005-01-25 08:07:00
X,2005-01-26 06:05:00,Y,2005-01-26 07:45:00
X,2005-01-27 06:16:00,Y,2005-01-27 07:54:00
X,2005-01-28 06:18:00,Y,2005-01-28 07:59:00
X,2005-01-31 06:50:00,Y,2005-01-31 08:44:00
X,2005-02-01 06:15:00,Y,2005-02-01 07:55:00
X,2005-02-02 06:12:00,Y,2005-02-02 07:59:00
X,2005-02-03 06:31:00,Y,2005-02-03 08:03:00
X,2005-02-04 06:08:00,Y,2005-02-04 07:53:00
X,2005-02-07 06:18:00,Y,2005-02-07 08:09:00
X,2005-02-08 06:02:00,Y,2005-02-08 07:49:00
X,2005-02-09 06:16:00,Y,2005-02-09 08:02:00
X,2005-02-10 06:12:00,Y,2005-02-10 08:07:00
X,2005-02-11 06:13:00,Y,2005-02-11 08:04:00
X,2005-02-14 06:20:00,Y,2005-02-14 08:11:00
X,2005-02-15 06:20:00,Y,2005-02-15 08:06:00
X,2005-02-16 06:11:00,Y,2005-02-16 08:01:00
X,2005-02-17 06:14:00,Y,2005-02-17 07:59:00
X,2005-02-18 06:13:00,Y,2005-02-18 07:59:00
X,2005-02-21 06:15:00,Y,2005-02-21 08:14:00
X,2005-02-22 06:23:00,Y,2005-02-22 08:10:00
Z,2005-01-11 10:15:00,X,2005-01-11 11:58:00
Z,2005-01-12 10:09:00,X,2005-01-12 11:47:00
Z,2005-01-13 10:18:00,X,2005-01-13 12:06:00
Z,2005-01-14 10:15:00,X,2005-01-14 12:06:00
Z,2005-01-17 10:25:00,X,2005-01-17 12:13:00
Z,2005-01-18 10:16:00,X,2005-01-18 11:55:00
Z,2005-01-19 10:15:00,X,2005-01-19 12:00:00
Z,2005-01-20 10:27:00,X,2005-01-20 12:17:00
Z,2005-01-21 11:28:00,X,2005-01-21 13:25:00
Z,2005-01-24 10:17:00,X,2005-01-24 12:05:00
Z,2005-01-25 10:20:00,X,2005-01-25 12:16:00
Z,2005-01-26 10:17:00,X,2005-01-26 12:21:00
Z,2005-01-27 10:30:00,X,2005-01-27 12:38:00
Z,2005-01-28 10:24:00,X,2005-01-28 12:19:00
Z,2005-01-31 10:30:00,X,2005-01-31 12:18:00
Z,2005-02-01 10:19:00,X,2005-02-01 12:22:00
Z,2005-02-02 10:17:00,X,2005-02-02 12:17:00
Z,2005-02-03 10:14:00,X,2005-02-03 12:04:00
Z,2005-02-04 10:18:00,X,2005-02-04 12:16:00
Z,2005-02-07 10:10:00,X,2005-02-07 12:02:00
Z,2005-02-08 10:10:00,X,2005-02-08 11:57:00
Z,2005-02-09 10:18:00,X,2005-02-09 12:06:00
Z,2005-02-10 10:19:00,X,2005-02-10 12:04:00
Z,2005-02-11 10:14:00,X,2005-02-11 11:58:00
Z,2005-02-14 11:11:00,X,2005-02-14 13:04:00
Z,2005-02-15 10:20:00,X,2005-02-15 12:13:00
Z,2005-02-16 10:34:00,X,2005-02-16 12:22:00
Z,2005-02-17 10:20:00,X,2005-02-17 12:09:00
Z,2005-02-18 10:23:00,X,2005-02-18 12:08:00
Z,2005-02-21 10:30:00,X,2005-02-21 12:24:00
Z,2005-02-22 10:19:00,X,2005-02-22 12:13:00
Y,2005-01-11 08:46:00,Z,2005-01-11 09:33:00
Y,2005-01-12 08:40:00,Z,2005-01-12 09:25:00
Y,2005-01-13 08:56:00,Z,2005-01-13 09:45:00
Y,2005-01-14 08:55:00,Z,2005-01-14 09:44:00
Y,2005-01-17 08:48:00,Z,2005-01-17 09:34:00
Y,2005-01-18 08:54:00,Z,2005-01-18 09:43:00
Y,2005-01-19 08:48:00,Z,2005-01-19 09:32:00
Y,2005-01-20 08:58:00,Z,2005-01-20 09:51:00
Y,2005-01-21 10:19:00,Z,2005-01-21 11:08:00
Y,2005-01-24 08:48:00,Z,2005-01-24 09:45:00
Y,2005-01-25 08:50:00,Z,2005-01-25 09:43:00
Y,2005-01-26 08:44:00,Z,2005-01-26 09:26:00
Y,2005-01-27 08:52:00,Z,2005-01-27 09:39:00
Y,2005-01-28 08:46:00,Z,2005-01-28 09:37:00
Y,2005-01-31 09:21:00,Z,2005-01-31 10:05:00
Y,2005-02-01 08:49:00,Z,2005-02-01 09:34:00
Y,2005-02-02 08:45:00,Z,2005-02-02 09:30:00
Y,2005-02-03 08:48:00,Z,2005-02-03 09:36:00
Y,2005-02-04 08:50:00,Z,2005-02-04 09:33:00
Y,2005-02-07 08:47:00,Z,2005-02-07 09:36:00
Y,2005-02-08 08:41:00,Z,2005-02-08 09:22:00
Y,2005-02-09 08:46:00,Z,2005-02-09 09:31:00
Y,2005-02-10 08:48:00,Z,2005-02-10 09:35:00
Y,2005-02-11 08:53:00,Z,2005-02-11 09:34:00
Y,2005-02-14 09:18:00,Z,2005-02-14 10:33:00
Y,2005-02-15 08:53:00,Z,2005-02-15 09:45:00
Y,2005-02-16 08:45:00,Z,2005-02-16 09:29:00
Y,2005-02-17 08:38:00,Z,2005-02-17 09:24:00
Y,2005-02-18 08:42:00,Z,2005-02-18 09:28:00
Y,2005-02-21 08:58:00,Z,2005-02-21 09:52:00
Y,2005-02-22 08:45:00,Z,2005-02-22 09:35:00
\.