Datetime operators (was: Re: [SQL] Another Date question)

On Thu, 2 Dec 1999, Andy Lewis wrote:

> Hello All!
>
> I'd like to create a table with a datetime field that defaults to +60
> days.
>
> mydate datetime default 'now() +(at)60 days',
> ...

Where is a problem?

You can use "now() + 60"

See:

test=> create table d (x text, d datetime default now() + 60);
CREATE
test=> insert into d values ('hello');
INSERT 506143 1
test=> select * from d;
x |d
-----+----------------------------
hello|Tue Feb 01 00:00:00 2000 CET
(1 row)

But problem is if you want change other datetime value (min,sec,year..etc),
you can use to_char/from_char datetime routines from CVS tree:

select from_char(
to_char('now'::datetime,'MM ') || --- Month
to_char('now'::datetime,'DD')::int +60 || --- Day + 60
to_char('now'::datetime,' YYYY HH24:MI:SS'), --- Year,hour,min,sec
'FMMM FMDD YYYY HH24:MI:SS'); --- Make datetime

----------------------------
Tue Feb 01 13:30:37 2000 CET --- Output datetime
(1 row)

Yes, it is a lot of complicated, but if you a little change this example,
you can use it for increment a arbitrary datetime number (sec,min..).

I agree with your now() + '60 days' is better and easy, but for this we need
new "datetime + text" oprerator, now is date_pli(dateVal, days) only.

My first idea is "to_char" operator as:
datetime + 'to_char format pictures string' example:

datetime + '05 DD 10 HH12'
(add 5days and 10hours to datetime)

For this is parser in to-from_char module.

Or second idea is make it as easy:
datetime + '10 day' or
datetime + '2 year' ..etc.

But I'm not sure what is better or exists it in other SQL.

.... Any comment Thomas?

Karel

----------------------------------------------------------------------
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> http://home.zf.jcu.cz/~zakkr/

Docs: http://docs.linux.cz (big docs archive)
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
-----------------------------------------------------------------------