From jaltamirano@correolux.com.mx Thu Nov  10 18:03:45 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 398FCD9599
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 14:03:45 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 29132-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 18:03:43 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from CORREO.correolux.com.mx
	(customer-200-79-53-218.uninet-ide.com.mx [200.79.53.218])
	by svr1.postgresql.org (Postfix) with ESMTP id CE631D7CB7
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 14:03:41 -0400 (AST)
Received: from 192.168.7.58 ([192.168.7.58]) by correolux.com.mx with
	MailEnable ESMTP; Thu, 10 Nov 2005 09:04:46 -0600
Subject: time
From: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="=-xHZwlhZA25iMLbh3E7GN"
Date: Thu, 10 Nov 2005 09:03:55 -0600
Message-Id: <1131635035.3529.143.camel@Desarrollo>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.026 required=5 tests=[AWL=0.025,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.026
X-Spam-Level: 
X-Archive-Number: 200511/90
X-Sequence-Number: 23341


--=-xHZwlhZA25iMLbh3E7GN
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello everybody!!!, I'd like to know if there another way to get the
time from a timestamp type, because in earliest versions I just get the
time like time(fecha) and now this returns an error, thanks in
advanced!!!!!!!

--=-xHZwlhZA25iMLbh3E7GN
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!!!!!
</BODY>
</HTML>

--=-xHZwlhZA25iMLbh3E7GN--





From jaltamirano@correolux.com.mx Thu Nov  10 15:15:56 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A704EDB157
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 11:15:56 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 40792-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 15:15:53 +0000 (GMT)
X-Greylist: delayed 00:12:15.819971 by SQLgrey-
Received: from CORREO.correolux.com.mx
	(customer-200-79-53-218.uninet-ide.com.mx [200.79.53.218])
	by svr1.postgresql.org (Postfix) with ESMTP id E46D7DB152
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 11:15:54 -0400 (AST)
Received: from 192.168.7.58 ([192.168.7.58]) by correolux.com.mx with
	MailEnable ESMTP; Thu, 10 Nov 2005 09:17:04 -0600
Subject: Re: time
From: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
To: pgsql-sql@postgresql.org
In-Reply-To: <1131635035.3529.143.camel@Desarrollo>
References: <1131635035.3529.143.camel@Desarrollo>
Content-Type: multipart/alternative; boundary="=-K7HSx8s5c3/T84cC8hv3"
Date: Thu, 10 Nov 2005 09:16:13 -0600
Message-Id: <1131635773.3529.153.camel@Desarrollo>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-3) 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.029 required=5 tests=[AWL=0.028,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.029
X-Spam-Level: 
X-Archive-Number: 200511/86
X-Sequence-Number: 23337


--=-K7HSx8s5c3/T84cC8hv3
Content-Type: text/plain
Content-Transfer-Encoding: 7bit



> Hello everybody!!!, I'd like to know if there another way to get the
> time from a timestamp type, because in earliest versions I just get
> the time like time(fecha) and now
> this returns an error, thanks in advanced!!!!!!!

--=-K7HSx8s5c3/T84cC8hv3
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
<BR>
<BLOCKQUOTE TYPE=CITE>
    <FONT COLOR="#000000">Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now</FONT><BR>
    <FONT COLOR="#000000">this returns an error, thanks in advanced!!!!!!!</FONT>
</BLOCKQUOTE>
</BODY>
</HTML>

--=-K7HSx8s5c3/T84cC8hv3--





From pg@rbt.ca Thu Nov  10 18:11:29 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 17BE3D9599
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 14:11:29 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 69420-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 18:11:27 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from keymaster.look.ca (delta1.look.ca [207.136.80.9])
	by svr1.postgresql.org (Postfix) with ESMTP id A79E2D7BF7
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 14:11:27 -0400 (AST)
Received: from 216-154-14-245.dsl.look.ca ([216.154.14.245])
	by keymaster.look.ca with esmtp (Exim 4.20)
	id 1EaGtT-0002oY-Ez; Thu, 10 Nov 2005 18:11:27 +0000
From: Rod Taylor <pg@rbt.ca>
To: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1131635035.3529.143.camel@Desarrollo>
References: <1131635035.3529.143.camel@Desarrollo>
Date: Thu, 10 Nov 2005 13:11:27 -0500
Message-Id: <1131646287.819.144.camel@home>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 FreeBSD GNOME Team Port 
X-SA-Exim-Mail-From: pg@rbt.ca
Subject: Re: time
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-SA-Exim-Version: 3.1 (built Tue Feb 24 05:09:27 GMT 2004)
X-SA-Exim-Scanned: Yes
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.011 required=5 tests=[AWL=0.011]
X-Spam-Score: 0.011
X-Spam-Level: 
X-Archive-Number: 200511/91
X-Sequence-Number: 23342

On Thu, 2005-11-10 at 09:03 -0600, Judith Altamirano Figueroa wrote:
> Hello everybody!!!, I'd like to know if there another way to get the
> time from a timestamp type, because in earliest versions I just get
> the time like time(fecha) and now this returns an error, thanks in
> advanced!!!!!!!

rbt=# select cast(now() - date_trunc('day', now()) as time);
      time
-----------------
 13:10:42.495579
(1 row)

-- 




From mike@fuhr.org Thu Nov  10 18:22:01 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6862CD9CDC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 14:22:01 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 72506-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 18:21:59 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id C4135D9BAF
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 14:21:59 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jAAILiZ4039561
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 10 Nov 2005 11:21:46 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jAAILhKL093414;
	Thu, 10 Nov 2005 11:21:43 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jAAILd6S093407;
	Thu, 10 Nov 2005 11:21:39 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 10 Nov 2005 11:21:39 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Rod Taylor <pg@rbt.ca>
Cc: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>,
	pgsql-sql@postgresql.org
Subject: Re: time
Message-ID: <20051110182139.GA87715@winnie.fuhr.org>
References: <1131635035.3529.143.camel@Desarrollo>
	<1131646287.819.144.camel@home>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1131646287.819.144.camel@home>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006]
