extracting time from a timestamp with time zone field

I have the following view

CREATE OR REPLACE VIEW VoIP AS
SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS
Call_Seconds, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS
Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month,
EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day,
h323ConnectTime AT TIME ZONE 'UTC' AS Time, CalledStationId AS Number,
H323RemoteAddress AS Remote_IP, h323ConfID AS CondID
FROM StopVoIP;

but I wish the "Time" column to display time only, not date and time. I have
read everything I can find in the postgres docs regarding formatiing and
extracting info from timestamp fields, and I cannot figure out how to do
this. Sure someone else has done this before!!

h323setuptime is: h323SetupTime timestamp with time zone NOT NULL

Can someone help?
(I would recomend that if postgres has some easy way of doing this, that the
info be added to
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-datetime.html
for others to find)

Thanks in advance.

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

date_trunc is the function you need
http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=functions-da
tetime.html#FUNCTIONS-DATETIME-TRUNC

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Peter Nixon
Sent: Monday, April 07, 2003 5:39
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] extracting time from a timestamp with time zone field

I have the following view

CREATE OR REPLACE VIEW VoIP AS
SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS
Call_Seconds, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS
Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month,
EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day,
h323ConnectTime AT TIME ZONE 'UTC' AS Time, CalledStationId AS Number,
H323RemoteAddress AS Remote_IP, h323ConfID AS CondID
FROM StopVoIP;

but I wish the "Time" column to display time only, not date and time. I have
read everything I can find in the postgres docs regarding formatiing and
extracting info from timestamp fields, and I cannot figure out how to do
this. Sure someone else has done this before!!

h323setuptime is: h323SetupTime timestamp with time zone NOT NULL

Can someone help?
(I would recomend that if postgres has some easy way of doing this, that the
info be added to
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-da
tetime.html
for others to find)

Thanks in advance.

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

date_truc seems to only lower precision which is not what I want.
For instance if the value in my TIMESTAMP field is:
2001-02-16 20:38:40
I want a function that outputs:
20:38:40
ie the time, WITHOUT the date part of the field.

Now I can do this with PL/Perl but I am assuming that this would be a fair
bit slower than a native function that did the same and I am processing many
GB of records at a time here so speed is important. (I just spent a whole day
optimising some code to gain an extra 30 transactions per second).

Does anyone have an idea how to do this??

Speaking of which, does anyone have any speed comparisons between the
different Procedural Languages. I am currently teaching myself PL/pgSQL as I
am under the impression it is alot faster than PL/Perl although obviously
not as powerfull.

TIA

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

On Mon April 7 2003 19:38, Andy Kriger wrote:
> date_trunc is the function you need
> http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=functions-d
>a tetime.html#FUNCTIONS-DATETIME-TRUNC
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Peter Nixon
> Sent: Monday, April 07, 2003 5:39
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] extracting time from a timestamp with time zone field
>
>
> I have the following view
>
> CREATE OR REPLACE VIEW VoIP AS
> SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS
> Call_Seconds, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS
> Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month,
> EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day,
> h323ConnectTime AT TIME ZONE 'UTC' AS Time, CalledStationId AS Number,
> H323RemoteAddress AS Remote_IP, h323ConfID AS CondID
> FROM StopVoIP;
>
> but I wish the "Time" column to display time only, not date and time. I
> have read everything I can find in the postgres docs regarding formatiing
> and extracting info from timestamp fields, and I cannot figure out how to
> do this. Sure someone else has done this before!!
>
> h323setuptime is: h323SetupTime timestamp with time zone NOT NULL
>
> Can someone help?
> (I would recomend that if postgres has some easy way of doing this, that
> the info be added to
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-d
>a tetime.html
> for others to find)
>
> Thanks in advance.
>
> --
>
> Peter Nixon
> http://www.peternixon.net/
> PGP Key: http://www.peternixon.net/public.asc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

On Tue, Apr 08, 2003 at 11:38:38AM +0300, Peter Nixon wrote:
> date_truc seems to only lower precision which is not what I want.
> For instance if the value in my TIMESTAMP field is:
> 2001-02-16 20:38:40
> I want a function that outputs:
> 20:38:40
> ie the time, WITHOUT the date part of the field.

Just cast it:
# select cast('2001-02-16 20:38:40'::timestamp as time);
?column?
----------
20:38:40
(1 row)

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
> - Samuel P. Huntington