Thread: 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)
-----------------------------------------------------------------------
> > 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 > > ************ >
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 >
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 > > > > > > ************ > > > > > >
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
> 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
<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