pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatetime operators (was: Re: [SQL] Another Date question)
10+ messages / 5 participants
[nested] [flat]
* Datetime operators (was: Re: [SQL] Another Date question)
@ 1999-12-03 12:23 Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 10+ messages in thread
From: Karel Zak - Zakkr @ 1999-12-03 12:23 UTC (permalink / raw)
To: Andy Lewis <alewis@roundnoon.com>; +Cc: pgsql-hackers <pgsql-hackers@postgreSQL.org>; Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-sql
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)
-----------------------------------------------------------------------
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
@ 1999-12-03 15:48 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 15:50 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
0 siblings, 2 replies; 10+ messages in thread
From: Thomas Lockhart @ 1999-12-03 15:48 UTC (permalink / raw)
To: Karel Zak - Zakkr <zakkr@zf.jcu.cz>; +Cc: Andy Lewis <alewis@roundnoon.com>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
> > 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-12-03 15:50 ` Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1 sibling, 0 replies; 10+ messages in thread
From: Karel Zak - Zakkr @ 1999-12-03 15:50 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-12-03 19:43 ` kaiq@realtyideas.com
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Andy Lewis <alewis@roundnoon.com>
1999-12-03 20:03 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 22:35 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 10+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-03 19:43 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Karel Zak - Zakkr <zakkr@zf.jcu.cz>; Andy Lewis <alewis@roundnoon.com>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
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
>
> ************
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
@ 1999-12-03 19:43 ` Andy Lewis <alewis@roundnoon.com>
1999-12-03 20:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
2 siblings, 1 reply; 10+ messages in thread
From: Andy Lewis @ 1999-12-03 19:43 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Karel Zak - Zakkr <zakkr@zf.jcu.cz>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
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
> >
> > ************
> >
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Andy Lewis <alewis@roundnoon.com>
@ 1999-12-03 20:48 ` kaiq@realtyideas.com
1999-12-03 20:06 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
0 siblings, 1 reply; 10+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-03 20:48 UTC (permalink / raw)
To: Andy Lewis <alewis@roundnoon.com>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Karel Zak - Zakkr <zakkr@zf.jcu.cz>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
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
> > >
> > > ************
> > >
> >
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Andy Lewis <alewis@roundnoon.com>
1999-12-03 20:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
@ 1999-12-03 20:06 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 21:01 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
0 siblings, 1 reply; 10+ messages in thread
From: Thomas Lockhart @ 1999-12-03 20:06 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: Andy Lewis <alewis@roundnoon.com>; Karel Zak - Zakkr <zakkr@zf.jcu.cz>; pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Andy Lewis <alewis@roundnoon.com>
1999-12-03 20:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
1999-12-03 20:06 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-12-03 21:01 ` kaiq@realtyideas.com
0 siblings, 0 replies; 10+ messages in thread
From: kaiq@realtyideas.com @ 1999-12-03 21:01 UTC (permalink / raw)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Andy Lewis <alewis@roundnoon.com>; Karel Zak - Zakkr <zakkr@zf.jcu.cz>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
@ 1999-12-03 20:03 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2 siblings, 0 replies; 10+ messages in thread
From: Thomas Lockhart @ 1999-12-03 20:03 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: Karel Zak - Zakkr <zakkr@zf.jcu.cz>; Andy Lewis <alewis@roundnoon.com>; pgsql-hackers <pgsql-hackers@postgresql.org>; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Datetime operators (was: Re: [SQL] Another Date question)
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Re: Datetime operators (was: Re: [SQL] Another Date question) Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 19:43 ` Re: Datetime operators (was: Re: [SQL] Another Date question) kaiq@realtyideas.com
@ 1999-12-03 22:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 1999-12-03 22:35 UTC (permalink / raw)
To: kaiq@realtyideas.com; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Karel Zak - Zakkr <zakkr@zf.jcu.cz>; Andy Lewis <alewis@roundnoon.com>; pgsql-hackers <pgsql-hackers@postgreSQL.org>; pgsql-sql
<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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~1999-12-03 22:35 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-03 12:23 Datetime operators (was: Re: [SQL] Another Date question) Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 15:48 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 15:50 ` Karel Zak - Zakkr <zakkr@zf.jcu.cz>
1999-12-03 19:43 ` kaiq@realtyideas.com
1999-12-03 19:43 ` Andy Lewis <alewis@roundnoon.com>
1999-12-03 20:48 ` kaiq@realtyideas.com
1999-12-03 20:06 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 21:01 ` kaiq@realtyideas.com
1999-12-03 20:03 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-12-03 22:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox