pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Default timestamp value
5+ messages / 4 participants
[nested] [flat]

* Default timestamp value
@ 2000-06-05 07:34  Rob S. <rslifka@home.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Rob S. @ 2000-06-05 07:34 UTC (permalink / raw)
  To: pgsql-sql

As a precursor to this question, I read:

	CREATE TABLE
	Date/Time Types (and corresponding input/output)
	Date/Time Functions

...but I still don't see how to have the default value of a timestamp to be
the time at which the individual record is inserted.  I just get the time I
created the table.  Specifically, what to put where the '?' is at.

... "TimeDate" TIMESTAMP DEFAULT ? ...

I tried 'now' and 'current' but it just makes the default value the time
when I create the table, and 'current' gives me the word 'current' back in
psql, so I imagine I'm not taking that in quite the right way =)  I can see
why it does this for both of these, althought in the docs, it says current
is current time, deferred, but I dunno what that means.

Any help or pointers to a nice resource?  IMHO, the PG docs are a great
reference, but not much in the way of terrific examples =)

- r





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Default timestamp value
@ 2000-06-05 08:13  Thomas Behr <thomas.behr@bnv-bamberg.de>
  parent: Rob S. <rslifka@home.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Thomas Behr @ 2000-06-05 08:13 UTC (permalink / raw)
  To: rslifka@home.com; +Cc: pgsql-sql

"Rob S." wrote:
> 
> As a precursor to this question, I read:
> 
>         CREATE TABLE
>         Date/Time Types (and corresponding input/output)
>         Date/Time Functions
> 
> ...but I still don't see how to have the default value of a timestamp to be
> the time at which the individual record is inserted.  I just get the time I
> created the table.  Specifically, what to put where the '?' is at.
> 
> ... "TimeDate" TIMESTAMP DEFAULT ? ...

Hallo Rob,
an example, which works by me [PostgreSQL 6.4.2 on i686-pc-linux-gnu,
compiled by gcc 2.7.2.]:

CREATE TABLE "session" (
  "zeitpunkt" timestamp DEFAULT now ( ) NOT NULL,
  "sid" int4 NOT NULL,
  "nummer" int4 NOT NULL,
  "status" character varying NOT NULL,
  "host" inet);

Ade

Thomas

> 
> I tried 'now' and 'current' but it just makes the default value the time
> when I create the table, and 'current' gives me the word 'current' back in
> psql, so I imagine I'm not taking that in quite the right way =)  I can see
> why it does this for both of these, althought in the docs, it says current
> is current time, deferred, but I dunno what that means.
> 
> Any help or pointers to a nice resource?  IMHO, the PG docs are a great
> reference, but not much in the way of terrific examples =)
> 
> - r



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Default timestamp value
@ 2000-06-05 15:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rob S. <rslifka@home.com>
  1 sibling, 2 replies; 5+ messages in thread

From: Tom Lane @ 2000-06-05 15:27 UTC (permalink / raw)
  To: rslifka@home.com; +Cc: pgsql-sql

"Rob S." <rslifka@home.com> writes:
> ...but I still don't see how to have the default value of a timestamp to be
> the time at which the individual record is inserted.  I just get the time I
> created the table.  Specifically, what to put where the '?' is at.

> ... "TimeDate" TIMESTAMP DEFAULT ? ...

In 7.0 either "'now'" or "now()" should work, eg

regression=# create table foo (f1 int, f2 timestamp default now());
CREATE
regression=# insert into foo values(1);
INSERT 395192 1
regression=# insert into foo values(2);
INSERT 395193 1
regression=# select * from foo;
 f1 |           f2
----+------------------------
  1 | 2000-06-05 11:15:25-04
  2 | 2000-06-05 11:15:28-04
(2 rows)

Versions before 7.0 are not entirely consistent about this, but I
believe the explicit function call now() will work the way you want
in any version.

BTW, this *is* covered in the FAQ, see
	http://www.postgresql.org/docs/faq-english.html#4.22

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Default timestamp value
@ 2000-06-05 15:52  Jeff Hoffmann <jeff@propertykey.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Jeff Hoffmann @ 2000-06-05 15:52 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: rslifka@home.com; pgsql-sql

Tom Lane wrote:
> 
> "Rob S." <rslifka@home.com> writes:
> > ...but I still don't see how to have the default value of a timestamp to be
> > the time at which the individual record is inserted.  I just get the time I
> > created the table.  

> Versions before 7.0 are not entirely consistent about this, but I
> believe the explicit function call now() will work the way you want
> in any version.

you can also use the value CURRENT_TIMESTAMP, which is standard SQL. 
you have to be careful about using 'now' as a default, though.  i have a
tendancy to put type qualifications on most of the things i write ever
since i had problems with getting an index to be used on an int2 field. 
the key to the faq is that you can't put a type qualification after the
'now', otherwise it won't work.  so in 7.0, if you create a table like
any of these:

create table foo (f1 int, f2 timestamp default now());
create table foo (f1 int, f2 timestamp default 'now');
create table foo (f1 int, f2 timestamp default CURRENT_TIMESTAMP);

it will work.  if you do it like this, it won't:

create table foo (f1 int, f2 timestamp default 'now'::datetime);
create table foo (f1 int, f2 timestamp default 'now'::timestamp);

it will give you the time that the table was created.  it's just a
little gotcha that might frustrate people in a similar situation.

jeff



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: Default timestamp value
@ 2000-06-05 16:41  Rob S. <rslifka@home.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 5+ messages in thread

From: Rob S. @ 2000-06-05 16:41 UTC (permalink / raw)
  To: pgsql-sql

Thanks very much everyone!

> BTW, this *is* covered in the FAQ, see
> 	http://www.postgresql.org/docs/faq-english.html#4.22

Ahh, but I did look at a FAQ! I just went back and checked the date, I guess
I shouldn't have just skimmed down the page: Last updated: Mon Oct 14
08:05:23 EDT 1996.  That's the last time I search for a FAQ before checking
the PG page. ;)

Thanks again everybody =)

- r




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2000-06-05 16:41 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-05 07:34 Default timestamp value Rob S. <rslifka@home.com>
2000-06-05 08:13 ` Thomas Behr <thomas.behr@bnv-bamberg.de>
2000-06-05 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-05 15:52   ` Jeff Hoffmann <jeff@propertykey.com>
2000-06-05 16:41   ` Rob S. <rslifka@home.com>

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