Thread: Feature request - Add microsecond as a time unit for interval

The interval datatype can go to microsecond precision though currently 
the smallest unit is seconds.  Microseconds are represented as decimal 
places, eg 5 microseconds is 0.000005 seconds.

To insert microseconds I have to use the following line, ($1*0.000001 || 
' seconds')::interval
Being able to specify microseconds as a unit would avoid hacks like this 
and improve support for sub-second intervals.


David

David Tulloh <david.tulloh@anu.edu.au> writes:
> To insert microseconds I have to use the following line, ($1*0.000001 || 
> ' seconds')::interval

Actually, the preferred way to do that is to use the numeric-times-interval
operator, eg

regression=# select 7 * '0.000001 second'::interval;   ?column?
-----------------00:00:00.000007
(1 row)

This generalizes to any scale factor you care to use, eg fortnights...
so I don't see a pressing need to add microseconds.
        regards, tom lane

On Thu, Feb 09, 2006 at 10:30:30AM -0500, Tom Lane wrote:
> David Tulloh <david.tulloh@anu.edu.au> writes:
> > To insert microseconds I have to use the following line, ($1*0.000001 || 
> > ' seconds')::interval
> 
> Actually, the preferred way to do that is to use the numeric-times-interval
> operator, eg
> 
> regression=# select 7 * '0.000001 second'::interval;
>     ?column?
> -----------------
>  00:00:00.000007
> (1 row)
> 
> This generalizes to any scale factor you care to use, eg fortnights...
> so I don't see a pressing need to add microseconds.

Something that's always struck me about intervals in PostgreSQL is the
how obtuse it is to generate one. Is there some reasonable way we could
add support for something like interval('7 microseconds')? Or perhaps
seconds(0.000007)?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

> This generalizes to any scale factor you care to use, eg fortnights...
> so I don't see a pressing need to add microseconds.

Perhaps an argument for adding microseconds to interval declarations is 
that you can extract them using extract()...  Those two lists of allowed  scales should be the same, no?

Chris