From: Timo Tuomi <ttuomi@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Please help with a query..
Date: 11 Apr 2006 02:34:22 -0700
Message-ID: <1144748062.471716.171260@u72g2000cwu.googlegroups.com> (raw)
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
\.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ttuomi@gmail.com
Subject: Re: Please help with a query..
In-Reply-To: <1144748062.471716.171260@u72g2000cwu.googlegroups.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox