Hello All!

I'd like to create a table with a datetime field that defaults to +60
days.

mydate datetime default 'now() +@60 days',
...

Doesn't seem to work.

What am I missing?

Thanks

Andy


Thu, 2 Dec 1999 21:56:52 -0600 (CST)
Andy Lewis <alewis@roundnoon.com>
Another Date question
your may need to make a simple function. see function in doc.

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',
> ...
> 
> Doesn't seem to work.
> 
> What am I missing?
> 
> Thanks
> 
> Andy
> 
> 
> ************
> 


Fri, 3 Dec 1999 00:13:21 -0600 (CST)
<kaiq@realtyideas.com>
Re: [SQL] Another Date question
I finished the book (version Nov 30). It is a very good one. clear and
straight to the point. 
some comments:
A)
here are  my 2-cents:
1)I found a type in p55 line 4552: "than" should be "that".
2) when I read it, I feel the data in the example should be given.
   i.e., all the inserts should be given (esp. on p58).
B)
here is a big question: why you say that normalization is good for
data retreval (page 43 )? If my memory not wrong, it is ONLY good for data
update/insert/delete.

C) here is the main concern: sql92. 
1) page3, after talk oss, the book should  mention sql92;
          and treat the whole book accordingly (see next).
2) page10, \g should not be used as recommened one. ; should be used. 
        this is not sql92 (?), but ";" is certainly the most used. 
3) page19: single quotation mark should be mentioned as the prefered 
      one. (sql92 ).
4) page23: /*  */ should be mentioned that it is not sql92.
5) page27: != is not sql92.
6) page28: regex is not sql92, so, should be considered ONLY 
           after tried like ;
7) page31: in "case", should indicate that "end" is not needed in sql92,
     and thus very likely later version of pg may also not need end. 
8) page61: oid should be used in caution, because, in short,  it is not in
      sql92. 

in short, all non-necessary non-sql92 features should be put into
secondary position. all important feature that is not sql92 should
be pointed out.
 
we OSS/PG people should differentiate/advertize ourselves as
standard-keeper. so, this book should keep this as the main topic.
It will NOT confuse new user/beginner, if handled consistantly. 
Also, it will add the worth-value for old pg user for sql92 info.


hope this book will not like all other vendor-oriented books where
as if sql86/92 never exists! sql86/92 are our friends, even family member!
 

Kai


Fri, 3 Dec 1999 00:46:06 -0600 (CST)
<kaiq@realtyideas.com>
the book and sql92
> I finished the book (version Nov 30). It is a very good one. clear and
> straight to the point. 
> some comments:
> A)
> here are  my 2-cents:
> 1)I found a type in p55 line 4552: "than" should be "that".

Fixed.  Thanks.

> 2) when I read it, I feel the data in the example should be given.
>    i.e., all the inserts should be given (esp. on p58).

Well, the issue here is that I really have not developed enough data to
show a meaningful output for this, and I don't think it is worth the
major space needed to insert it.  That is why I left it out.

> B)
> here is a big question: why you say that normalization is good for
> data retreval (page 43 )? If my memory not wrong, it is ONLY good for data
> update/insert/delete.

Changed to 'data lookup' because without normalization, you can't lookup
information about a specific customer very easily.

> 
> C) here is the main concern: sql92. 
> 1) page3, after talk oss, the book should  mention sql92;
>           and treat the whole book accordingly (see next).

I disagree.  This is an intro/concepts.  I emphasize standard SQL ways
as much as possible.  Yesterday I changed now() to CURRENT_TIMESTAMP for
this reason, and if you see any other cases where I use non-standard
more, let me know.  However, this is just to get them started.  They
want results.  Worrying about standard SQL at this point is not a good
idea.  Get them started first.  I emphasize that, but don't want to be
pointing out saying "don't do this, and don't do that" at this point.


> 2) page10, \g should not be used as recommened one. ; should be used. 
>         this is not sql92 (?), but ";" is certainly the most used. 

\g used very rarely, but it should be shown to show consistency with
other psql commands.

> 3) page19: single quotation mark should be mentioned as the prefered 
>       one. (sql92 ).

single mentioned first.

