Re: extracting time from a timestamp with time zone field

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Peter Nixon
Sent: Monday, April 07, 2003 5:39
To: pgsql-general@postgresql.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