X-Spam-Score: 0.006
X-Spam-Level: 
X-Archive-Number: 200511/92
X-Sequence-Number: 23343

On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote:
> rbt=# select cast(now() - date_trunc('day', now()) as time);
>       time
> -----------------
>  13:10:42.495579
> (1 row)

Am I missing something?  Is there a reason not to simply cast the
timestamp value to time?

test=> select cast(now() - date_trunc('day', now()) as time);
        time         
---------------------
 11:19:19.8921250105
(1 row)

test=> select now()::time;
       now       
-----------------
 11:19:19.892125
(1 row)

test=> select cast(now() as time);
       now       
-----------------
 11:19:19.892125
(1 row)

-- 
Michael Fuhr



From pg@rbt.ca Thu Nov  10 18:27:15 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 68177D9CDC
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 14:27:15 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74843-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 18:27:13 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from jandor.look.ca (theta1.look.ca [207.136.80.122])
	by svr1.postgresql.org (Postfix) with ESMTP id BA795D9599
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 14:27:13 -0400 (AST)
Received: from 216-154-14-245.dsl.look.ca ([216.154.14.245])
	by jandor.look.ca with esmtp (Exim 4.20)
	id 1EaH8g-000528-Lc; Thu, 10 Nov 2005 18:27:10 +0000
From: Rod Taylor <pg@rbt.ca>
To: Michael Fuhr <mike@fuhr.org>
Cc: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>,
	pgsql-sql@postgresql.org
In-Reply-To: <20051110182139.GA87715@winnie.fuhr.org>
References: <1131635035.3529.143.camel@Desarrollo>
	<1131646287.819.144.camel@home>
	<20051110182139.GA87715@winnie.fuhr.org>
Date: Thu, 10 Nov 2005 13:27:10 -0500
Message-Id: <1131647230.819.147.camel@home>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 FreeBSD GNOME Team Port 
X-SA-Exim-Mail-From: pg@rbt.ca
Subject: Re: time
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-SA-Exim-Version: 3.1 (built Tue Feb 24 05:09:27 GMT 2004)
X-SA-Exim-Scanned: Yes
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.01 required=5 tests=[AWL=0.010]
X-Spam-Score: 0.01
X-Spam-Level: 
X-Archive-Number: 200511/93
X-Sequence-Number: 23344

On Thu, 2005-11-10 at 11:21 -0700, Michael Fuhr wrote:
> On Thu, Nov 10, 2005 at 01:11:27PM -0500, Rod Taylor wrote:
> > rbt=# select cast(now() - date_trunc('day', now()) as time);
> >       time
> > -----------------
> >  13:10:42.495579
> > (1 row)
> 
> Am I missing something?  Is there a reason not to simply cast the
> timestamp value to time?

Nope. I had thought that the question came up because a straight cast
didn't work so I gave the first work around I thought of.
-- 




