extracting time from a timestamp with time zone field

From: Peter Nixon <listuser(at)peternixon(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: extracting time from a timestamp with time zone field
Date: 2003-04-07 09:39:05
Message-ID: b6rh41$1p13$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin Clift 2003-04-07 10:34:05 Postgresql & AMD x86-64
Previous Message Ulli Mueckstein 2003-04-07 07:28:55 call analyze from stored procedure in C