Thread: CURRENT_TIMESTAMP vs actual time
Hi, I understand that CURRENT_TIMESTAMP marks the beginning of the current transaction. I want it to be the actual time. How do I do this? timeofday() returns a string, how do I convert that into a TIMESTAMP? Is it possible to create a column with DEFAULT value evaluated to the actual current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the current transaction). What I do now to get it to work is do a COMMIT right before the insert, that way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that is so crappy and doesn't work if I actually need to use transactional features (i.e. rollback). Thanks for the help, -- C
On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote:
> I understand that CURRENT_TIMESTAMP marks the beginning of the current
> transaction. I want it to be the actual time. How do I do this?
> timeofday() returns a string, how do I convert that into a TIMESTAMP?
timeofday()::timestamp;
> Is it possible to create a column with DEFAULT value evaluated to the
> actual
> current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of
> the
> current transaction).
Sure. Use the expression above or create a function for it:
create or replace function timeofday_stamp() returns timestamp as
'select timeofday()::timestamp;'
language sql volatile;
create table test_stamp (
id integer primary key,
my_stamp timestamp default timeofday_stamp()
);
insert into test_stamp values (1);
insert into test_stamp values (2);
select * from test_stamp;
id | my_stamp
----+----------------------------
1 | 2005-04-20 19:35:59.884837
2 | 2005-04-20 19:36:13.719402
(2 rows)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
Try SELECT timeofday()::TIMESTAMP; Regards, Ben ""Christopher J. Bottaro"" <cjbottaro@alumni.cs.utexas.edu> wrote in message news:d46k11$6nc$1@sea.gmane.org... > Hi, > I understand that CURRENT_TIMESTAMP marks the beginning of the current > transaction. I want it to be the actual time. How do I do this? > timeofday() returns a string, how do I convert that into a TIMESTAMP? > > Is it possible to create a column with DEFAULT value evaluated to the > actual > current time (i.e. not the CURRENT_TIMESTAMP which is the beginning of the > current transaction). > > What I do now to get it to work is do a COMMIT right before the insert, > that > way CURRENT_TIMESTAMP is (pretty close to) the actual time. ...but that > is > so crappy and doesn't work if I actually need to use transactional > features > (i.e. rollback). > > Thanks for the help, > -- C > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
I need to have an "identity" column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success.... How can I put a bigserial column in a view ? Thanks.
John DeSoi wrote: > > On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: > >> I understand that CURRENT_TIMESTAMP marks the beginning of the current >> transaction. I want it to be the actual time. How do I do this? >> timeofday() returns a string, how do I convert that into a TIMESTAMP? > > > timeofday()::timestamp; Great, that did it, thanks. I also found out that you can say CAST(timeofday() AS TIMESTAMP). I assume its the same thing... -- C
On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: > John DeSoi wrote: > > > > > On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: > > > >> I understand that CURRENT_TIMESTAMP marks the beginning of the current > >> transaction. I want it to be the actual time. How do I do this? > >> timeofday() returns a string, how do I convert that into a TIMESTAMP? > > > > timeofday()::timestamp; > > Great, that did it, thanks. I also found out that you can say > CAST(timeofday() AS TIMESTAMP). I assume its the same thing... Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a timestamp with time zone, whereas casting to timestamp unadorned returns a timestamp without time zone. Try cast(timeofday() as timestamptz) or cast(timeofday() as timestamp with time zone) It may not matter a lot but you may as well be aware of the difference ... -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica" (Irulan)
Alvaro Herrera wrote: > On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: >> John DeSoi wrote: >> >> > >> > On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: >> > >> >> I understand that CURRENT_TIMESTAMP marks the beginning of the current >> >> transaction. I want it to be the actual time. How do I do this? >> >> timeofday() returns a string, how do I convert that into a TIMESTAMP? >> > >> > timeofday()::timestamp; >> >> Great, that did it, thanks. I also found out that you can say >> CAST(timeofday() AS TIMESTAMP). I assume its the same thing... > > Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a > timestamp with time zone, whereas casting to timestamp unadorned returns > a timestamp without time zone. Try > > cast(timeofday() as timestamptz) > or > cast(timeofday() as timestamp with time zone) > > It may not matter a lot but you may as well be aware of the difference ... Ahh, thanks for the tip. I guess I'll just stick with timeofday()::timestamp...its more concise anyways... -- C
Zlatko Matic wrote: > I need to have an "identity" column in a view. > I was using bigserial columns in tables and Postgre created nextval > function expression automatically. > Now I have tried with nextval function in the view, but with no success.... > How can I put a bigserial column in a view ? What do you mean by an "identity" column in a view? A view is just a named query, so doesn't hold any data of its own. Can you explain what you are trying to achieve? -- Richard Huxton Archonet Ltd
Christopher J. Bottaro wrote: > Alvaro Herrera wrote: > > >>On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: >> >>>John DeSoi wrote: >>> >>> >>>>On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: >>>> >>>> >>>>>I understand that CURRENT_TIMESTAMP marks the beginning of the current >>>>>transaction. I want it to be the actual time. How do I do this? >>>>>timeofday() returns a string, how do I convert that into a TIMESTAMP? >>>> >>>>timeofday()::timestamp; >>> >>>Great, that did it, thanks. I also found out that you can say >>>CAST(timeofday() AS TIMESTAMP). I assume its the same thing... >> >>Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a >>timestamp with time zone, whereas casting to timestamp unadorned returns >>a timestamp without time zone. Try >> >>cast(timeofday() as timestamptz) >>or >>cast(timeofday() as timestamp with time zone) >> >>It may not matter a lot but you may as well be aware of the difference ... > > > Ahh, thanks for the tip. I guess I'll just stick with > timeofday()::timestamp...its more concise anyways... > Why use timeofday() at all? Why not now(). It will return a timestamptz without casts. Regards, Thomas Hallgren
Well, my front-end is MS Access, and Access sees views as tables. When I have forms with subforms there is a problem with linking them if table has no primary key. As Access thinks that a view is a table, I need a primary key in the view. Also, Access doesn't like text field of ODBC-linked table to be primary key (#Deleted phenomena#). Instead, it should be a numeric field. Therefore, I would like to have an autoincrement field, which Access will consider as primary key...I need a calculated bigserial field... Can I accomplish it whith nextval ? Greetings, Zlatko ----- Original Message ----- From: "Richard Huxton" <dev@archonet.com> To: "Zlatko Matic" <zlatko.matic1@sb.t-com.hr> Cc: <pgsql-general@postgresql.org> Sent: Thursday, April 21, 2005 5:06 PM Subject: Re: [GENERAL] bigserial field in a view, nextval function ? > Zlatko Matic wrote: >> I need to have an "identity" column in a view. >> I was using bigserial columns in tables and Postgre created nextval >> function expression automatically. >> Now I have tried with nextval function in the view, but with no >> success.... >> How can I put a bigserial column in a view ? > > What do you mean by an "identity" column in a view? A view is just a named > query, so doesn't hold any data of its own. Can you explain what you are > trying to achieve? > > -- > Richard Huxton > Archonet Ltd >
On Thu, 2005-04-21 at 09:52, Christopher J. Bottaro wrote: > Alvaro Herrera wrote: > > > On Thu, Apr 21, 2005 at 09:22:26AM -0500, Christopher J. Bottaro wrote: > >> John DeSoi wrote: > >> > >> > > >> > On Apr 20, 2005, at 6:15 PM, Christopher J. Bottaro wrote: > >> > > >> >> I understand that CURRENT_TIMESTAMP marks the beginning of the current > >> >> transaction. I want it to be the actual time. How do I do this? > >> >> timeofday() returns a string, how do I convert that into a TIMESTAMP? > >> > > >> > timeofday()::timestamp; > >> > >> Great, that did it, thanks. I also found out that you can say > >> CAST(timeofday() AS TIMESTAMP). I assume its the same thing... > > > > Not sure it's the same thing. IIRC, CURRENT_TIMESTAMP returns a > > timestamp with time zone, whereas casting to timestamp unadorned returns > > a timestamp without time zone. Try > > > > cast(timeofday() as timestamptz) > > or > > cast(timeofday() as timestamp with time zone) > > > > It may not matter a lot but you may as well be aware of the difference ... > > Ahh, thanks for the tip. I guess I'll just stick with > timeofday()::timestamp...its more concise anyways... 2 points: 1: cast(timeofday() as timestamptz) is the SQL standard way of doing it, and it's more portable. 2: I think Alvaro's point was about timestamp with timezone, not the format for casting. i.e. if you use postgresql's shorthand for casting, you could use this for timestamptz: select timeofday()::timestamptz
Thomas Hallgren wrote: > Why use timeofday() at all? Why not now(). It will return a timestamptz > without casts. For the same reason that the OP couldn't use CURRENT_TIMESTAMP. It returns the timestamp of the start of the transaction. -- Alban Hertroys MAG Productions T: +31(0)53 4346874 F: +31(0)53 4346876 E: alban@magproductions.nl W: http://www.magproductions.nl
On Thu, 2005-04-21 at 10:21, Thomas Hallgren wrote: > Christopher J. Bottaro wrote: > > Alvaro Herrera wrote: > > Ahh, thanks for the tip. I guess I'll just stick with > > timeofday()::timestamp...its more concise anyways... > > > > Why use timeofday() at all? Why not now(). It will return a timestamptz > without casts. I think you missed the first part of the conversation, which was he needed a type that updated inside a transaction: # begin; # select timeofday()::timestamptz; 2005-04-21 10:59:58.181834-05 # select now(); 2005-04-21 10:59:50.286865-05 # select timeofday()::timestamptz; 2005-04-21 11:00:04.821057-05 # select now(); 2005-04-21 10:59:50.286865-05
Scott Marlowe wrote: > I think you missed the first part of the conversation... I sure did. Sorry... Regards, Thomas Hallgren