Thread: Inserting Values into Interval
Hey, 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? This will make developing application so much easier if there is, so thanks. -- View this message in context: http://www.nabble.com/Inserting-Values-into-Interval-tp24153731p24153731.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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 ':' ?