Re: [GENERAL] Date & Time - Mailing list pgsql-general

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