pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtimestamp
13+ messages / 10 participants
[nested] [flat]
* timestamp
@ 2002-10-06 14:41 Brian Ward <brian0@verizon.net>
0 siblings, 4 replies; 13+ messages in thread
From: Brian Ward @ 2002-10-06 14:41 UTC (permalink / raw)
To: pgsql-sql
How do I create a time stamp column in a table that default to the current
time
when a row is inserted?
I tried putting in now() as the default but I have something wrong
with the syntax or something I think
Any one have an example of a table creation script that has a timestamp
column?
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2002-10-06 15:38 Brian Ward <brian0@verizon.net>
parent: Brian Ward <brian0@verizon.net>
3 siblings, 0 replies; 13+ messages in thread
From: Brian Ward @ 2002-10-06 15:38 UTC (permalink / raw)
To: pgsql-sql
I think I got it but I'm not sure if it's correct.
I put the default as CURRENT_TIMESTAMP but I though I had read that was
being taken out and only now was supported?
Does anyone know?
"Brian Ward" <brian0@verizon.net> wrote in message
news:anphrb$24se$1@news.hub.org...
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a table creation script that has a timestamp
> column?
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2002-10-07 12:25 Larry Rosenman <ler@lerctr.org>
parent: Brian Ward <brian0@verizon.net>
3 siblings, 0 replies; 13+ messages in thread
From: Larry Rosenman @ 2002-10-07 12:25 UTC (permalink / raw)
To: Brian Ward <brian0@verizon.net>; +Cc: pgsql-sql
On Sun, 2002-10-06 at 09:41, Brian Ward wrote:
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a table creation script that has a timestamp
> column?
"comment_timestamp" timestamp(0) with time zone DEFAULT now()
that's a live one from a table I have.
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2002-10-07 12:35 Dan Langille <dan@langille.org>
parent: Brian Ward <brian0@verizon.net>
3 siblings, 0 replies; 13+ messages in thread
From: Dan Langille @ 2002-10-07 12:35 UTC (permalink / raw)
To: Brian Ward <brian0@verizon.net>; +Cc: pgsql-sql
On Sun, 6 Oct 2002, Brian Ward wrote:
> How do I create a time stamp column in a table that default to the current
> time
> when a row is inserted?
> I tried putting in now() as the default but I have something wrong
> with the syntax or something I think
> Any one have an example of a table creation script that has a timestamp
> column?
When you have an example which does not do what you want, it is better to
provide that so we can see how to correct it.
I am using : firstlogin timestamp default current_timestamp
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2002-10-07 12:44 Dan Langille <dan@langille.org>
parent: Brian Ward <brian0@verizon.net>
3 siblings, 0 replies; 13+ messages in thread
From: Dan Langille @ 2002-10-07 12:44 UTC (permalink / raw)
To: pgsql-sql
On Sun, 6 Oct 2002, Brian Ward wrote:
And Brian, since this *is* a mailing list, it would be polite to use a
valid email address. I suggest that you either unsubscribe or fix the
email address.
Mail to brian0@verizon.net bounces with the message: '550 Invalid
recipient: <brian0@verizon.net>'.
^ permalink raw reply [nested|flat] 13+ messages in thread
* timestamp
@ 2003-02-12 08:46 Daniel Jaenecke <daniel.jaenecke@smaxs.de>
0 siblings, 1 reply; 13+ messages in thread
From: Daniel Jaenecke @ 2003-02-12 08:46 UTC (permalink / raw)
To: pgsql-sql
Hi!
I am rather new to PostGreSQL, having mainly used MySQL until now.
MySQL has a column type TIMESTAMP[1] which holds - as expected - a timestamp, but additionally this timestamp is being updated automatically on any UPDATE or INSERT operation.
Since this is a quite handy feature to keep track of latest changes I would like to create such a behavior for my PostGres tables too. But since I have no idea how to do it I would warml welcome any suggestion... :)
Thx
dj
[1]
http://www.mysql.com/doc/en/DATETIME.html
^ permalink raw reply [nested|flat] 13+ messages in thread
* timestamp
@ 2003-02-12 09:09 Daniel Jaenecke <jaenecke@smaxs.de>
0 siblings, 1 reply; 13+ messages in thread
From: Daniel Jaenecke @ 2003-02-12 09:09 UTC (permalink / raw)
To: pgsql-sql
Hi!
I am rather new to PostGreSQL, having mainly used MySQL until now.
MySQL has a column type TIMESTAMP[1] which holds - as expected - a timestamp, but additionally this timestamp is being updated automatically on any UPDATE or INSERT operation.
Since this is a quite handy feature to keep track of latest changes I would like to create such a behavior for my PostGres tables too. But since I have no idea how to do it I would warml welcome any suggestion... :)
Thx
dj
[1]
http://www.mysql.com/doc/en/DATETIME.html
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2003-02-12 17:55 Dmitry Tkach <dmitry@openratings.com>
parent: Daniel Jaenecke <jaenecke@smaxs.de>
0 siblings, 0 replies; 13+ messages in thread
From: Dmitry Tkach @ 2003-02-12 17:55 UTC (permalink / raw)
To: Daniel Jaenecke <jaenecke@smaxs.de>
Inserting is easy:
create table timestamped
(
id serial primary key,
stuff text,
stamp timestamp not null default now ()
);
insert (stuff) into timestamped values ('blah');
Updating is more tricky... One way is to use a rule:
create rule update_stamp as on update to timestamped where stamp is null do instead
update timestamped set stuff = new.stuff, timestamp=now() where id = new.id;
or
create rule update_replace as on update to timestamped do instead
(
delete from timestamped where id=old.id;
insert into timestamped values (new.*);
);
I like this one better, because it does not depend on any column names, except the it - so you can modify the original table,
but the rule will still work...
Another way is to create a trigger:
create function update_stamp () returns opaque as
'begin; new.stamp=now(); return new;end;'
language 'plpgsql';
create trigger update_stamp_tg before update on timestamped
for each row execute procedure update_stamp();
This is even better than the rule, because you can use that same function with any table that has a column, called 'stamp',
even if it doesn't have a primary key
You can also make the column name an argument,for complete flexibility but I don't know how to do that in plpgsql (I believe, it's possible though) -
can be fairly easily done in C if you are not afraid of writing C functions :-)
I hope, it helps...
Dima
Daniel Jaenecke wrote:
> Hi!
>
> I am rather new to PostGreSQL, having mainly used MySQL until now.
> MySQL has a column type TIMESTAMP[1] which holds - as expected - a timestamp, but additionally this timestamp is being updated automatically on any UPDATE or INSERT operation.
>
> Since this is a quite handy feature to keep track of latest changes I would like to create such a behavior for my PostGres tables too. But since I have no idea how to do it I would warml welcome any suggestion... :)
>
> Thx
> dj
>
> [1]
> http://www.mysql.com/doc/en/DATETIME.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: timestamp
@ 2003-02-12 20:45 Larry Rosenman <ler@lerctr.org>
parent: Daniel Jaenecke <daniel.jaenecke@smaxs.de>
0 siblings, 0 replies; 13+ messages in thread
From: Larry Rosenman @ 2003-02-12 20:45 UTC (permalink / raw)
To: Daniel Jaenecke <daniel.jaenecke@smaxs.de>; pgsql-sql
You want a trigger. Similar to the following:
CREATE FUNCTION "netblock_date_change" () RETURNS opaque AS 'DECLARE
BEGIN
new.last_update_at = cast(now() as timestamp(0));
new.last_update_by = current_user;
return new;
END;' LANGUAGE 'plpgsql';
CREATE TRIGGER "netblock_change" BEFORE INSERT OR UPDATE ON "networks" FOR
EACH
ROW EXECUTE PROCEDURE "netblock_date_change" ();
--On Wednesday, February 12, 2003 09:46:15 +0100 Daniel Jaenecke
<daniel.jaenecke@smaxs.de> wrote:
> Hi!
>
> I am rather new to PostGreSQL, having mainly used MySQL until now.
> MySQL has a column type TIMESTAMP[1] which holds - as expected - a
> timestamp, but additionally this timestamp is being updated automatically
> on any UPDATE or INSERT operation.
>
> Since this is a quite handy feature to keep track of latest changes I
> would like to create such a behavior for my PostGres tables too. But
> since I have no idea how to do it I would warml welcome any suggestion...
> :)
>
> Thx
> dj
>
> [1]
> http://www.mysql.com/doc/en/DATETIME.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
^ permalink raw reply [nested|flat] 13+ messages in thread
* Timestamp
@ 2003-10-22 08:02 Abdul Wahab Dahalan <wahab@mimos.my>
0 siblings, 3 replies; 13+ messages in thread
From: Abdul Wahab Dahalan @ 2003-10-22 08:02 UTC (permalink / raw)
To: pgsql-sql
Hi !
In my database I've a field "departure" with timestamp without time zone
data type.
eg :
departure
2003-11-01 14:29:46
Now I want to select it as in this format 01-11-2003 14:29:46 How should
I do it?.
Thank you for any help.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Timestamp
@ 2003-10-22 08:18 Tomasz Myrta <jasiek@klaster.net>
parent: Abdul Wahab Dahalan <wahab@mimos.my>
2 siblings, 0 replies; 13+ messages in thread
From: Tomasz Myrta @ 2003-10-22 08:18 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: pgsql-sql
Dnia 2003-10-22 10:02, Użytkownik Abdul Wahab Dahalan napisał:
> Hi !
> In my database I've a field "departure" with timestamp without time zone
> data type.
> eg :
> departure
> 2003-11-01 14:29:46
>
> Now I want to select it as in this format 01-11-2003 14:29:46 How should
> I do it?.
You can find it in documentation - 6.7. Data Type Formatting Functions
select to_char(departure,'DD-MM-YYYY HH24:MI:SS') ...
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Timestamp
@ 2003-10-22 08:39 achill@matrix.gatewaynet.com
parent: Abdul Wahab Dahalan <wahab@mimos.my>
2 siblings, 0 replies; 13+ messages in thread
From: achill@matrix.gatewaynet.com @ 2003-10-22 08:39 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; +Cc: pgsql-sql
On Wed, 22 Oct 2003, Abdul Wahab Dahalan wrote:
> Hi !
> In my database I've a field "departure" with timestamp without time zone
> data type.
> eg :
> departure
> 2003-11-01 14:29:46
Maybe
SET DateStyle TO 'German' ;
SELECT replace(departure,'.','-') from <yourtable> ;
is close to what you want,
but better leave it default and format the date (input/output) from your
app.
>
> Now I want to select it as in this format 01-11-2003 14:29:46 How should
> I do it?.
>
> Thank you for any help.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
-Achilleus
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Timestamp
@ 2003-10-22 11:40 Theodore Petrosky <tedpet5@yahoo.com>
parent: Abdul Wahab Dahalan <wahab@mimos.my>
2 siblings, 0 replies; 13+ messages in thread
From: Theodore Petrosky @ 2003-10-22 11:40 UTC (permalink / raw)
To: Abdul Wahab Dahalan <wahab@mimos.my>; pgsql-sql
Check this out....
http://www.postgresql.org/docs/7.3/static/functions-formatting.html
to_char(departure, 'MM-DD-YYYY HH24:MI:SS') as
newformatdeparture
Ted
--- Abdul Wahab Dahalan <wahab@mimos.my> wrote:
> Hi !
> In my database I've a field "departure" with
> timestamp without time zone
> data type.
> eg :
> departure
> 2003-11-01 14:29:46
>
> Now I want to select it as in this format 01-11-2003
> 14:29:46 How should
> I do it?.
>
> Thank you for any help.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2003-10-22 11:40 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-06 14:41 timestamp Brian Ward <brian0@verizon.net>
2002-10-06 15:38 ` Brian Ward <brian0@verizon.net>
2002-10-07 12:25 ` Larry Rosenman <ler@lerctr.org>
2002-10-07 12:35 ` Dan Langille <dan@langille.org>
2002-10-07 12:44 ` Dan Langille <dan@langille.org>
2003-02-12 08:46 timestamp Daniel Jaenecke <daniel.jaenecke@smaxs.de>
2003-02-12 20:45 ` Larry Rosenman <ler@lerctr.org>
2003-02-12 09:09 timestamp Daniel Jaenecke <jaenecke@smaxs.de>
2003-02-12 17:55 ` Dmitry Tkach <dmitry@openratings.com>
2003-10-22 08:02 Timestamp Abdul Wahab Dahalan <wahab@mimos.my>
2003-10-22 08:18 ` Re: Timestamp Tomasz Myrta <jasiek@klaster.net>
2003-10-22 08:39 ` Re: Timestamp achill@matrix.gatewaynet.com
2003-10-22 11:40 ` Re: Timestamp Theodore Petrosky <tedpet5@yahoo.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