pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1)
4+ messages / 2 participants
[nested] [flat]
* Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1)
@ 2002-08-20 16:36 Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-20 17:03 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lucas Brasilino @ 2002-08-20 16:36 UTC (permalink / raw)
To: pgsql-sql
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;
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!
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with timestamp field/time function.. (upgrading
2002-08-20 16:36 Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1) Lucas Brasilino <brasilino@recife.pe.gov.br>
@ 2002-08-20 17:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-22 11:46 ` Re: Problem with timestamp field/time function.. (upgrading Lucas Brasilino <brasilino@recife.pe.gov.br>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-08-20 17:03 UTC (permalink / raw)
To: Lucas Brasilino <brasilino@recife.pe.gov.br>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with timestamp field/time function.. (upgrading
2002-08-20 16:36 Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1) Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-20 17:03 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-22 11:46 ` Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-22 14:28 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Lucas Brasilino @ 2002-08-22 11:46 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Problem with timestamp field/time function.. (upgrading
2002-08-20 16:36 Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1) Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-20 17:03 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-22 11:46 ` Re: Problem with timestamp field/time function.. (upgrading Lucas Brasilino <brasilino@recife.pe.gov.br>
@ 2002-08-22 14:28 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-08-22 14:28 UTC (permalink / raw)
To: Lucas Brasilino <brasilino@recife.pe.gov.br>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-08-22 14:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-20 16:36 Problem with timestamp field/time function.. (upgrading from 7.0 to 7.2.1) Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-20 17:03 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-22 11:46 ` Re: Problem with timestamp field/time function.. (upgrading Lucas Brasilino <brasilino@recife.pe.gov.br>
2002-08-22 14:28 ` Re: Problem with timestamp field/time function.. (upgrading Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox