Can postgresql ignore DST ?
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
How about storing timestamp without timezone[1]?
1] https://www.postgresql.org/docs/current/static/datatype-datetime.html
Regards,
Amul
Show quoted text
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
.
Do
Regards,
Ven
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
Thanks a lot Tom.
We have Timezone configured to Australia/Sydney, we can change that to 11
and do we need to foresee any issues ?
Regards,
Ven
regards, tom lane
--
Regards,
Venkata B N
Database Consultant
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 Thu, 14 Dec 2017 at 7:28 pm, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
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.
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 ?
Regards,
Ven
--
Regards,
Venkata B N
Database Consultant
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)
If it helps (it may not) we always set our servers to Australia/Brisbane
(UTC+10), as Queensland doesn't have daylight savings, and is fairly
unlikely to, thus flicking all responsibility to the interface
developers... :)
On 15 December 2017 at 10:20, Venkata B Nagothi <nag1010@gmail.com> wrote:
On Thu, 14 Dec 2017 at 7:28 pm, Laurenz Albe <laurenz.albe@cybertec.at>
wrote: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.
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 ?
Regards,
Ven--
Regards,
Venkata B N
Database Consultant
--
Dr Ben Madin
Managing Director
m : +61 448 887 220
e : ben@ausvet.com.au
5 Shuffrey Street, Fremantle
Western Australia
on the web: www.ausvet.com.au
This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete
it and notify the sender. The contents of this email are the opinion of the
writer only and are not endorsed by Ausvet unless expressly stated
otherwise. Although Ausvet uses virus scanning software we do not accept
liability for viruses or similar in any attachments.
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.
Regards,
Ven
On 12/14/2017 9:17 PM, Venkata B Nagothi wrote:
On Fri, Dec 15, 2017 at 3:23 PM, Ben Madin <ben@ausvet.com.au
<mailto: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
On Fri, Dec 15, 2017 at 6:21 PM, Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
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.
My bad. It was my mis-understanding, the data did not change as it was
supposed to be. We had some bad data pushed from the application which led
to mis-understanding. All good now.
Regards,
Ven