Re: Timezone conversion woes

Hi Reid,

I am having a hard time locating this section (6.8.3) in the
documentation (I'm looking both here:
http://www.postgresql.org/docs/8.0/interactive/index.html and here:
http://jdbc.postgresql.org/documentation/80/index.html and not seeing
it in either place.) Where should I be looking?

Regarding the AT TIME ZONE option, it looks like that is specific to
Postgres, and works with the select, yes? My problem here is that I am
reading from MS SQL and trying to _write_ to Postgres. So I'm not sure
how this would help me.

Can anyone tell me if its possible to read a Date/Time/Timestamp as a
"bytes" value and then write it back that way somehow? Any other
suggestions?

THanks much,
Christian

On 7/14/05, Reid Thompson <Reid.Thompson@ateb.com> wrote:
> Christian Cryder wrote:
> > Ok, let me see if I can explain this simply. Is there any way
> > to read/write JDBC date/time/timestamp data through the
> > postgres drivers WITHOUT having any timezone conversion issues?
> >
> > I've searched high and low for info on this and not found any
> > conclusive answers.
> >
> > What is driving this problem is that we are reading data out
> > of one DB (MS SQL, using Net Direct drivers), and writing it back
> > into Postgres 8.
> >
> > In most cases, the NetDirect drivers leave timezone info
> > alone, EXCEPT when the date in question happens to fall into
> > a daylight savings time issue. In other words, when reading a
> > "zoneless" time out of the db, NetDirect says "hey, that's
> > not a valid time, because it falls into daylight savings time
> > "no mans land" (between 2-3 AM, 1st Sunday of April), and so
> > it rolls it forward to what it considers a valid time. The
> > only way we have found to offset this is to tell the JVM we
> > are running in UTC. Then NetDirect doesn't do the conversion.
> >
> > However, as soon as we do this, we now have a problem on the
> > Postgres side - Postgres says "oh, you're running in UTC, but
> > the DB is running as MST, so I better convert that date for
> > you." Argh. No, that is not what we want. We can compensate
> > by telling Postgres to run in UTC, but that's not really what
> > we want to do either (ie. because then, any code that writes
> > data into the DB has to remember to set its jvm timezone to UTC as
> > well).
> >
> > What we really want to do here is just tell the drivers -
> > "leave my dates alone, pal!" Is there any way to do that? I
> > realize that the NetDirect behavior is kind of at the root of
> > this, but we haven't found any way to change that. So please
> > don't just say - "your screwed". Our goal is to sucessfully
> > migrate to Postgres, here, so I'd really appreciate solutions rather
> > than finger pointing.
> >
> > Any suggestions?
> >
> > Thanks,
> > Christian
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
>                http://www.postgresql.org/docs/faq
>
> would this be of use
>
> 6.8.3. AT TIME ZONE
>
> The AT TIME ZONE construct allows conversions of timestamps to different
> timezones.
>
> Table 6-19. AT TIME ZONE Variants
> Expression      Returns         Description
> timestamp without time zone AT TIME ZONE zone   timestamp with time zone
> Convert local time in given timezone to UTC
> timestamp with time zone AT TIME ZONE zone      timestamp without time
> zone    Convert UTC to local time in given timezone
> time with time zone AT TIME ZONE zone   time with time zone     Convert
> local time across timezones
>
> In these expressions, the desired time zone can be specified either as a
> text string (e.g., 'PST') or as an interval (e.g., INTERVAL '-08:00').
>
> Examples (supposing that TimeZone is PST8PDT):
>
> SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
>  Result: 2001-02-16 19:38:40-08
>
>  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE
> 'MST';
>  Result: 2001-02-16 18:38:40
>
> The first example takes a zone-less timestamp and interprets it as MST
> time (GMT-7) to produce a UTC timestamp, which is then rotated to PST
> (GMT-8) for display. The second example takes a timestamp specified in
> EST (GMT-5) and converts it to local time in MST (GMT-7).
>
> The function timezone(zone, timestamp) is equivalent to the
> SQL-compliant construct timestamp AT TIME ZONE zone.
>
> reid
>