Confusion regarding sub-second time input
От | Carl Worth |
---|---|
Тема | Confusion regarding sub-second time input |
Дата | |
Msg-id | 15615.36462.638613.274663@scream.east.isi.edu обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: