Обсуждение: time

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

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)

-- 

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

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

[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

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 syntaxtime(now())
which worked long ago, but has not since we added the
SQL-spec time precision syntax.
        regards, tom lane

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

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 syntaxtime(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

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