RE: How to perform calculations on 'time' and 'interval' datatypes - Mailing list pgsql-general

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
>