missing something obvious about intervals?

try

select '2005-12-01'::date + (456.5::float || ' seconds')::interval;
?column?
------------------------
2005-12-01 00:07:36.50
(1 row)

---------- Original Message -----------
From: David Rysdam <drysdam@ll.mit.edu>
To: "pg >> Postgres General" <pgsql-general@postgresql.org>
Sent: Mon, 12 Dec 2005 13:00:13 -0500
Subject: [GENERAL] missing something obvious about intervals?

I have a table that has a date field and a "real" field that represents
a number of seconds. I want select the date field + the seconds field.
Just adding it doesn't work. Casting to interval doesn't work.
to_date/to_timestamp don't work. How do I do this?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

------- End of Original Message -------

I knew it had to be something like this, but the search space was just
too big. Thanks!

Jim Buttafuoco wrote:

On Dec 12 01:00, David Rysdam wrote:

I have a table that has a date field and a "real" field that represents
a number of seconds. I want select the date field + the seconds field.
Just adding it doesn't work. Casting to interval doesn't work.
to_date/to_timestamp don't work. How do I do this?

In the "9.9. Date/Time Functions and Operators" chapter of PostgreSQL
Documentation, here's the first example of the first page:

Operator: +
Example : date '2001-09-28' + integer '7'
Result : date '2001-10-05'

--
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."

On Dec 12 08:09, Volkan YAZICI wrote:

On Dec 12 01:00, David Rysdam wrote:

I have a table that has a date field and a "real" field that represents
a number of seconds. I want select the date field + the seconds field.
Just adding it doesn't work. Casting to interval doesn't work.
to_date/to_timestamp don't work. How do I do this?

In the "9.9. Date/Time Functions and Operators" chapter of PostgreSQL
Documentation, here's the first example of the first page:

Operator: +
Example : date '2001-09-28' + integer '7'
Result : date '2001-10-05'

Sorry, just after typing send key I realized that I misunderstood your
question. Here is a working one:

test=> SELECT d + (i || ' seconds')::interval FROM t;

Regards.

--
"We are the middle children of history, raised by television to believe
that someday we'll be millionaires and movie stars and rock stars, but
we won't. And we're just learning this fact," Tyler said. "So don't
fuck with us."

On Mon, Dec 12, 2005 at 13:04:05 -0500,
Jim Buttafuoco <jim@contactbda.com> wrote:

try

select '2005-12-01'::date + (456.5::float || ' seconds')::interval;
?column?
------------------------
2005-12-01 00:07:36.50
(1 row)

You are better off doing a multiply. Something like:
area=> select '2005-12-01'::date + 456.5 * '1 second'::interval;
?column?
------------------------
2005-12-01 00:07:36.50
(1 row)

On Mon, Dec 12, 2005 at 12:34:45PM -0600, Bruno Wolff III wrote:

You are better off doing a multiply. Something like:
area=> select '2005-12-01'::date + 456.5 * '1 second'::interval;
?column?
------------------------
2005-12-01 00:07:36.50
(1 row)

This comes up often enough that it's probably worth adding a built-in
function, especially if it's faster to do the multiply (though
presumably a built-in function could outperform both the multiply and
the more common (4.5::float || ' seconds')::interval form.
--
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

"Jim C. Nasby" <jnasby@pervasive.com> writes:

On Mon, Dec 12, 2005 at 12:34:45PM -0600, Bruno Wolff III wrote:

You are better off doing a multiply. Something like:
area=> select '2005-12-01'::date + 456.5 * '1 second'::interval;
?column?
------------------------
2005-12-01 00:07:36.50
(1 row)

This comes up often enough that it's probably worth adding a built-in
function,

The only case I've noticed seeing on a regular basis is Unix timestamp
conversion (ie, the special case where the date is 1970-01-01); and we
did add a built-in function for that in 8.1.

regards, tom lane