Thread: 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@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
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
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@svana.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