From mike@fuhr.org Thu Nov  10 18:38:19 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 465F9D7C80
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 14:38:19 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86507-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 18:38:17 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 99260D7BF7
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 14:38:17 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jAAIcDE0039621
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 10 Nov 2005 11:38:15 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jAAIcDjg032038;
	Thu, 10 Nov 2005 11:38:13 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jAAIcCcf032037;
	Thu, 10 Nov 2005 11:38:12 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 10 Nov 2005 11:38:12 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>
Cc: pgsql-sql@postgresql.org
Subject: Re: time
Message-ID: <20051110183812.GA21207@winnie.fuhr.org>
References: <1131635035.3529.143.camel@Desarrollo>
	<1131646287.819.144.camel@home>
	<20051110182139.GA87715@winnie.fuhr.org>
	<1131647057.3529.272.camel@Desarrollo>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1131647057.3529.272.camel@Desarrollo>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006]
X-Spam-Score: 0.006
X-Spam-Level: 
X-Archive-Number: 200511/94
X-Sequence-Number: 23345

[Please copy the mailing list on replies.]

On Thu, Nov 10, 2005 at 12:24:17PM -0600, Judith Altamirano Figueroa wrote:
> excuse me and how can I just get the hour, minute and second

The time type takes an optional precision:

test=> select now()::time;
       now       
-----------------
 11:36:34.124678
(1 row)

test=> select now()::time(0);
   now    
----------
 11:36:34
(1 row)

test=> select now()::time(2);
     now     
-------------
 11:36:34.12
(1 row)

-- 
Michael Fuhr



From tgl@sss.pgh.pa.us Thu Nov  10 19:13:49 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 99246DB1D0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 15:13:49 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 04194-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 19:13:47 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 1E22FDB1AC
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 15:13:47 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAAJDhM4011041;
	Thu, 10 Nov 2005 14:13:43 -0500 (EST)
To: Michael Fuhr <mike@fuhr.org>
Cc: Rod Taylor <pg@rbt.ca>,
	Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>,
	pgsql-sql@postgresql.org
Subject: Re: time 
In-reply-to: <20051110182139.GA87715@winnie.fuhr.org> 
References: <1131635035.3529.143.camel@Desarrollo>
	<1131646287.819.144.camel@home>
	<20051110182139.GA87715@winnie.fuhr.org>
Comments: In-reply-to Michael Fuhr <mike@fuhr.org>
	message dated "Thu, 10 Nov 2005 11:21:39 -0700"
Date: Thu, 10 Nov 2005 14:13:43 -0500
Message-ID: <11040.1131650023@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006]
X-Spam-Score: 0.006
X-Spam-Level: 
X-Archive-Number: 200511/95
X-Sequence-Number: 23346

Michael Fuhr <mike@fuhr.org> writes:
> Am I missing something?  Is there a reason not to simply cast the
> timestamp value to time?

> test=> select now()::time;
>        now       
> -----------------
>  11:19:19.892125
> (1 row)

> test=> select cast(now() as time);
>        now       
> -----------------
>  11:19:19.892125
> (1 row)

I think the OP was trying to use the functional cast syntax
	time(now())
which worked long ago, but has not since we added the
SQL-spec time precision syntax.

			regards, tom lane



From mike@fuhr.org Thu Nov  10 19:22:49 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 36E39DAC4B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 15:22:49 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32645-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 19:22:47 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id AD5DADA5B3
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 15:22:47 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jAAJMfmb039745
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 10 Nov 2005 12:22:46 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jAAJMeIb060491;
	Thu, 10 Nov 2005 12:22:40 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jAAJMdTx060490;
	Thu, 10 Nov 2005 12:22:39 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 10 Nov 2005 12:22:39 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Rod Taylor <pg@rbt.ca>,
	Judith Altamirano Figueroa <jaltamirano@correolux.com.mx>,
	pgsql-sql@postgresql.org
Subject: Re: time
Message-ID: <20051110192239.GA60396@winnie.fuhr.org>
References: <1131635035.3529.143.camel@Desarrollo>
	<1131646287.819.144.camel@home>
	<20051110182139.GA87715@winnie.fuhr.org>
	<11040.1131650023@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <11040.1131650023@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006]
X-Spam-Score: 0.006
X-Spam-Level: 
X-Archive-Number: 200511/96
X-Sequence-Number: 23347

On Thu, Nov 10, 2005 at 02:13:43PM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > Am I missing something?  Is there a reason not to simply cast the
> > timestamp value to time?
> 
> I think the OP was trying to use the functional cast syntax
> 	time(now())
> which worked long ago, but has not since we added the
> SQL-spec time precision syntax.

