Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Scott Marlowe wrote: I think you missed the first part of the conversation... I sure did. Sorry... Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 (<[EMAIL PROTECTED]>) "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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
Try SELECT timeofday()::TIMESTAMP; Regards, Ben ""Christopher J. Bottaro"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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 [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] CURRENT_TIMESTAMP vs actual time
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
