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

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