Thread: CURRENT_TIMESTAMP vs actual time

CURRENT_TIMESTAMP vs actual time

From
"Christopher J. Bottaro"
Date:
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

Re: CURRENT_TIMESTAMP vs actual time

From
John DeSoi
Date:
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


Re: CURRENT_TIMESTAMP vs actual time

From
"Ben Trewern"
Date:
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
>



bigserial field in a view, nextval function ?

From
"Zlatko Matic"
Date:
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.


Re: CURRENT_TIMESTAMP vs actual time

From
"Christopher J. Bottaro"
Date:
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

Re: CURRENT_TIMESTAMP vs actual time

From
Alvaro Herrera
Date:
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)

Re: CURRENT_TIMESTAMP vs actual time

From
"Christopher J. Bottaro"
Date:
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

Re: bigserial field in a view, nextval function ?

From
Richard Huxton
Date:
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

Re: CURRENT_TIMESTAMP vs actual time

From
Thomas Hallgren
Date:
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

Re: bigserial field in a view, nextval function ?

From
"Zlatko Matic"
Date:
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
>


Re: CURRENT_TIMESTAMP vs actual time

From
Scott Marlowe
Date:
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

Re: CURRENT_TIMESTAMP vs actual time

From
Alban Hertroys
Date:
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

Re: CURRENT_TIMESTAMP vs actual time

From
Scott Marlowe
Date:
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



Re: CURRENT_TIMESTAMP vs actual time

From
Thomas Hallgren
Date:
Scott Marlowe wrote:
> I think you missed the first part of the conversation...
I sure did. Sorry...

Regards,
Thomas Hallgren