Thread: Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1)

Hi All:
I've googling around, searching the mailinglist archive and reading FAQ's 
but I haven't find the answer for my question. And I know it is quite 
commom! I'm trying upgrading to 7.2.1.
I'm running postgresql 7.0 with a column like:
                     Table "materia"       Column       |           Type           | Modifiers
--------------------+--------------------------+-----------

materiadata        | timestamp with time zone | not null
mmateriatitulo      | character varying(80)    | not null
materiasequencial  | numeric(30,6)            | not null


I used to execute this query:
select max(time(materiadata)) from materia;

orselect materiasequencial, materiatitulo, time(materiadata)from materiaorder by time(materiadata) desc;    
I've read at PostgreSQL 7.3dev Administrator Guide's Release Notes that 
time() and timestamp() functions in postgresql 7.2 are deprecated (so in 
7.2.1).
So, how can I get the same result above without using time() ??
Or if it not possible, how can I extract (yes, I tried with extract() 
function too) time from a timestamp column?I know it's quite simple question... but I haven't find any clue!


Thanks a lot in advance.
Bests regards



[]'s
Lucas Brasilino
brasilino@recife.pe.gov.br
http://www.recife.pe.gov.br
Emprel -    Empresa Municipal de Informatica (pt_BR)    Municipal Computing Enterprise (en_US)
Recife - Pernambuco - Brasil
Fone: +55-81-34167078

On Tue, 20 Aug 2002, Lucas Brasilino wrote:

>     I'm running postgresql 7.0 with a column like:
>
>                       Table "materia"
>         Column       |           Type           | Modifiers
> --------------------+--------------------------+-----------
>
> materiadata        | timestamp with time zone | not null
> mmateriatitulo      | character varying(80)    | not null
> materiasequencial  | numeric(30,6)            | not null
>
>
> I used to execute this query:
>
>     select max(time(materiadata)) from materia;
>
> or
>     select materiasequencial, materiatitulo, time(materiadata)
>     from materia
>     order by time(materiadata) desc;
>
>     I've read at PostgreSQL 7.3dev Administrator Guide's Release Notes that
> time() and timestamp() functions in postgresql 7.2 are deprecated (so in
> 7.2.1).
>
>     So, how can I get the same result above without using time() ??
> Or if it not possible, how can I extract (yes, I tried with extract()
> function too) time from a timestamp column?
>     I know it's quite simple question... but I haven't find any clue!

In general you could probably use CAST(materiadata as time) I'd guess.
I believe that at this point you can still use the functions, you just
need to double quote them ("time"(materiadata)) to differentiate them
from the type specifiers.

Hi Stephan

>>    So, how can I get the same result above without using time() ??
>>Or if it not possible, how can I extract (yes, I tried with extract()
>>function too) time from a timestamp column?
>>    I know it's quite simple question... but I haven't find any clue!
>>
> 
> In general you could probably use CAST(materiadata as time) I'd guess.
> I believe that at this point you can still use the functions, you just
> need to double quote them ("time"(materiadata)) to differentiate them
> from the type specifiers.
> 
Well, it seems to work only with "timestamp without time zone" type. But 
worked. Thanks a lot.For me it's a new approching not using functions such as time, but 
"casting" it to a "time" type ( like <sql>select materiadata::time from 
materia;</sql>).If PostgreSQL development group change it's point of view, I suppose 
there's some advantage. Do you know some advantages ??

bests regards
-- 

[]'s
Lucas Brasilino
brasilino@recife.pe.gov.br
http://www.recife.pe.gov.br
Emprel -    Empresa Municipal de Informatica (pt_BR)    Municipal Computing Enterprise (en_US)
Recife - Pernambuco - Brasil
Fone: +55-81-34167078

On Thu, 22 Aug 2002, Lucas Brasilino wrote:

> Hi Stephan
>
> >>    So, how can I get the same result above without using time() ??
> >>Or if it not possible, how can I extract (yes, I tried with extract()
> >>function too) time from a timestamp column?
> >>    I know it's quite simple question... but I haven't find any clue!
> >>
> >
> > In general you could probably use CAST(materiadata as time) I'd guess.
> > I believe that at this point you can still use the functions, you just
> > need to double quote them ("time"(materiadata)) to differentiate them
> > from the type specifiers.
> >
>
>     Well, it seems to work only with "timestamp without time zone" type. But
> worked. Thanks a lot.
>     For me it's a new approching not using functions such as time, but
> "casting" it to a "time" type ( like <sql>select materiadata::time from
> materia;</sql>).
>     If PostgreSQL development group change it's point of view, I suppose
> there's some advantage. Do you know some advantages ??

time(n) and timestamp(n) refer to a type which I believe gives n digits of
fractional seconds in SQL. Compatibility for that SQL feature was added
but the syntax collided with the old usage.  In any case the old time()
etc were actually typecasts since it was a direct call to the type casting
function.