Обсуждение: timestamp with timezone

Folks,

Our databases stores most of the dates as timestamp
with timezone format, so the data looks like this...

09/12/2003 12:51:31.268 PDT
09/12/2003 12:50:20 PDT

some has centiseconds in them along with TZ. Now i'm
wondering is there a way to just extract date and
timestamp alone leaving the centisecods as well the TZ
part.

does pg_dump utility has any options to trunc this
while it exports data ?

cheers,
shankar

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

oops forgot to mention, database version is 7.3.2

--- Shankar K <shan0075@yahoo.com> wrote:
> Folks,
>
> Our databases stores most of the dates as timestamp
> with timezone format, so the data looks like this...
>
> 09/12/2003 12:51:31.268 PDT
> 09/12/2003 12:50:20 PDT
>
> some has centiseconds in them along with TZ. Now i'm
> wondering is there a way to just extract date and
> timestamp alone leaving the centisecods as well the
> TZ
> part.
>
> does pg_dump utility has any options to trunc this
> while it exports data ?
>
> cheers,
> shankar
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site
> design software
> http://sitebuilder.yahoo.com
>


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Shankar K <shan0075@yahoo.com> writes:
> some has centiseconds in them along with TZ. Now i'm
> wondering is there a way to just extract date and
> timestamp alone leaving the centisecods as well the TZ
> part.

You mean you want to round off to even seconds?  Try date_trunc,
or cast to "timestamp(0) with time zone".  To force stuff to be
rounded when it's stored, declare the column like that in the
first place.

            regards, tom lane

> You mean you want to round off to even seconds?  Try
> date_trunc,
> or cast to "timestamp(0) with time zone".

well, i just need to trunc whatever thats after the
timestamp i.e from 06/10/2003 12:50:19.188 PDT, i need
to remove .188 PDT

i'll see if date_trunc helps me or not. thanks for the
suggestions tom.

>To force stuff to be
> rounded when it's stored, declare the column like
> that in the
> first place.
>
yeah, i wish the 3rd party apps had 'timestamp without
timezone' as the default for all date columns but it
isn't :(

anyways, reason behind the whole truncating TZ is
that, we basically copy the data from postgres to
oracle on a nightly batch process for our data
warehousing requirements, but oracle upto 8.1.7.x
doesn't support timestamp with timezone datatype, so
we are kind of stuck with spooling required
tables/formating dates to oracle compatible formats
and then use sql*loader to upload the data into
oracle.

not sure how well i can imporve this whole ETL
process. Any suggestions are greatly appreciated.

Thanks
Shankar

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

I think you could choose between two solutions (at least) :

1 - Insert date and time in place of timestamp
2 - use some functions like substring. Something like :
  select substring('09/12/2003 12:51:31.268 PDT' from '../../.... ..:..:..')

Bruno

Shankar K wrote:

>oops forgot to mention, database version is 7.3.2
>
>--- Shankar K <shan0075@yahoo.com> wrote:
>
>
>>Folks,
>>
>>Our databases stores most of the dates as timestamp
>>with timezone format, so the data looks like this...
>>
>>09/12/2003 12:51:31.268 PDT
>>09/12/2003 12:50:20 PDT
>>
>>some has centiseconds in them along with TZ. Now i'm
>>wondering is there a way to just extract date and
>>timestamp alone leaving the centisecods as well the
>>TZ
>>part.
>>
>>does pg_dump utility has any options to trunc this
>>while it exports data ?
>>
>>cheers,
>>shankar
>>
>>__________________________________
>>Do you Yahoo!?
>>Yahoo! SiteBuilder - Free, easy-to-use web site
>>design software
>>http://sitebuilder.yahoo.com
>>
>>
>>
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! SiteBuilder - Free, easy-to-use web site design software
>http://sitebuilder.yahoo.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>

--
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com