Thread: How to perform calculations on 'time' and 'interval' datatypes
Hello, I searched the pgsql-general archives and found no answer to my question, so I decided to ask it here. For my acquitance-project in PostgreSQL I've chosen kind of bus/tram timetable application I store departures from starting terminal/stop in a table I want to calculate departures for following stops adding known number of minutes en route and then store it in the table as well. Like: bus leaves at 4:20 goes 6 minutes to the next stop, then 10 to another and so on. I have only times of departure for the starting point of the line and want to calculate departure for all the stops. I figured I must give the intervals in seconds for the following to work, but there is this problem with type mismatch I do for example a SELECT departure1, departure+600::intervals as departure2, departure+1000::intervals as departure3, ... and then departure1 is of time type, but departure2 and following are of interval type However, I would like them to be also of time type, so I could stuff them in the table. It's OK for displaying, but when I try to do an update I get ERROR: Attribute 'departure' os of type 'time' but expression is of type 'interval' You will need to rewrite or cast the expression. So, casting does not work (ERROR: Cannot cast type 'time' to 'interval'). Any ideas about rewriting it? Thanks in advance, Marcin
On Tue, Nov 28, 2000 at 02:16:58PM +0100, Marcin Bajer wrote: > I do for example a > SELECT departure1, departure+600::intervals as departure2, > departure+1000::intervals as departure3, ... > > and then departure1 is of time type, but departure2 and following are of > interval type Would timestamp be recommended over time? Also, interval can handle things in the form 'x hour y min'. Example, from something I'm working on now: tv=> \d shows Table "shows" Attribute | Type | Modifier -------------+-----------+---------------------------------------------------- title | text | not null story | text | channel | integer | not null start | timestamp | not null length | interval | not null status | char(1) | default 'U' description | text | comment | text | number | integer | not null default nextval('shows_number_seq'::text) Indices: shows_number_key, shows_start_channel, shows_title_story Constraint: (strpos('ARUW'::text, (status)::text) > 0) tv=> select start from shows where number=1; start ------------------------ 2000-11-22 10:00:00-06 (1 row) tv=> update shows set start=start + interval '5 min' where number=1; UPDATE 1 tv=> select start from shows where number=1; start ------------------------ 2000-11-22 10:05:00-06 (1 row) mrc -- Mike Castle Life is like a clock: You can work constantly dalgoda@ix.netcom.com and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Hi Marcin, You could try something like this: SELECT departure1, departure1 + '6 minutes'::interval as departure2, departure1 + '16 minutes'::interval as departure3, .... However, I think you'll find that for any sort of a lengthy route, this will become very quickly unworkable. Instead, you might consider setting up your tables something like this: create table departure ( route int4, departure time, primary key(route, departure) ); create table routes ( route int4 constraint route_fkey_check references departure (route) on delete cascade on update cascade, stopno int4, timefromstart interval, primary key(route, stopno) ); Then throw in some random data: insert into departure values (1, '12:00'); insert into routes values (1, 1, '6 minutes'); insert into routes values (1, 2, '16 minutes'); insert into routes values (1, 3, '25 minutes'); insert into routes values (1, 4, '31 minutes'); Then you can query your database like this: SELECT r.route, r.stopno, d.departure + r.timefromstart AS "Stop Time" FROM routes r, departure d WHERE r.route=d.route AND d.route=1 ORDER BY r.route, r.stopno; ... which will produce a meaningful timetable like this: route | stopno | Stop Time -------+--------+----------- 1 | 1 | 12:06 1 | 2 | 12:16 1 | 3 | 12:25 1 | 4 | 12:31 Hope this helps. Francis Solomon > Hello, > > I searched the pgsql-general archives and found no answer > to my question, so I decided to ask it here. > > For my acquitance-project in PostgreSQL I've chosen > kind of bus/tram timetable application > > I store departures from starting terminal/stop in a table > I want to calculate departures for following stops > adding known number of minutes en route > and then store it in the table > as well. > Like: bus leaves at 4:20 goes 6 minutes to the next stop, then 10 to > another > and so on. I have only times of departure for the starting > point of the > line > and want to calculate departure for all the stops. > I figured I must give the intervals in seconds for the following to > work, > but there is this problem with type mismatch > > I do for example a > SELECT departure1, departure+600::intervals as departure2, > departure+1000::intervals as departure3, ... > > and then departure1 is of time type, but departure2 and > following are of > interval type > > However, I would like them to be also of time type, so I could stuff > them in the table. > It's OK for displaying, but when I try to do an update I get > ERROR: Attribute 'departure' os of type 'time' but expression > is of type > 'interval' > You will need to rewrite or cast the expression. > > So, casting does not work (ERROR: Cannot cast type 'time' to > 'interval'). > Any ideas about rewriting it? > > Thanks in advance, > Marcin >