> 4) page23: /*  */ should be mentioned that it is not sql92.

Mentioned last.

> 5) page27: != is not sql92.

Many db's support this.

> 6) page28: regex is not sql92, so, should be considered ONLY 
>            after tried like ;

Again, see above.

> 7) page31: in "case", should indicate that "end" is not needed in sql92,
>      and thus very likely later version of pg may also not need end. 

No need.

> 8) page61: oid should be used in caution, because, in short,  it is not in
>       sql92. 


> 
> in short, all non-necessary non-sql92 features should be put into
> secondary position. all important feature that is not sql92 should
> be pointed out.
>  
> we OSS/PG people should differentiate/advertize ourselves as
> standard-keeper. so, this book should keep this as the main topic.
> It will NOT confuse new user/beginner, if handled consistantly. 
> Also, it will add the worth-value for old pg user for sql92 info.
> 
> 
> hope this book will not like all other vendor-oriented books where
> as if sql86/92 never exists! sql86/92 are our friends, even family member!

That is not the scope of this book.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 3 Dec 1999 02:56:27 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [SQL] the book and sql92

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)
-----------------------------------------------------------------------


Fri, 3 Dec 1999 13:23:17 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Datetime operators (was: Re: [SQL] Another Date question)
> > 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 have enclosed your default values into a large string, rather than
letting them be evaluated as an expression:

  mydate datetime default (now() + '60 days')

where the outer parens are optional.

>      datetime + '10 day' or
>      datetime + '2 year' ..etc.
> But I'm not sure what is better or exists it in other SQL.

afaik this is the simplest and most direct way to do it. Note that you
can include other timespan fields in the constant:

  mydate datetime default (now() + '60 days 10 hours')

HTH

                      - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 03 Dec 1999 15:48:35 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Datetime operators (was: Re: [SQL] Another Date question)

On Fri, 3 Dec 1999, Thomas Lockhart wrote:

> afaik this is the simplest and most direct way to do it. Note that you
> can include other timespan fields in the constant:
> 
>   mydate datetime default (now() + '60 days 10 hours')
> 

Sorry, sooooorry, I total bad see in source and docs, my previous letter
is good for /dev/null only... 

					Again sorry
			                 Karel 


Fri, 3 Dec 1999 16:50:18 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: Datetime operators (was: Re: [SQL] Another Date question)
why 

create table mymy (mydate datetime default (now() + '60 days'::timespan )); 

does not work?

On Fri, 3 Dec 1999, Thomas Lockhart wrote:

> > > 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 have enclosed your default values into a large string, rather than
> letting them be evaluated as an expression:
> 
>   mydate datetime default (now() + '60 days')
> 
> where the outer parens are optional.
> 
> >      datetime + '10 day' or
> >      datetime + '2 year' ..etc.
> > But I'm not sure what is better or exists it in other SQL.
> 
> afaik this is the simplest and most direct way to do it. Note that you
> can include other timespan fields in the constant:
> 
>   mydate datetime default (now() + '60 days 10 hours')
> 
> HTH
> 
>                       - Thomas
> 
> -- 
> Thomas Lockhart				lockhart@alumni.caltech.edu
> South Pasadena, California
> 
> ************
> 


Fri, 3 Dec 1999 13:43:06 -0600 (CST)
<kaiq@realtyideas.com>
Re: Datetime operators (was: Re: [SQL] Another Date question)
Remove the ::timespan and it will.

Andy

On Fri, 3 Dec 1999 kaiq@realtyideas.com wrote:

> why 
> 
> create table mymy (mydate datetime default (now() + '60 days'::timespan )); 
> 
> does not work?
> 
> On Fri, 3 Dec 1999, Thomas Lockhart wrote:
> 
> > > > 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 have enclosed your default values into a large string, rather than
> > letting them be evaluated as an expression:
> > 
> >   mydate datetime default (now() + '60 days')
> > 
> > where the outer parens are optional.
> > 
> > >      datetime + '10 day' or
> > >      datetime + '2 year' ..etc.
> > > But I'm not sure what is better or exists it in other SQL.
> > 
> > afaik this is the simplest and most direct way to do it. Note that you
> > can include other timespan fields in the constant:
> > 
> >   mydate datetime default (now() + '60 days 10 hours')
> > 
> > HTH
> > 
> >                       - Thomas
> > 
> > -- 
> > Thomas Lockhart				lockhart@alumni.caltech.edu
> > South Pasadena, California
> > 
> > ************
> > 
> 


