Inserting Values into Interval

BlackMage <dsd7872@uncw.edu> writes:

I am having a small issue when entering values into the interval field. Say
I want to enter a time of 2:03, two minutes and 3 seconds. When I insert
that into an interval field, it comes up at 02:03:00, 2 hours, 3 minutes.
The only way I've gotten around this so far is by doing 00:02:03. But I was
wondering if there is another of inserting into an interval field where the
values will start at the lower end first, so the result will be 00:02:03
when 2:03 is inserted?

In 8.4 it'll be possible to do that by declaring the interval as MINUTE
TO SECOND, but there's no way around it in existing releases. I'm not
sure I'd care to rely on that anyway, because any time you provide an
interval value that isn't *immediately* tied to a MINUTE TO SECOND
qualifier, it's going to get interpreted in the more standard way.
I think you'd be happier in the long run if you avoid depending on such
an ambiguous data format.

regards, tom lane

Tom Lane-2 wrote:

BlackMage <dsd7872@uncw.edu> writes:

I am having a small issue when entering values into the interval field.
Say
I want to enter a time of 2:03, two minutes and 3 seconds. When I insert
that into an interval field, it comes up at 02:03:00, 2 hours, 3 minutes.
The only way I've gotten around this so far is by doing 00:02:03. But I
was
wondering if there is another of inserting into an interval field where
the
values will start at the lower end first, so the result will be 00:02:03
when 2:03 is inserted?

In 8.4 it'll be possible to do that by declaring the interval as MINUTE
TO SECOND, but there's no way around it in existing releases. I'm not
sure I'd care to rely on that anyway, because any time you provide an
interval value that isn't *immediately* tied to a MINUTE TO SECOND
qualifier, it's going to get interpreted in the more standard way.
I think you'd be happier in the long run if you avoid depending on such
an ambiguous data format.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Well what format should be used then?

The application works like this. Users use a watch time how fast they run
from point A to point B. Afterwards they enter the time taken, say 5 minutes
39 seconds, into a field. The field already checks to make sure the time is
entered in the correct format. After that it takes the time entered in that
fields an enters it into a the db.

So what other field would you enter this into in the db?

--
View this message in context: http://www.nabble.com/Inserting-Values-into-Interval-tp24153731p24164840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 2009-06-23, BlackMage <dsd7872@uncw.edu> wrote:

Well what format should be used then?

The application works like this. Users use a watch time how fast they run
from point A to point B. Afterwards they enter the time taken, say 5 minutes
39 seconds, into a field. The field already checks to make sure the time is
entered in the correct format. After that it takes the time entered in that
fields an enters it into a the db.

So what other field would you enter this into in the db?

postgres likes 00:05:39 and 5m39s
it also accepts 5m39 and 0:5:39

so if you can translate the user entered ':' to an 'm' or
prepend a '0:' to their input you should get the result you desire.

another option may be to divide the interval by 60 :)

00:05:39 is how it will represent the value normally.

On the other hand, could you train them to use 'm' instead of ':' ?