Re: Timezone conversion woes

Yeah, create your timestamps without timezones and they will not be
converted.

Dave
On 14-Jul-05, at 2:22 PM, Christian Cryder wrote:

> 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
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>