Subject: Suggestion for Date/Time Functions Section

From: CSN <[email-masked]>
Date: Fri, 19 Aug 2005 21:59:21 -0700 (PDT)

Hi,

I suggestion for the date/time functions in the docs:
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

I was trying to figure out how to do:
update table set next=now() + interval 'table.period
seconds';

I tried subqueries, the concat operator, and anything
else I could think of until I rediscovered the page on
the various ways to cast. Perhaps mix up the examples
in the docs so other ways to do date arithmetic (and
that allow expressions, fields, etc.) are obvious:

date '2001-09-28' + cast((7+7)||' seconds' as
interval);
date '2001-09-28' + ((7+7)||' seconds')::interval);
etc.

Or am I missing an easier way to do date arithmetic
using a table's fields as part of the equation? (I
think mysql has date_add(...), date_subtract(...),
etc.

CSN

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

---

From: Michael Glaesemann <[email-masked]>
Date: Sat, 20 Aug 2005 14:42:48 +0900

> update table set next=now() + interval 'table.period

create table foo (
     foo_id serial not null unique
     , foo_int integer not null
     , foo_timestamp timestamp(0) with time zone not null
) without oids;

insert into foo (foo_int, foo_timestamp) values (10, current_timestamp);
insert into foo (foo_int, foo_timestamp) values (20,  
current_timestamp + interval '20 seconds');
insert into foo (foo_int, foo_timestamp) values (30,  
current_timestamp + interval '30 seconds');
insert into foo (foo_int, foo_timestamp) values (40,  
current_timestamp + interval '40 seconds');

test=# select foo_id, foo_int, foo_timestamp from foo;
foo_id | foo_int |     foo_timestamp
--------+---------+------------------------
       1 |      10 | 2005-08-20 14:39:14+09
       2 |      20 | 2005-08-20 14:39:34+09
       3 |      30 | 2005-08-20 14:39:44+09
       4 |      40 | 2005-08-20 14:39:54+09
(4 rows)

test=# update foo set foo_timestamp = current_timestamp + foo_int *  
interval '1 second';
UPDATE 4
test=# select foo_id, foo_int, foo_timestamp from foo;
foo_id | foo_int |     foo_timestamp
--------+---------+------------------------
       1 |      10 | 2005-08-20 14:39:58+09
       2 |      20 | 2005-08-20 14:40:08+09
       3 |      30 | 2005-08-20 14:40:18+09
       4 |      40 | 2005-08-20 14:40:28+09
(4 rows)

Is that what you want?

Michael Glaesemann
grzm myrealbox com

---

From: Stephan Szabo <[email-masked]>
Date: Fri, 19 Aug 2005 22:44:30 -0700 (PDT)

> date '2001-09-28' + cast((7+7)||' seconds' as

I'd suggest something like
 date '2001-09-28' + (7+7) * interval '1 second';
instead of using text concatenation.