Fri, 3 Dec 1999 13:43:32 -0600 (CST)
Andy Lewis <alewis@roundnoon.com>
Re: Datetime operators (was: Re: [SQL] Another Date question)
> why
>   create table mymy (mydate datetime
>     default (now() + '60 days'::timespan ));
> does not work?

Uh, I think it does, right?

                   - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 03 Dec 1999 20:03:27 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Datetime operators (was: Re: [SQL] Another Date question)
> I feel pain about it :-) because that was what I tried, and then, 
> since it did not work, I assumed "default" did not accept expressions.

No pain here:

postgres=> create table mymy (mydate datetime
postgres->   default (now() + '60 days'::timespan ));
CREATE

What version are you running??

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Fri, 03 Dec 1999 20:06:13 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Datetime operators (was: Re: [SQL] Another Date question)
I know, thanks to you, but why? It supposes to work. seems things within
default's parenthesis  are different for no reason.

I mean, 
  insert into mymy values( now() +  '60 days'::timespan );
works fine. usually the more strict one (diligitantly casted one)
always works. 

I feel pain about it :-) because that was what I tried, and then, since it
did not work, I assumed "default" did not accept expressions.



On Fri, 3 Dec 1999, Andy Lewis wrote:

> Remove the ::timespan and it will.
> 
> Andy
> 
> On Fri, 3 Dec 1999 kaiq@realtyideas.com wrote:
> 
> > why 
> > 
> > create table mymy (mydate datetime default (now() + '60 days'::timespan )); 
> > 
> > does not work?
> > 
> > On Fri, 3 Dec 1999, Thomas Lockhart wrote:
> > 
> > > > > 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 have enclosed your default values into a large string, rather than
> > > letting them be evaluated as an expression:
> > > 
> > >   mydate datetime default (now() + '60 days')
> > > 
> > > where the outer parens are optional.
> > > 
> > > >      datetime + '10 day' or
> > > >      datetime + '2 year' ..etc.
> > > > But I'm not sure what is better or exists it in other SQL.
> > > 
> > > afaik this is the simplest and most direct way to do it. Note that you
> > > can include other timespan fields in the constant:
> > > 
> > >   mydate datetime default (now() + '60 days 10 hours')
> > > 
> > > HTH
> > > 
> > >                       - Thomas
> > > 
> > > -- 
> > > Thomas Lockhart				lockhart@alumni.caltech.edu
> > > South Pasadena, California
> > > 
> > > ************
> > > 
> > 
> 


Fri, 3 Dec 1999 14:48:09 -0600 (CST)
<kaiq@realtyideas.com>
Re: Datetime operators (was: Re: [SQL] Another Date question)
6.5.1. time to upgrade ;-) thanks.


On Fri, 3 Dec 1999, Thomas Lockhart wrote:

> > I feel pain about it :-) because that was what I tried, and then, 
> > since it did not work, I assumed "default" did not accept expressions.
> 
> No pain here:
> 
> postgres=> create table mymy (mydate datetime
> postgres->   default (now() + '60 days'::timespan ));
> CREATE
> 
> What version are you running??
> 
>                      - Thomas
> 
> -- 
> Thomas Lockhart				lockhart@alumni.caltech.edu
> South Pasadena, California
> 


Fri, 3 Dec 1999 15:01:51 -0600 (CST)
<kaiq@realtyideas.com>
Re: Datetime operators (was: Re: [SQL] Another Date question)
<kaiq@realtyideas.com> writes:
> why 
> create table mymy (mydate datetime default (now() + '60 days'::timespan )); 
> does not work?

I believe :: casts are broken in default expressions in 6.5.*.  They are
fixed in current sources (which is what Thomas probably tried) --- but
in the meantime, that expression will work fine without the cast...

			regards, tom lane

Fri, 03 Dec 1999 17:35:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Datetime operators (was: Re: [SQL] Another Date question) 