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
>