Re: 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