Re: Timestamps and PostgreSQL
Hi Andrew,
On 07/12/2010, at 9:21 AM, Andrew Lindesay wrote:
> Possibly not helpful, but you can stick this construct inside the class;
>
> static
> {
> ... do stuff ...
> }
>
> I guess that runs before main() because it has to initialise the class before
> it can run code in it.
Actually, that's very helpful, as I can stick that in a custom class between
Application and ERXApplication and I don't need to do anything in
Application.main().
--
Paul.
http://logicsquad.net/
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On 07/12/2010, at 9:16 AM, Chuck Hill wrote:
> On Dec 6, 2010, at 2:10 PM, Paul Hoadley wrote:
>
>> On 07/12/2010, at 6:13 AM, Chuck Hill wrote:
>>
>>> Is there a Wiki page for EOF and Postgres? It strikes me as this is
>>> something that should be documented. I don't think we can "fix" it, as it
>>> would impact people with existing data.
>>
>> I can put it here:
>>
>> http://wiki.objectstyle.org/confluence/display/WO/Database+Compatibility+and+Comparisons-PostgreSQL
>
> Perhaps more likely to be found here:
> http://wiki.objectstyle.org/confluence/display/WO/Project+WONDER-Frameworks-PostgresqlPlugin
OK.
>> I'm just trying to nail down one last detail. I've noted that calling
>> TimeZone.setDefault() in the Application constructor doesn't seem to occur
>> early enough to influence the JDBC driver's creation of its default
>> GregorianCalendar. Setting user.timezone works, but it _doesn't_ work from
>> a Properties file, which would have been ideal. The only method that works
>> is setting -Duser.timezone=UTC as a launch argument, which is a pain because
>> I have to remember to do it every time I set up a new app. Is there any way
>> I can get some code called even earlier than the Application constructor?
>
> I suspect the problem is that the driver is in /Library/Java/Extensions
Yeah, that's where I've got it.
> and so is getting loaded and initialized by the boot class loader (or is that
> root class loader? Q will know.). If that is where it is, try removing it
> from there and adding it to /Libraries in your application. That should
> delay loading until after the Properties values are available.
Surprisingly, that _doesn't_ work. I see:
user.timezone=UTC
And then later in the TimestampUtils constructor:
TimestampUtils.TimestampUtils: System.getProperty("user.timezone") = UTC
TimestampUtils.TimestampUtils: defaultCal.getTimeZone() =
sun.util.calendar.ZoneInfo[id="Australia/Adelaide"...
defaultCal is its default GregorianCalendar that's causing the problem.
--
Paul.
http://logicsquad.net/
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On 07/12/2010, at 9:10 AM, Ramsey Gurley wrote:
> I might be mistaken, but I don't think you can get any earlier than main():
>
> public static void main(String argv[]) {
> TimeZone.setDefault(...);
> ERXApplication.main(argv, Application.class);
> }
That does work.
--
Paul.
http://logicsquad.net/
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
Oh yeah... static initializers and variables come first. D'oh!
I think Chuck made an excellent point too. If that turns out to be the
case, I think there's room for one last EOF commandment (Thou shalt
not /Library/Java/Extensions)
Ramsey
On Dec 6, 2010, at 5:51 PM, Andrew Lindesay wrote:
Hi Ramsey;
Possibly not helpful, but you can stick this construct inside the
class;
static
{
... do stuff ...
}
I guess that runs before main() because it has to initialise the
class before it can run code in it.
cheers.
I might be mistaken, but I don't think you can get any earlier than
main():
public static void main(String argv[]) {
TimeZone.setDefault(...);
ERXApplication.main(argv, Application.class);
}
--
Andrew Lindesay
www.silvereye.co.nz
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/ramsey%40xeotech.com
This email sent to ram...@xeotech.com
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
Hi Ramsey;
Possibly not helpful, but you can stick this construct inside the class;
static
{
... do stuff ...
}
I guess that runs before main() because it has to initialise the class
before it can run code in it.
cheers.
I might be mistaken, but I don't think you can get any earlier than main():
public static void main(String argv[]) {
TimeZone.setDefault(...);
ERXApplication.main(argv, Application.class);
}
--
Andrew Lindesay
www.silvereye.co.nz
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On Dec 6, 2010, at 2:10 PM, Paul Hoadley wrote: > On 07/12/2010, at 6:13 AM, Chuck Hill wrote: > >> Is there a Wiki page for EOF and Postgres? It strikes me as this is >> something that should be documented. I don't think we can "fix" it, as it >> would impact people with existing data. > > I can put it here: > > http://wiki.objectstyle.org/confluence/display/WO/Database+Compatibility+and+Comparisons-PostgreSQL Perhaps more likely to be found here: http://wiki.objectstyle.org/confluence/display/WO/Project+WONDER-Frameworks-PostgresqlPlugin > I'm just trying to nail down one last detail. I've noted that calling > TimeZone.setDefault() in the Application constructor doesn't seem to occur > early enough to influence the JDBC driver's creation of its default > GregorianCalendar. Setting user.timezone works, but it _doesn't_ work from a > Properties file, which would have been ideal. The only method that works is > setting -Duser.timezone=UTC as a launch argument, which is a pain because I > have to remember to do it every time I set up a new app. Is there any way I > can get some code called even earlier than the Application constructor? I suspect the problem is that the driver is in /Library/Java/Extensions and so is getting loaded and initialized by the boot class loader (or is that root class loader? Q will know.). If that is where it is, try removing it from there and adding it to /Libraries in your application. That should delay loading until after the Properties values are available. Chuck -- Chuck Hill Senior Consultant / VP Development Practical WebObjects - for developers who want to increase their overall knowledge of WebObjects or who are trying to solve specific problems. http://www.global-village.net/products/practical_webobjects smime.p7s Description: S/MIME cryptographic signature ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On Dec 6, 2010, at 5:10 PM, Paul Hoadley wrote:
On 07/12/2010, at 6:13 AM, Chuck Hill wrote:
Is there a Wiki page for EOF and Postgres? It strikes me as this
is something that should be documented. I don't think we can
"fix" it, as it would impact people with existing data.
I can put it here:
http://wiki.objectstyle.org/confluence/display/WO/Database+Compatibility+and+Comparisons-PostgreSQL
I'm just trying to nail down one last detail. I've noted that
calling TimeZone.setDefault() in the Application constructor doesn't
seem to occur early enough to influence the JDBC driver's creation
of its default GregorianCalendar. Setting user.timezone works, but
it _doesn't_ work from a Properties file, which would have been
ideal. The only method that works is setting -Duser.timezone=UTC as
a launch argument, which is a pain because I have to remember to do
it every time I set up a new app. Is there any way I can get some
code called even earlier than the Application constructor?
--
Paul.
http://logicsquad.net/
I might be mistaken, but I don't think you can get any earlier than
main():
public static void main(String argv[]) {
TimeZone.setDefault(...);
ERXApplication.main(argv, Application.class);
}
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On 07/12/2010, at 6:13 AM, Chuck Hill wrote: > Is there a Wiki page for EOF and Postgres? It strikes me as this is > something that should be documented. I don't think we can "fix" it, as it > would impact people with existing data. I can put it here: http://wiki.objectstyle.org/confluence/display/WO/Database+Compatibility+and+Comparisons-PostgreSQL I'm just trying to nail down one last detail. I've noted that calling TimeZone.setDefault() in the Application constructor doesn't seem to occur early enough to influence the JDBC driver's creation of its default GregorianCalendar. Setting user.timezone works, but it _doesn't_ work from a Properties file, which would have been ideal. The only method that works is setting -Duser.timezone=UTC as a launch argument, which is a pain because I have to remember to do it every time I set up a new app. Is there any way I can get some code called even earlier than the Application constructor? -- Paul. http://logicsquad.net/ ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
Is there a Wiki page for EOF and Postgres? It strikes me as this is something
that should be documented. I don't think we can "fix" it, as it would impact
people with existing data.
On Dec 5, 2010, at 1:38 AM, Paul Hoadley wrote:
> I just couldn't let this go. Anyone not using PostgreSQL can tune out.
>
> On 04/12/2010, at 9:47 AM, Paul Hoadley wrote:
>
Displaying those times _without_ a formatter in the app shows the first
timestamp as '2010-12-03 04:51:55 Etc/GMT' which is exactly as expected,
but the second (that was entered from the console behind the app's back)
as '2010-12-02 18:21:55 Etc/GMT'.
Is it obvious to any other PostgreSQL users what is going on? It looks
like the database server's machine-local timezone is being used to adjust
the timestamp somewhere between WebObjects and the database.
>>>
>>> I agree.
>>
>> After some furious Googling, it turns out that the JDBC driver is affected
>> by the 'user.timezone' system property. Launching the app with
>> '-Duser.timezone=GMT' on my laptop (which otherwise gets set to
>> 'Australia/Adelaide') results in the behaviour I was expecting. I assume
>> this was the missing link in the "UTC from top to bottom" chain, as I can
>> see from the logs that both app servers were picking up their respective
>> local values for this property. Just to be clear, this doesn't actually
>> matter too much if the app is just running indefinitely on the same server:
>> the timestamps seem to be adjusted on the way in and back out of the
>> database, so that everything Just Works from within the app. It was only
>> when I dumped and restored the database somewhere else that it became a
>> problem.
>
> The JDBC driver is, indeed, adjusting the timestamp, and apparently this is a
> feature. EOF calls PreparedStatement.setTimestamp(int, Timestamp)
> (implemented in AbstractJdbc2Statement), which in turn calls
> PreparedStatement.setTimestamp(int, Timestamp, Calendar) with a null
> Calendar. To format the timestamp, TimestampUtils.toString(Calendar,
> Timestamp) is called, again with a null Calendar. The null Calendar is
> replaced by a new GregorianCalendar(), which is used to format the string
> sent to the database. All of this is fine, _except_ that the
> GregorianCalendar's timezone turns out to be the machine-local timezone,
> despite calling TimeZone.setDefault(TimeZone.getTimeZone("GMT")) in the
> application's constructor. (It's not obvious to me why this would be the
> case—does TimeZone.setDefault() not apply JVM-wide?) In any case, setting
> the user.timezone system property (to UTC) _is_ sufficient to cause that
> GregorianCalendar to be constructed with its timezone set to UTC, and then
> the JDBC driver doesn't perform any adjustments to timestamps on the way in
> or out.
>
> The PostgreSQL JDBC driver's behaviour strikes me as somewhat
> counter-intuitive, to say the least. I'm pretty sure it nails the issue of
> keeping timestamps in UTC with Postgres, though.
>
>
> --
> Paul.
>
> http://logicsquad.net/
>
>
> ___
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
> Help/Unsubscribe/Update your Subscription:
> http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net
>
> This email sent to ch...@global-village.net
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On 05/12/2010, at 8:08 PM, Paul Hoadley wrote: > EOF calls PreparedStatement.setTimestamp(int, Timestamp) (implemented in > AbstractJdbc2Statement), which in turn calls > PreparedStatement.setTimestamp(int, Timestamp, Calendar) with a null Calendar. Just for completeness, that's not quite true. Ramsey pointed out that JDBCColumn.takeInputValue() calls setObject() on that class. This is the top of the stack trace in setTimestamp(int, Timestamp, Calendar): at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:3049) at org.postgresql.jdbc2.AbstractJdbc2Statement.setTimestamp(AbstractJdbc2Statement.java:1334) at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1649) at org.postgresql.jdbc3.AbstractJdbc3Statement.setObject(AbstractJdbc3Statement.java:1438) at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1700) at com.webobjects.jdbcadaptor.JDBCColumn.takeInputValue(JDBCColumn.java:569) at com.webobjects.jdbcadaptor.JDBCChannel._bindInputVariablesWithBindingsAndExecute(JDBCChannel.java:260) at com.webobjects.jdbcadaptor.JDBCChannel._evaluateExpression(JDBCChannel.java:337) at com.webobjects.jdbcadaptor.JDBCChannel.evaluateExpression(JDBCChannel.java:296) at com.webobjects.jdbcadaptor.JDBCChannel.selectAttributes(JDBCChannel.java:220) at com.webobjects.eoaccess.EODatabaseChannel._selectWithFetchSpecificationEditingContext(EODatabaseChannel.java:897) at com.webobjects.eoaccess.EODatabaseChannel.selectObjectsWithFetchSpecification(EODatabaseChannel.java:234) at com.webobjects.eoaccess.EODatabaseContext._objectsWithFetchSpecificationEditingContext(EODatabaseContext.java:3055) at com.webobjects.eoaccess.EODatabaseContext.objectsWithFetchSpecification(EODatabaseContext.java:3195) at com.webobjects.eocontrol.EOObjectStoreCoordinator.objectsWithFetchSpecification(EOObjectStoreCoordinator.java:488) setObject() is called, and then eventually setTimestamp(int, Timestamp) then setTimestamp(int, Timestamp, null). So I was wrong to implicate EOF even peripherally: looks like it's entirely the JDBC driver. -- Paul. http://logicsquad.net/ ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
I just couldn't let this go. Anyone not using PostgreSQL can tune out.
On 04/12/2010, at 9:47 AM, Paul Hoadley wrote:
>>> Displaying those times _without_ a formatter in the app shows the first
>>> timestamp as '2010-12-03 04:51:55 Etc/GMT' which is exactly as expected,
>>> but the second (that was entered from the console behind the app's back) as
>>> '2010-12-02 18:21:55 Etc/GMT'.
>>>
>>> Is it obvious to any other PostgreSQL users what is going on? It looks
>>> like the database server's machine-local timezone is being used to adjust
>>> the timestamp somewhere between WebObjects and the database.
>>
>> I agree.
>
> After some furious Googling, it turns out that the JDBC driver is affected by
> the 'user.timezone' system property. Launching the app with
> '-Duser.timezone=GMT' on my laptop (which otherwise gets set to
> 'Australia/Adelaide') results in the behaviour I was expecting. I assume
> this was the missing link in the "UTC from top to bottom" chain, as I can see
> from the logs that both app servers were picking up their respective local
> values for this property. Just to be clear, this doesn't actually matter too
> much if the app is just running indefinitely on the same server: the
> timestamps seem to be adjusted on the way in and back out of the database, so
> that everything Just Works from within the app. It was only when I dumped
> and restored the database somewhere else that it became a problem.
The JDBC driver is, indeed, adjusting the timestamp, and apparently this is a
feature. EOF calls PreparedStatement.setTimestamp(int, Timestamp) (implemented
in AbstractJdbc2Statement), which in turn calls
PreparedStatement.setTimestamp(int, Timestamp, Calendar) with a null Calendar.
To format the timestamp, TimestampUtils.toString(Calendar, Timestamp) is
called, again with a null Calendar. The null Calendar is replaced by a new
GregorianCalendar(), which is used to format the string sent to the database.
All of this is fine, _except_ that the GregorianCalendar's timezone turns out
to be the machine-local timezone, despite calling
TimeZone.setDefault(TimeZone.getTimeZone("GMT")) in the application's
constructor. (It's not obvious to me why this would be the case—does
TimeZone.setDefault() not apply JVM-wide?) In any case, setting the
user.timezone system property (to UTC) _is_ sufficient to cause that
GregorianCalendar to be constructed with its timezone set to UTC, and then the
JDBC driver doesn't perform any adjustments to timestamps on the way in or out.
The PostgreSQL JDBC driver's behaviour strikes me as somewhat
counter-intuitive, to say the least. I'm pretty sure it nails the issue of
keeping timestamps in UTC with Postgres, though.
--
Paul.
http://logicsquad.net/
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
Hi Chuck,
On 04/12/2010, at 5:29 AM, Chuck Hill wrote:
> On Dec 2, 2010, at 9:23 PM, Paul Hoadley wrote:
>
>> Hello,
>>
>> I am trying to debug a timestamp issue.
>
> Run away to New Zealand. South Island. Assume a new identity. Save
> yourself while you still can!
Yeah, that's how I feel about this...
>> The larger problem is that I recently moved an application between servers
>> whose local timezones are different by 5 hours. I thought I had taken care
>> to set every layer to UTC (JVM:
>> TimeZone.setDefault(TimeZone.getTimeZone("GMT")); WO:
>> NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT());
>
> I am unsure if any of those really matter. NSTimestamp is always in GMT.
> The time zone should only affect formatting and parsing.
I wasn't sure either, though I've seen them suggested on the list over the
years.
> If you are doing NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT()), then I
> assume you are using a use specific TZ when parsing user input / showing
> times:
>> and only use user-local timezones in the view layer
Yes.
>> Postgres: '-c timezone=GMT'),
>
> That I am less sure about. FrontBase stores everything in GMT, but if you do
> a select in FrontBaseManager, it will format the times into your local
> timezone. I am unsure of PG's handling of this.
Until last week, I thought Postgres (or psql in particular) was doing the same
thing. But now I'm not convinced, as pg_dump dumps the timestamps _as
displayed by psql_. That was the source of my larger problem, as the restore
was into a different server-local timezone.
>> Inserting the same literal timestamp using psql:
>>
>> PBF=# insert into admission(admit_time, booking_id) VALUES ('2010-12-03
>> 04:51:55.912', 1);
>> INSERT 0 1
>>
>> Adds the following row:
>>
>> 2 | 2010-12-03 04:51:55.912
>>
>> That is, the time in UTC as expected.
>
> So it looks like PG is not doing what FB does, so it looks like it is somehow
> altering the inserted data. Perhaps due to the "WITHOUT TIME ZONE" definition?
I don't _think_ so. I manually changed the column type to "TIMESTAMP WITH TIME
ZONE", and it made no difference.
>> Displaying those times _without_ a formatter in the app shows the first
>> timestamp as '2010-12-03 04:51:55 Etc/GMT' which is exactly as expected, but
>> the second (that was entered from the console behind the app's back) as
>> '2010-12-02 18:21:55 Etc/GMT'.
>>
>> Is it obvious to any other PostgreSQL users what is going on? It looks like
>> the database server's machine-local timezone is being used to adjust the
>> timestamp somewhere between WebObjects and the database.
>
> I agree.
After some furious Googling, it turns out that the JDBC driver is affected by
the 'user.timezone' system property. Launching the app with
'-Duser.timezone=GMT' on my laptop (which otherwise gets set to
'Australia/Adelaide') results in the behaviour I was expecting. I assume this
was the missing link in the "UTC from top to bottom" chain, as I can see from
the logs that both app servers were picking up their respective local values
for this property. Just to be clear, this doesn't actually matter too much if
the app is just running indefinitely on the same server: the timestamps seem to
be adjusted on the way in and back out of the database, so that everything Just
Works from within the app. It was only when I dumped and restored the database
somewhere else that it became a problem.
--
Paul.
http://logicsquad.net/
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
It really doesn't help Chuck; we've got users, WebObjects and timezones here too. I did once know about GMT0 and PG, but I haven't used PG in production for a couple of years now. I think I did configure a PG server host to GMT0 once and a colleague tells me that he "configured postgres.conference in the equivalent of /var/lib/pgsql/data" -- I think he means ".configuration" there... cheers. >> I am trying to debug a timestamp issue. > Run away to New Zealand. South Island. Assume a new identity. Save yourself while you still can! -- Andrew Lindesay www.silvereye.co.nz ___ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
Re: Timestamps and PostgreSQL
On Dec 2, 2010, at 9:23 PM, Paul Hoadley wrote:
> Hello,
>
> I am trying to debug a timestamp issue.
Run away to New Zealand. South Island. Assume a new identity. Save yourself
while you still can!
> In development I am using PostgreSQL 8.2.5 (8.4.4 in deployment), WO 5.4.3,
> PostgresqlPlugIn.framework, and postgresql-8.2-508.jdbc3.jar.
>
> The larger problem is that I recently moved an application between servers
> whose local timezones are different by 5 hours. I thought I had taken care
> to set every layer to UTC (JVM:
> TimeZone.setDefault(TimeZone.getTimeZone("GMT")); WO:
> NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT());
I am unsure if any of those really matter. NSTimestamp is always in GMT. The
time zone should only affect formatting and parsing. If you are doing
NSTimeZone.setDefaultTimeZone(NSTimeZone.getGMT()), then I assume you are using
a use specific TZ when parsing user input / showing times:
> and only use user-local timezones in the view layer
> Postgres: '-c timezone=GMT'),
That I am less sure about. FrontBase stores everything in GMT, but if you do a
select in FrontBaseManager, it will format the times into your local timezone.
I am unsure of PG's handling of this.
> Despite this, it looks like the move has botched some timestamps, moving them
> by 5 hours. The attributes in question use the 'dateTime' prototype from
> ERPrototypes, the externalType for which is "timestamp" which is interpreted
> by Postgres as "TIMESTAMP WITHOUT TIME ZONE".
That is what FB uses, perhaps PG should be using TIMESTAMP WITH TIME ZONE to
prevent the DB server from modifying the inserted data?
> In an effort to get to the bottom of this, I can't help concluding that the
> machine-local timezone for the database server is having an effect, which
> just doesn't seem right to me. My laptop's local timezone is +1030, and I'm
> running Postgres on it for development: everything is local on the one
> machine in the one timezone. I've just added an entity to the database which
> generates this SQL:
>
> INSERT INTO admission(admit_time, booking_id, amount, id, ward,
> discharge_time) VALUES (?::timestamp, ?::int4, NULL, ?::int4, NULL, NULL)"
> withBindings: 1:2010-12-03 04:51:55.912(admitTime), 2:1(bookingID), 3:1(id)>
>
> Local time here was 1521, so that looks exactly right: local current time
> converted to UTC. Using psql, we're in GMT:
>
> PBF=# SHOW TIME ZONE;
> TimeZone
> --
> GMT
> (1 row)
>
> Yet it appears that the time has been converted back to +1030:
>
> PBF=# select id, admit_time from admission;
> id | admit_time
> +-
> 1 | 2010-12-03 15:21:55.912
> (1 row)
You would see that in the FrontBaseManager, because the tool formats the data
for display in your TZ.
> Inserting the same literal timestamp using psql:
>
> PBF=# insert into admission(admit_time, booking_id) VALUES ('2010-12-03
> 04:51:55.912', 1);
> INSERT 0 1
>
> Adds the following row:
>
> 2 | 2010-12-03 04:51:55.912
>
> That is, the time in UTC as expected.
So it looks like PG is not doing what FB does, so it looks like it is somehow
altering the inserted data. Perhaps due to the "WITHOUT TIME ZONE" definition?
> Displaying those times _without_ a formatter in the app shows the first
> timestamp as '2010-12-03 04:51:55 Etc/GMT' which is exactly as expected, but
> the second (that was entered from the console behind the app's back) as
> '2010-12-02 18:21:55 Etc/GMT'.
>
> Is it obvious to any other PostgreSQL users what is going on? It looks like
> the database server's machine-local timezone is being used to adjust the
> timestamp somewhere between WebObjects and the database.
I agree.
> Is there some final parameter I need to supply or variable I need to adjust
> to get the stack into UTC from top to bottom? I'm reasonably sure this was
> the cause of my larger problem, as pg_dump outputs the timestamps just as
> they're being displayed by psql, and then on restore they're 5 hours out from
> what would be expected.
Sorry, no specific help there.
> I hate timestamps.
I hate time.
http://www.youtube.com/watch?v=RwJNTZtjC7E
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their overall
knowledge of WebObjects or who are trying to solve specific problems.
http://www.global-village.net/products/practical_webobjects
smime.p7s
Description: S/MIME cryptographic signature
___
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
