Thread: Date & Time
How would I insert the current date & time into a record during an insert, IE such as the Oracle SYSDATE function?? ( insert into test values (123, SYSDATE, 'Name'); Thanks, Dale.
Dale - How about this? Ross test=> create table test (t_id int, start datetime, what text); CREATE test=> insert into test values (123, now(), 'Name'); INSERT 684299 1 test=> select * from test; t_id|start |what ----+----------------------------+---- 123|Tue Nov 30 16:46:07 1999 CST|Name (1 row) test=> insert into test values (456, now(), 'Other'); INSERT 684300 1 test=> select * from test; t_id|start |what ----+----------------------------+---- 123|Tue Nov 30 16:46:07 1999 CST|Name 456|Tue Nov 30 16:46:15 1999 CST|Other (2 rows) test=> -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005 On Tue, Nov 30, 1999 at 04:34:34PM -0600, Dale Anderson wrote: > How would I insert the current date & time into a record during an insert, IE such as the Oracle SYSDATE function?? > > ( insert into test values (123, SYSDATE, 'Name'); > > Thanks, > Dale. > > > > ************ >
"now" performs better in this case: insert into test values (123, 'now','Name'); now() is needed for "default" in create table, because "now" won't work right (a bug/feature ?). see archive in this list On Tue, 30 Nov 1999, Ross J. Reedstrom wrote: > Dale - > How about this? > > Ross > > test=> create table test (t_id int, start datetime, what text); > CREATE > test=> insert into test values (123, now(), 'Name'); > INSERT 684299 1 > test=> select * from test; > t_id|start |what > ----+----------------------------+---- > 123|Tue Nov 30 16:46:07 1999 CST|Name > (1 row) > > test=> insert into test values (456, now(), 'Other'); > INSERT 684300 1 > test=> select * from test; > t_id|start |what > ----+----------------------------+---- > 123|Tue Nov 30 16:46:07 1999 CST|Name > 456|Tue Nov 30 16:46:15 1999 CST|Other > (2 rows) > > test=> > > -- > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005 > > > > On Tue, Nov 30, 1999 at 04:34:34PM -0600, Dale Anderson wrote: > > How would I insert the current date & time into a record during an insert, IE such as the Oracle SYSDATE function?? > > > > ( insert into test values (123, SYSDATE, 'Name'); > > > > Thanks, > > Dale. > > > > > > > > ************ > > > > ************ >
> "now" performs better in this case: > insert into test values (123, 'now','Name'); > > now() is needed for "default" in create table, because > "now" won't work right (a bug/feature ?). > Why does 'now' preform better than now()? -- 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
Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which seems to work the same? Cheers. Ed Loehr kaiq@realtyideas.com wrote: > "now" performs better in this case: > insert into test values (123, 'now','Name'); > > now() is needed for "default" in create table, because > "now" won't work right (a bug/feature ?). > > see archive in this list > > On Tue, 30 Nov 1999, Ross J. Reedstrom wrote: > > > Dale - > > How about this? > > > > Ross > > > > test=> create table test (t_id int, start datetime, what text); > > CREATE > > test=> insert into test values (123, now(), 'Name'); > > INSERT 684299 1 > > test=> select * from test; > > t_id|start |what > > ----+----------------------------+---- > > 123|Tue Nov 30 16:46:07 1999 CST|Name > > (1 row) > > > > test=> insert into test values (456, now(), 'Other'); > > INSERT 684300 1 > > test=> select * from test; > > t_id|start |what > > ----+----------------------------+---- > > 123|Tue Nov 30 16:46:07 1999 CST|Name > > 456|Tue Nov 30 16:46:15 1999 CST|Other > > (2 rows) > > > > test=> > > > > -- > > Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> > > NSBRI Research Scientist/Programmer > > Computer and Information Technology Institute > > Rice University, 6100 S. Main St., Houston, TX 77005 > > > > > > > > On Tue, Nov 30, 1999 at 04:34:34PM -0600, Dale Anderson wrote: > > > How would I insert the current date & time into a record during an insert, IE such as the Oracle SYSDATE function?? > > > > > > ( insert into test values (123, SYSDATE, 'Name'); > > > > > > Thanks, > > > Dale. > > > > > > > > > > > > ************ > > > > > > > ************ > > > > ************
now is a constant, while now() is a function. so, to me, it should be always now unless you really need now(). what's more, in my impression, now() is not in any official doc, it is like a rescue for now. however, in this case, seems there is not much difference.-- oops as for current_timestamp: I bet it is cos the sql92 thing. now and now() is not sql92 standard. but not sure if now or now() are the underlying thing, not sure even whether now and now() are from the same code. Anybody ideas? On Tue, 30 Nov 1999, Bruce Momjian wrote: > > "now" performs better in this case: > > insert into test values (123, 'now','Name'); > > > > now() is needed for "default" in create table, because > > "now" won't work right (a bug/feature ?). > > > > Why does 'now' preform better than now()? > > -- > 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 > > ************ >
Ed Loehr ha scritto: > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which seems towork > the same? > I think it is the same function, both of them return the current date and time. now() should be the internal postgreSQL function. and CURRENT_TIMESTAMP is the exact SQL-92 syntax Jose'
> Ed Loehr ha scritto: > > > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which seemsto work > > the same? > > > > I think it is the same function, both of them return the current date and time. > > now() should be the internal postgreSQL function. > and CURRENT_TIMESTAMP is the exact SQL-92 syntax I am changing my book to use CURRENT_TIMESTAMP rather than now(). -- 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
no, you won't ;-) further testing indicates that current_stamp like current, instead of now/now(). also, I remembered (I tried to check the archive, but failed) now() should not be use in where clause, cos it will hurt performance. Kai On Wed, 1 Dec 1999, Bruce Momjian wrote: > > Ed Loehr ha scritto: > > > > > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which seemsto work > > > the same? > > > > > > > I think it is the same function, both of them return the current date and time. > > > > now() should be the internal postgreSQL function. > > and CURRENT_TIMESTAMP is the exact SQL-92 syntax > > I am changing my book to use CURRENT_TIMESTAMP rather than now(). > > -- > 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 > > ************ >
> no, you won't ;-) > further testing indicates that current_stamp like current, instead of > now/now(). Now I am confused. You say there is a difference between them? -- 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
Here is the test. I did not put it in mail because pine did not like
cut/paste. now I find a way to do it. It looks good!
the conclusion: current_timestamp is "current" -- it should be,
the looks closer than now/now() :-)
###############################################################
test3=> drop table account;
DROP
test3=> CREATE TABLE account (
test3-> act char(1) default 'Y',
test3-> createdfunc DATETIME DEFAULT now(),
test3-> createdcons DATETIME DEFAULT 'now',
test3-> created2cons DATETIME DEFAULT 'current_timestamp',
test3-> createdcurr DATETIME DEFAULT 'current'
test3-> );
CREATE
test3=>
test3=>
test3=> insert into account values('y');
INSERT 283346 1
test3=> insert into account values('1');
INSERT 283347 1
test3=> insert into account (createdcons) values(now());
INSERT 283348 1
test3=> insert into account (createdcons) values(now);
ERROR: Attribute now not found
test3=> insert into account (createdcons) values('now');
INSERT 283349 1
test3=> insert into account (createdcons) values(current);
ERROR: Attribute current not found
test3=> insert into account (createdcons) values('current');
INSERT 283350 1
test3=> insert into account (createdcons) values(current_timestamp);
INSERT 283351 1
test3=> insert into account (createdcons) values('current_timestamp');
INSERT 283352 1
test3=>
test3=> insert into account (createdcons) values(current_timestamp());
ERROR: parser: parse error at or near ")"
test3=> insert into account (createdcons) values(current_timestamp(now));
ERROR: parser: parse error at or near "now"
test3=> insert into account (createdcons) values(current_timestamp('now'));
ERROR: parser: parse error at or near "'"
test3=> insert into account (createdcons) values(now(current_timestamp));
ERROR: No such function 'now' with the specified attributes
test3=>
test3=> select * from account;
act|createdfunc |createdcons |created2cons|createdcurr
---+----------------------------+----------------------------+------------+-----------
y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
1 |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
(7 rows)
test3=> select * from account where createdcons = 'now';
act|createdfunc |createdcons |created2cons|createdcurr
---+----------------------------+----------------------------+------------+-----------
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
(5 rows)
test3=> select * from account where createdcons = now();
act|createdfunc |createdcons |created2cons|createdcurr
---+----------------------------+----------------------------+------------+-----------
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
(5 rows)
test3=> select * from account where createdcons = 'current';
act|createdfunc |createdcons |created2cons|createdcurr
---+----------------------------+----------------------------+------------+-----------
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
(5 rows)
test3=> select * from account where createdcons = 'current_timestamp';
act|createdfunc |createdcons|created2cons|createdcurr
---+----------------------------+-----------+------------+-----------
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
(2 rows)
test3=> select * from account where createdcons = current_timestamp();
ERROR: parser: parse error at or near ")"
test3=> select * from account where createdcons = current_timestamp('now');
ERROR: parser: parse error at or near "'"
test3=> select * from account where createdcons = 'current_timestamp('now')';
ERROR: parser: parse error at or near "now"
##############################################################
On Wed, 1 Dec 1999, Bruce Momjian wrote:
> > Ed Loehr ha scritto:
> >
> > > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which
seemsto work
> > > the same?
> > >
> >
> > I think it is the same function, both of them return the current date and time.
> >
> > now() should be the internal postgreSQL function.
> > and CURRENT_TIMESTAMP is the exact SQL-92 syntax
>
> I am changing my book to use CURRENT_TIMESTAMP rather than now().
>
> --
> 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
>
> ************
>
kaiq@realtyideas.com wrote:
> Here is the test. I did not put it in mail because pine did not like
> cut/paste. now I find a way to do it. It looks good!
> the conclusion: current_timestamp is "current" -- it should be,
> the looks closer than now/now() :-)
I don't think that's quite true, at least on v6.5.2.
Single quotes make a big difference, both on the declaration
in CREATE and on the input params to INSERT. Here's
an example...
CREATE TABLE mytable (
id SERIAL,
note VARCHAR,
dt1 DATETIME DEFAULT CURRENT_TIMESTAMP,
dt2 DATETIME DEFAULT 'CURRENT_TIMESTAMP'
);
INSERT INTO mytable (note) VALUES
('True default datetime values');
INSERT INTO mytable (note,dt1) VALUES
('dt1 input as ''current_timestamp''','current_timestamp');
INSERT INTO mytable (note,dt1) VALUES
('dt1 input as current_timestamp',current_timestamp);
INSERT INTO mytable (note,dt2) VALUES
('dt2 input as ''current_timestamp''','current_timestamp');
INSERT INTO mytable (note,dt2) VALUES
('dt2 input as current_timestamp',current_timestamp);
SELECT * FROM mytable;
The results:
test=> SELECT * FROM mytable;
id|note |dt1 |dt2
--+--------------------------------+----------------------------+----------------------------
1|True default datetime values |Thu Dec 02 13:07:41 1999 CST|current
2|dt1 input as 'current_timestamp'|current |current
3|dt1 input as current_timestamp |Thu Dec 02 13:07:41 1999 CST|current
4|dt2 input as 'current_timestamp'|Thu Dec 02 13:07:41 1999 CST|current
5|dt2 input as current_timestamp |Thu Dec 02 13:07:41 1999 CST|Thu Dec 02 13:07:41 1999 CST
(5 rows)
And with 'current' and 'now' as column types...
DROP SEQUENCE mytable_id_seq;
DROP TABLE mytable;
CREATE TABLE mytable (
id SERIAL,
note VARCHAR,
dt3 DATETIME DEFAULT 'current',
dt4 DATETIME DEFAULT 'now'
);
INSERT INTO mytable (note) VALUES
('True default datetime values');
INSERT INTO mytable (note,dt3) VALUES
('dt3 input as ''current_timestamp''','current_timestamp');
INSERT INTO mytable (note,dt3) VALUES
('dt3 input as current_timestamp',current_timestamp);
INSERT INTO mytable (note,dt4) VALUES
('dt4 input as ''current_timestamp''','current_timestamp');
INSERT INTO mytable (note,dt4) VALUES
('dt4 input as current_timestamp',current_timestamp);
SELECT * FROM mytable;
Results:
test=> SELECT * FROM mytable;
id|note |dt3 |dt4
--+--------------------------------+----------------------------+----------------------------
1|True default datetime values |current |Thu Dec 02 13:10:24 1999 CST
2|dt3 input as 'current_timestamp'|current |Thu Dec 02 13:10:24 1999 CST
3|dt3 input as current_timestamp |Thu Dec 02 13:10:25 1999 CST|Thu Dec 02 13:10:24 1999 CST
4|dt4 input as 'current_timestamp'|current |current
5|dt4 input as current_timestamp |current |Thu Dec 02 13:10:25 1999 CST
(5 rows)
Cheers.
Ed Loehr
>
>
> ###############################################################
> test3=> drop table account;
>
> DROP
> test3=> CREATE TABLE account (
>
> test3-> act char(1) default 'Y',
>
> test3-> createdfunc DATETIME DEFAULT now(),
>
> test3-> createdcons DATETIME DEFAULT 'now',
>
> test3-> created2cons DATETIME DEFAULT 'current_timestamp',
>
> test3-> createdcurr DATETIME DEFAULT 'current'
>
> test3-> );
>
> CREATE
> test3=>
>
> test3=>
>
> test3=> insert into account values('y');
>
> INSERT 283346 1
> test3=> insert into account values('1');
> INSERT 283347 1
> test3=> insert into account (createdcons) values(now());
>
> INSERT 283348 1
> test3=> insert into account (createdcons) values(now);
>
> ERROR: Attribute now not found
> test3=> insert into account (createdcons) values('now');
>
> INSERT 283349 1
> test3=> insert into account (createdcons) values(current);
>
> ERROR: Attribute current not found
> test3=> insert into account (createdcons) values('current');
>
> INSERT 283350 1
> test3=> insert into account (createdcons) values(current_timestamp);
>
> INSERT 283351 1
> test3=> insert into account (createdcons) values('current_timestamp');
>
> INSERT 283352 1
> test3=>
> test3=> insert into account (createdcons) values(current_timestamp());
>
> ERROR: parser: parse error at or near ")"
> test3=> insert into account (createdcons) values(current_timestamp(now));
>
> ERROR: parser: parse error at or near "now"
> test3=> insert into account (createdcons) values(current_timestamp('now'));
>
> ERROR: parser: parse error at or near "'"
> test3=> insert into account (createdcons) values(now(current_timestamp));
>
> ERROR: No such function 'now' with the specified attributes
> test3=>
>
> test3=> select * from account;
>
> act|createdfunc |createdcons |created2cons|createdcurr
> ---+----------------------------+----------------------------+------------+-----------
> y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
> 1 |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> (7 rows)
>
> test3=> select * from account where createdcons = 'now';
>
> act|createdfunc |createdcons |created2cons|createdcurr
> ---+----------------------------+----------------------------+------------+-----------
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> (5 rows)
>
> test3=> select * from account where createdcons = now();
>
> act|createdfunc |createdcons |created2cons|createdcurr
> ---+----------------------------+----------------------------+------------+-----------
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> (5 rows)
>
> test3=> select * from account where createdcons = 'current';
> act|createdfunc |createdcons |created2cons|createdcurr
> ---+----------------------------+----------------------------+------------+-----------
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> (5 rows)
>
> test3=> select * from account where createdcons = 'current_timestamp';
>
> act|createdfunc |createdcons|created2cons|createdcurr
> ---+----------------------------+-----------+------------+-----------
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> (2 rows)
>
> test3=> select * from account where createdcons = current_timestamp();
>
> ERROR: parser: parse error at or near ")"
> test3=> select * from account where createdcons = current_timestamp('now');
>
> ERROR: parser: parse error at or near "'"
> test3=> select * from account where createdcons = 'current_timestamp('now')';
>
> ERROR: parser: parse error at or near "now"
>
>
> ##############################################################
> On Wed, 1 Dec 1999, Bruce Momjian wrote:
>
> > > Ed Loehr ha scritto:
> > >
> > > > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP, which
seemsto work
> > > > the same?
> > > >
> > >
> > > I think it is the same function, both of them return the current date and time.
> > >
> > > now() should be the internal postgreSQL function.
> > > and CURRENT_TIMESTAMP is the exact SQL-92 syntax
> >
> > I am changing my book to use CURRENT_TIMESTAMP rather than now().
> >
> > --
> > 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
> >
> > ************
> >
>
> ************
I agree. my fault. sql92 constant is "global", it should not be in single
quotation mark, while pg's "now" is datetime's constant, it should be in
single quotation mark.
seems we do not need now/now() anymore!!!
seems that the problem/feature is the "current" implementation: it only
compare the beginning of the word. interesting!
On Thu, 2 Dec 1999, Ed Loehr wrote:
> kaiq@realtyideas.com wrote:
>
> > Here is the test. I did not put it in mail because pine did not like
> > cut/paste. now I find a way to do it. It looks good!
> > the conclusion: current_timestamp is "current" -- it should be,
> > the looks closer than now/now() :-)
>
> I don't think that's quite true, at least on v6.5.2.
>
> Single quotes make a big difference, both on the declaration
> in CREATE and on the input params to INSERT. Here's
> an example...
>
>
> CREATE TABLE mytable (
> id SERIAL,
> note VARCHAR,
> dt1 DATETIME DEFAULT CURRENT_TIMESTAMP,
> dt2 DATETIME DEFAULT 'CURRENT_TIMESTAMP'
> );
>
> INSERT INTO mytable (note) VALUES
> ('True default datetime values');
> INSERT INTO mytable (note,dt1) VALUES
> ('dt1 input as ''current_timestamp''','current_timestamp');
> INSERT INTO mytable (note,dt1) VALUES
> ('dt1 input as current_timestamp',current_timestamp);
> INSERT INTO mytable (note,dt2) VALUES
> ('dt2 input as ''current_timestamp''','current_timestamp');
> INSERT INTO mytable (note,dt2) VALUES
> ('dt2 input as current_timestamp',current_timestamp);
>
> SELECT * FROM mytable;
>
> The results:
>
> test=> SELECT * FROM mytable;
> id|note |dt1 |dt2
> --+--------------------------------+----------------------------+----------------------------
> 1|True default datetime values |Thu Dec 02 13:07:41 1999 CST|current
> 2|dt1 input as 'current_timestamp'|current |current
> 3|dt1 input as current_timestamp |Thu Dec 02 13:07:41 1999 CST|current
> 4|dt2 input as 'current_timestamp'|Thu Dec 02 13:07:41 1999 CST|current
> 5|dt2 input as current_timestamp |Thu Dec 02 13:07:41 1999 CST|Thu Dec 02 13:07:41 1999 CST
> (5 rows)
>
> And with 'current' and 'now' as column types...
>
> DROP SEQUENCE mytable_id_seq;
> DROP TABLE mytable;
> CREATE TABLE mytable (
> id SERIAL,
> note VARCHAR,
> dt3 DATETIME DEFAULT 'current',
> dt4 DATETIME DEFAULT 'now'
> );
>
> INSERT INTO mytable (note) VALUES
> ('True default datetime values');
> INSERT INTO mytable (note,dt3) VALUES
> ('dt3 input as ''current_timestamp''','current_timestamp');
> INSERT INTO mytable (note,dt3) VALUES
> ('dt3 input as current_timestamp',current_timestamp);
> INSERT INTO mytable (note,dt4) VALUES
> ('dt4 input as ''current_timestamp''','current_timestamp');
> INSERT INTO mytable (note,dt4) VALUES
> ('dt4 input as current_timestamp',current_timestamp);
>
> SELECT * FROM mytable;
>
> Results:
>
> test=> SELECT * FROM mytable;
> id|note |dt3 |dt4
> --+--------------------------------+----------------------------+----------------------------
> 1|True default datetime values |current |Thu Dec 02 13:10:24 1999 CST
> 2|dt3 input as 'current_timestamp'|current |Thu Dec 02 13:10:24 1999 CST
> 3|dt3 input as current_timestamp |Thu Dec 02 13:10:25 1999 CST|Thu Dec 02 13:10:24 1999 CST
> 4|dt4 input as 'current_timestamp'|current |current
> 5|dt4 input as current_timestamp |current |Thu Dec 02 13:10:25 1999 CST
> (5 rows)
>
> Cheers.
> Ed Loehr
>
> >
> >
> > ###############################################################
> > test3=> drop table account;
> >
> > DROP
> > test3=> CREATE TABLE account (
> >
> > test3-> act char(1) default 'Y',
> >
> > test3-> createdfunc DATETIME DEFAULT now(),
> >
> > test3-> createdcons DATETIME DEFAULT 'now',
> >
> > test3-> created2cons DATETIME DEFAULT 'current_timestamp',
> >
> > test3-> createdcurr DATETIME DEFAULT 'current'
> >
> > test3-> );
> >
> > CREATE
> > test3=>
> >
> > test3=>
> >
> > test3=> insert into account values('y');
> >
> > INSERT 283346 1
> > test3=> insert into account values('1');
> > INSERT 283347 1
> > test3=> insert into account (createdcons) values(now());
> >
> > INSERT 283348 1
> > test3=> insert into account (createdcons) values(now);
> >
> > ERROR: Attribute now not found
> > test3=> insert into account (createdcons) values('now');
> >
> > INSERT 283349 1
> > test3=> insert into account (createdcons) values(current);
> >
> > ERROR: Attribute current not found
> > test3=> insert into account (createdcons) values('current');
> >
> > INSERT 283350 1
> > test3=> insert into account (createdcons) values(current_timestamp);
> >
> > INSERT 283351 1
> > test3=> insert into account (createdcons) values('current_timestamp');
> >
> > INSERT 283352 1
> > test3=>
> > test3=> insert into account (createdcons) values(current_timestamp());
> >
> > ERROR: parser: parse error at or near ")"
> > test3=> insert into account (createdcons) values(current_timestamp(now));
> >
> > ERROR: parser: parse error at or near "now"
> > test3=> insert into account (createdcons) values(current_timestamp('now'));
> >
> > ERROR: parser: parse error at or near "'"
> > test3=> insert into account (createdcons) values(now(current_timestamp));
> >
> > ERROR: No such function 'now' with the specified attributes
> > test3=>
> >
> > test3=> select * from account;
> >
> > act|createdfunc |createdcons |created2cons|createdcurr
> > ---+----------------------------+----------------------------+------------+-----------
> > y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
> > 1 |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:33 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > (7 rows)
> >
> > test3=> select * from account where createdcons = 'now';
> >
> > act|createdfunc |createdcons |created2cons|createdcurr
> > ---+----------------------------+----------------------------+------------+-----------
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > (5 rows)
> >
> > test3=> select * from account where createdcons = now();
> >
> > act|createdfunc |createdcons |created2cons|createdcurr
> > ---+----------------------------+----------------------------+------------+-----------
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > (5 rows)
> >
> > test3=> select * from account where createdcons = 'current';
> > act|createdfunc |createdcons |created2cons|createdcurr
> > ---+----------------------------+----------------------------+------------+-----------
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|Thu Dec 02 08:41:34 1999 CST|current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > (5 rows)
> >
> > test3=> select * from account where createdcons = 'current_timestamp';
> >
> > act|createdfunc |createdcons|created2cons|createdcurr
> > ---+----------------------------+-----------+------------+-----------
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > Y |Thu Dec 02 08:41:34 1999 CST|current |current |current
> > (2 rows)
> >
> > test3=> select * from account where createdcons = current_timestamp();
> >
> > ERROR: parser: parse error at or near ")"
> > test3=> select * from account where createdcons = current_timestamp('now');
> >
> > ERROR: parser: parse error at or near "'"
> > test3=> select * from account where createdcons = 'current_timestamp('now')';
> >
> > ERROR: parser: parse error at or near "now"
> >
> >
> > ##############################################################
> > On Wed, 1 Dec 1999, Bruce Momjian wrote:
> >
> > > > Ed Loehr ha scritto:
> > > >
> > > > > Just curious: anyone have any comment on any practical differences between now() and CURRENT_TIMESTAMP,
whichseems to work
> > > > > the same?
> > > > >
> > > >
> > > > I think it is the same function, both of them return the current date and time.
> > > >
> > > > now() should be the internal postgreSQL function.
> > > > and CURRENT_TIMESTAMP is the exact SQL-92 syntax
> > >
> > > I am changing my book to use CURRENT_TIMESTAMP rather than now().
> > >
> > > --
> > > 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
> > >
> > > ************
> > >
> >
> > ************
>
> I agree. my fault. sql92 constant is "global", it should not be in single
> quotation mark, while pg's "now" is datetime's constant, it should be in
> single quotation mark.
>
> seems we do not need now/now() anymore!!!
>
> seems that the problem/feature is the "current" implementation: it only
> compare the beginning of the word. interesting!
>
Ah, but look at this under current tree:
test=> create table kk (x datetime default current_timestamp);
CREATE
test=> \d kk
Table "kk"
Attribute | Type | Extra
-----------+----------+------------------------------------------
x | datetime | default timestamp(datetime('now'::text))
Internally, it is using 'now'.
--
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
in ""current" implementation", I mean the implementation of "current". OK,
my fault again. I'm beaten :-) thanks
for 6.5.1 that I'm using, it is: "datetime default now()"
> > I agree. my fault. sql92 constant is "global", it should
not be in single
> > quotation mark, while pg's "now" is datetime's constant, it should be in
> > single quotation mark.
> >
> > seems we do not need now/now() anymore!!!
> >
> > seems that the problem/feature is the "current" implementation: it only
> > compare the beginning of the word. interesting!
> >
>
> Ah, but look at this under current tree:
>
> test=> create table kk (x datetime default current_timestamp);
> CREATE
> test=> \d kk
> Table "kk"
> Attribute | Type | Extra
> -----------+----------+------------------------------------------
> x | datetime | default timestamp(datetime('now'::text))
>
> Internally, it is using 'now'.
>
> --
> 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
>
> ************
>
> in ""current" implementation", I mean the implementation of "current". OK,
> my fault again. I'm beaten :-) thanks
>
> for 6.5.1 that I'm using, it is: "datetime default now()"
>
> > Table "kk"
> > Attribute | Type | Extra
> > -----------+----------+------------------------------------------
> > x | datetime | default timestamp(datetime('now'::text))
> >
> > Internally, it is using 'now'.
I think it was changed by Tom to fix a problem with default handling for
dates.
--
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
On 1999-12-01, Bruce Momjian mentioned: > I am changing my book to use CURRENT_TIMESTAMP rather than now(). I recall Thomas muttering some very promising things about the date/time revolution, including conformance to SQL and ISO formats. Perhaps this could go along with it. At least move the standard identifiers to more prominent locations in the docs (than the non-standard ones). Currently, those are very confusing because there are about three different ways to go and it pretty much says "use whatever". -- Peter Eisentraut Sernanders väg 10:115 peter_e@gmx.net 75262 Uppsala http://yi.org/peter-e/ Sweden