pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDefault 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