Re: extracting time from a timestamp with time zone field - Mailing list pgsql-general

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@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-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