Thread: Another Date question
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
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 > > > ************ >
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 certainlythe 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 mayalso 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
> 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, Pennsylvania19026
Hi, No need of functions. You can do it like this: CREATE TABLE mytable (mydate datetime DEFAULT now() + '@60 days'); Regards, Stoyan Genov > 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 > > > ************ >
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)
-----------------------------------------------------------------------
I knew I was close, this example should be used in the documentation. I'd certainly be helpful! Thanks. Andy On Fri, 3 Dec 1999, Stoyan Genov wrote: > Hi, > No need of functions. You can do it like this: > > CREATE TABLE mytable (mydate datetime DEFAULT now() + '@60 days'); > > Regards, > Stoyan Genov > > > 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 > > > > > > ************ > > > >
> > 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
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
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 > > ************ >
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 > > > > ************ > > >
> 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
<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