Обсуждение: Can postgresql ignore DST ?

How about storing timestamp without timezone[1]?

1] https://www.postgresql.org/docs/current/static/datatype-datetime.html

Regards,
Amul

On Thu, Dec 14, 2017 at 10:19 AM, Venkata B Nagothi <nag1010@gmail.com> wrote:
> Hi All,
>
> I have a question regarding PostgreSQL time zones and daylight savings -
>
> We have been migrating Oracle databases to PostgreSQL and the database we
> are migrating from does not seem to follow daylight savings and it is good
> that postgresql does.
>
> When i query the date columns i get the timezone offsets as +10 and +11
> depending on the date, time etc. Upon querying Oracle, i get all the
> timezone offsets as +11, adding to this, making the application code
> compatible to this will be utterly complex.
> To rule out any application issues, is it possible to get postgresql to
> ignore DST and render all the timestamps with timezone offsets of +11 ?
>
> I know it is weird question and it is not the right thing to do on the
> database, i am just asking in-case we may bump into this requirement in the
> near future.
>
> Any advise is appreciated !
>
>
>
> Regards,
> Venkata B N
>

Venkata B Nagothi <nag1010@gmail.com> writes:
> To rule out any application issues, is it possible to get postgresql to
> ignore DST and render all the timestamps with timezone offsets of +11 ?

set timezone = 11

            regards, tom lane

Venkata B Nagothi wrote:
> On Thu, 14 Dec 2017 at 4:04 pm, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Venkata B Nagothi <nag1010@gmail.com> writes:
> > > To rule out any application issues, is it possible to get postgresql to
> > > ignore DST and render all the timestamps with timezone offsets of +11 ?
> > 
> > set timezone = 11
> 
> We have Timezone configured to Australia/Sydney, we can change that to 11 and do we need to foresee any issues ?

That configuration parameter defines how the client will format
timestamps to strings and vice versa.

It should not have any other effects.

Yours,
Laurenz Albe

On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin <ben@ausvet.com.au> wrote:

I'd be a little worried that if you set timezone = 11 for Australia/Sydney you are embedding the daylight savings value, not the standard time value (UTC+10)

Totally agree. We have a weird situation where-in i had to do this and i would like to learn the impact on the data, i hope it would not fiddle the existing data. We are currently experimenting this.

 

in PostgreSQL, fields that are type TIMESTAMP WITH TIME ZONE convert all input time values to UTC, and store it in an internal representation, and on output, they are converted to the client's current TIMEZONE.

-- 
john r pierce, recycling bits in santa cruz

Venkata B Nagothi wrote:
> > > We have Timezone configured to Australia/Sydney, we can change that to 11 and do we need to foresee any issues ?
> > 
> > That configuration parameter defines how the client will format
> > timestamps to strings and vice versa.
> > 
> > It should not have any other effects.
> 
> I think what I see is some dates might have changed - is that a possibility ? 
> 
> If yes, is there any way I can revert back ?

I don't understand - you'd have to explain what you mean.

The setting doesn't change any data, it changes how a timestamp with time zone
is displayed.

It also sets the default time zone to use when a timestamp without timezone
is to be converted to a timestamp with time zone, so maybe that's what you
are observing.

Yours,
Laurenz Albe