Confusion regarding sub-second time input - Mailing list pgsql-general

On Mon, 3 Jun 2002 09:39:57 -0400
Neil Conway <neilconway@rogers.com> wrote:
> On Sun, 2 Jun 2002 21:17:50 -0400
> "Gregory Seidman" <gss+pg@cs.brown.edu> wrote:
> > Now I want to create its reverse so I can get a timestamp (with tz)
> from an
> > epoch-based integer. Is there some simple way of doing it?
>
> Try:
>
> select "timestamp"(extract(epoch from now())::int);

Thanks Neil. I had been struggling trying to come up with that
myself. What had stymied me was that I kept trying:

    select timestamp(1023377131);
    ERROR:  TIMESTAMP(1023377131) WITH TIME ZONE precision must be between
    0 and 13

Can anyone explain why that fails when the following three
incantations all succeed?

    select "timestamp"(1023377131);
    select timestamptz(1023377131);
    select "timestamptz"(1023377131);

Next, my new problem is that I have some timestamp data I need to get
into postgresql with sub-second accuracy. All of the date/time types
support 1 microsecond resolution, but I've hit several brick walls
trying to import data. For example:

    select "timestamp"(1023377131.75);
    ERROR:  Bad timestamp external representation '1023377131.75'

Any good reason for this?

Maybe I can workaround the problem by combining the integer and
non-integer portions separately:

    select "timestamp"(1023377131) + '.75 seconds'::interval;
            ?column?
    ------------------------
     2002-06-06 15:25:31.75

That works! But, as it turns out, the external data I happen to have
is in the form "7.5e1" which is causing me fits:

    select '7.5e-1 seconds'::interval;
    ERROR:  Bad interval external representation '7.5e-1 seconds'

I get similar behavior if I use floating-point literals rather than
strings:

    select .75::interval;
      interval
    -------------
     00:00:00.75
    (1 row)

    select 7.5e-1::interval;
    ERROR:  Bad interval external representation '7.5e-1'

Surely, postgresql knows how to grok '7.5e-1' as a floating-point
value? Of course it does:

    select '7.5e-1'::float;
     float8
    --------
       0.75

But, unfortunately, that doesn't seem to help me, as it refuses to
cast a floating-point expression to an interval:

    select '7.5e-1'::float::interval;
    ERROR:  Cannot cast type 'double precision' to 'interval'

For, that matter, I can't event cast an integer expression to an
interval:

    select 1::int::interval;
    ERROR:  Cannot cast type 'integer' to 'interval'

although an integer literal does of course work:

    select 1::interval;
    ERROR:  Cannot cast type 'integer' to 'interval'

Are there any fundamental reasons why the things I've been trying
can't work? Or are there just some code paths that haven't been
properly connected yet?

Anyone have any suggestions for me to get my data in? (For now, I'll
just munge it externally from "7.5e-1" -> ".75" etc. which should at
least get me where I'm going).

-Carl

--
Carl Worth
USC Information Sciences Institute                 cworth@east.isi.edu
3811 N. Fairfax Dr. #200, Arlington VA 22203          703-812-3725