I was referring to the expression that Rod Taylor posted, wondering
if there was some subtlety I was missing or if Rod simply didn't
think of the simpler solution (Rod responded indicating the latter).

-- 
Michael Fuhr



From Tadej.Kanizar@sagit-server.com Thu Nov  10 19:59:00 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A7D1ED8FC5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 15:59:00 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 55879-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 19:58:57 +0000 (GMT)
X-Greylist: delayed 00:30:06.357318 by SQLgrey-
Received: from sagit-server.com (sagit-server.com [193.95.229.214])
	by svr1.postgresql.org (Postfix) with SMTP id 9935BD70C0
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 15:58:57 -0400 (AST)
Received: from Spooler by sagit-server.com (Mercury/32 v4.01a) ID MO001215;
	10 Nov 2005 20:58:59 +0100
Received: from spooler by sagit-server.com (Mercury/32 v4.01a);
	10 Nov 2005 20:28:39 +0100
Received: from Tadejnotebook (213.172.241.205) by sagit-server.com (Mercury/32
	v4.01a) with ESMTP ID MG001213; 10 Nov 2005 20:28:29 +0100
From: "Tadej Kanizar" <Tadej.Kanizar@sagit-server.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: time 
Date: Thu, 10 Nov 2005 20:29:07 +0100
Message-ID: <003601c5e62d$06b09600$680aa8c0@Tadejnotebook>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 11
Thread-Index: AcXmK1tRTs/DoA4/RjWC72QVdulJzQAAVyBQ
In-Reply-To: <11040.1131650023@sss.pgh.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.714 required=5 tests=[RCVD_IN_NJABL_DUL=1.713,
	UNPARSEABLE_RELAY=0.001]
X-Spam-Score: 1.714
X-Spam-Level: *
X-Archive-Number: 200511/97
X-Sequence-Number: 23348

So I don't open a new thread.. 
I have a table with a column of type TIMESTAMP.
In output, I need to format it.. what's the best way to do it?
So, for instance, how could I format it so that it would output as YY-MM-DD
HH:MM?

Regards,
Tadej

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Tom Lane
Sent: 10. november 2005 20:14
To: Michael Fuhr
Cc: Rod Taylor; Judith Altamirano Figueroa; pgsql-sql@postgresql.org
Subject: Re: [SQL] time 

Michael Fuhr <mike@fuhr.org> writes:
> Am I missing something?  Is there a reason not to simply cast the
> timestamp value to time?

> test=> select now()::time;
>        now       
> -----------------
>  11:19:19.892125
> (1 row)

> test=> select cast(now() as time);
>        now       
> -----------------
>  11:19:19.892125
> (1 row)

I think the OP was trying to use the functional cast syntax
	time(now())
which worked long ago, but has not since we added the
SQL-spec time precision syntax.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org




From mike@fuhr.org Thu Nov  10 21:30:15 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 58F29D7CB7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 17:30:15 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 59295-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 10 Nov 2005 21:30:11 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 6948DD7115
	for <pgsql-sql@postgresql.org>; Thu, 10 Nov 2005 17:30:12 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id jAALUAd4039852
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Thu, 10 Nov 2005 14:30:13 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id jAALU90l061242;
	Thu, 10 Nov 2005 14:30:09 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id jAALU91p061241;
	Thu, 10 Nov 2005 14:30:09 -0700 (MST) (envelope-from mfuhr)
Date: Thu, 10 Nov 2005 14:30:09 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Tadej Kanizar <Tadej.Kanizar@sagit-server.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: time
Message-ID: <20051110213009.GA61211@winnie.fuhr.org>
References: <11040.1131650023@sss.pgh.pa.us>
	<003601c5e62d$06b09600$680aa8c0@Tadejnotebook>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <003601c5e62d$06b09600$680aa8c0@Tadejnotebook>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006]
X-Spam-Score: 0.006
X-Spam-Level: 
X-Archive-Number: 200511/98
X-Sequence-Number: 23349

On Thu, Nov 10, 2005 at 08:29:07PM +0100, Tadej Kanizar wrote:
> I have a table with a column of type TIMESTAMP.
> In output, I need to format it.. what's the best way to do it?
> So, for instance, how could I format it so that it would output as YY-MM-DD
> HH:MM?

See to_char() in the "Data Type Formatting Functions" section
of the "Functions and Operators" chapter in the documentation.

http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html

-- 
Michael Fuhr