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() +@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@zf.jcu.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)
-----------------------------------------------------------------------