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

Try SELECT timeofday()::TIMESTAMP;

Regards,

Ben
""Christopher J. Bottaro"" <cjbottaro@alumni.cs.utexas.edu> wrote in message
news:d46k11$6nc$1@sea.gmane.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 ?

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