Adrian - Yes how the db stores any data is irrelevant but the important
bit is how the date field is treated and returned to Java, I am merely
stating the fact that standard
SQL date fields have no time/timezone element and are unaffected by
timezone changes in the db. Time and timestamps can be though. Read
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt for more details
Can you give details on how you replicated? We only noticed the issue
because of changes between GMT and British Summer Time or vice versa. If
we had users from multiple countries using ofbiz and used their own
timezones, I believe any dates would show incorrectly for those west of
the UK.
Gareth Carter
Software Development Analyst
Stannah Management Services Ltd
IT
Ext: 7036
Tel: 01264 364311
Fax:
Please consider the environment before printing this email.
From: adrian.c...@sandglass-software.com
To: dev@ofbiz.apache.org
Date: 01/04/2014 11:57
Subject: Re: Birthday's Change
How a DB manufacturer persists a date field is irrelevant. The issue
here is how Java handles the date type in the JDBC driver and
supporting code.
I repeat:
I can reproduce the problem using a time zone with ZERO OFFSET, so
this is not a problem caused by applying a time zone to a date. More
likely, it is a problem with the WRONG time zone being applied to a
date.
-Adrian
Quoting gareth_car...@stannah.co.uk:
> Piere - SQL dates are stored without timezone information and dates are
> returned exactly the same with different default database timezones -
> unless you specifically convert the date field to a different timezone
in
> SQL
>
> Such as
>
> Default timezone on our database
> set timezone to 'GB';
> select birth_date, timezone('Canada/Atlantic', birth_date) from person
> where birth_date is not null limit 100;
>
> Swap to see change in second column but birth_date is unaffected by the
> timezone in db
> set timezone to 'Canada/Atlantic';
> select birth_date, timezone('GB', birth_date) from person where
birth_date
> is not null limit 100;
>
> In my opinion, this convention should be followed in ofbiz, you cannot
> correctly apply a timezone to a date only field without knowing the
time!
>
>
>
> Adrian - We have had this issue for over a Year now and had a brief
> discussion with Hans about this a while back. My opinion it is the
> timezone being applied to java.sql.Date incorrectly that is at fault.
> Freemarker also has the same issue
>
>
>
> Gareth Carter
> Software Development Analyst
> Stannah Management Services Ltd
> IT
>
> Ext: 7036
> Tel: 01264 364311
> Fax:
>
>
>
>
> Please consider the environment before printing this
email.
>
>
>
> From: Pierre Smits <pierre.sm...@gmail.com>
> To: dev@ofbiz.apache.org
> Date: 01/04/2014 11:15
> Subject: Re: Birthday's Change
>
>
>
> Rupert,
>
> Please create the JIRA issue. Irrespective of what the users timezone
is,
> the date must always be stored in accordance with the timezone setting
of
> the internal company used (with a failover (if not set) to the default
of
> the tenant, which - if not set - fails over to the default of the OFBiz
> setup (from a .properties file). And yes, calculations to/from should
> always have 12:00 noon in mind.
>
> Regards,
>
> Pierre Smits
>
> *ORRTIZ.COM <http://www.orrtiz.com>*
> Services & Solutions for Cloud-
> Based Manufacturing, Professional
> Services and Retail & Trade
> http://www.orrtiz.com
>
>
> On Tue, Apr 1, 2014 at 12:10 PM, Rupert Howell
> <ruperthow...@provolve.com>wrote:
>
>> Thanks Gareth that was put much more eloquently.
>> Adrian / Pierre are you happy there's an issue here and I'll raise a
> Jira
>> and submit a patch.
>>
>> Can we discuss if there's a need for for a new "date-fixed" field type
> that
>> never has the timezone applied to the date format on display or whether
> we
>> should use the existing date as a container for a specific moment in
> time
>> that is completely TZ independent. In my mind the latter is how it
> should
>> be since java.util.Date has no TZ information attached to it I cant see
> how
>> formatting it with a timezone is atall beneficial.
>>
>> Best Regards,
>>
>>
>> On 1 April 2014 09:45, <gareth_car...@stannah.co.uk> wrote:
>>
>> > Hi all
>> >
>> > Me and Rupert have been looking at this as we've had this issue for a
>> > while with specifically the Birth Date field - but any date only
> fields
>> > will have this issue.
>> >
>> > The birth date field is date only in ofbiz and in the database
>> > java.sql.Date is returned from jdbc drivers when the field is SQL
> date,
>> > the date will be set but the time will always be 00:00:00. The
>> > java.sql.Date is only there to represent date only component of
>> > java,util.Date (java.sql.Date overrides toString method to return
only
>> the
>> > date)
>> > Because java.sql.Date extends java.util.Date and can be used in
>> DateFormat
>> > class, applying a timezone with a negative offset will shift the day
> to
>> the
>> > previous day because time is ALWAYS set to 00:00:00
>> >
>> > This also occurs in freemarker if you convert a java.sql.Date to a
> string
>> > using syntax such as ${date?string} where date is a java.sql.Date
>> object. I
>> > have created a fix in my fork at
>> > https://github.com/gareth-carter/freemarker
>> >
>> > *Gareth Carter *
>> >
>> > *Software Development Analyst*
>> >
>> > *Stannah Management Services Ltd*
>> >
>> > *IT*
>> >
>> > *Ext:*
>> >
>> > 7036
>> >
>> > *Tel:*
>> >
>> > 01264 364311
>> >
>> > *Fax:*
>> >
>> >
>> >
>> > Please consider the environment before printing this email.
>> >
>> >
>> >
>> >
>> >
>> > From: Rupert Howell <ruperthow...@provolve.com>
>> > To: "dev@ofbiz.apache.org" <dev@ofbiz.apache.org>
>> > Date: 01/04/2014 09:27
>> > Subject: Re: Birthday's Change
>> > ------------------------------
>> >
>> >
>> >
>> > My birth date is my birth date wherever I am in the world - it is not
>> > relative. My passport doesn't change as I travel through Timezones.
> Yet
>> if
>> > I view my passport information is OFBiz it will change,
>> > Dates need to be viewed as dates and be totally independent of
>> timezones. I
>> > cannot think of a single reason why you would want to be specific
with
>> > dates. If you do want to be specific and have them change as to where
> you
>> > view them from - you'd just use Timestamps.
>> >
>> >
>> > On 1 April 2014 09:12, Pierre Smits <pierre.sm...@gmail.com> wrote:
>> >
>> > > Rupert,
>> > >
>> > > You are right when you don't want to be to specific. But if you are
>> > > specific and precise then a birthday needs to have a time zone
>> > associated.
>> > >
>> > > Remember it is not the birthday itself that shifts, but your
> viewpoint
>> of
>> > > it when changing locations (meaning time zones).
>> > >
>> > > Regarding.
>> > >
>> > > Pierre Smits
>> > >
>> > > *ORRTIZ.COM <http://www.orrtiz.com>*
>> > > Services & Solutions for Cloud-
>> > > Based Manufacturing, Professional
>> > > Services and Retail & Trade
>> > > http://www.orrtiz.com
>> > >
>> > >
>> > > On Tue, Apr 1, 2014 at 10:04 AM, Pierre Smits
> <pierre.sm...@gmail.com
>> > > >wrote:
>> > >
>> > > > Hmm.
>> > > >
>> > > > Digging a bit deeper I see that birthday is persisted as a date.
> So
>> > that
>> > > > shouldn't be creating issues.
>> > > >
>> > > >
>> > > > Pierre Smits
>> > > >
>> > > > *ORRTIZ.COM <http://www.orrtiz.com>*
>> > > > Services & Solutions for Cloud-
>> > > > Based Manufacturing, Professional
>> > > > Services and Retail & Trade
>> > > > http://www.orrtiz.com
>> > > >
>> > > >
>> > > > On Tue, Apr 1, 2014 at 10:00 AM, Pierre Smits <
>> pierre.sm...@gmail.com
>> > > >wrote:
>> > > >
>> > > >> Rupert,
>> > > >>
>> > > >> A date should not be stored as a date-time, but as a date. This
>> > appears
>> > > >> throughout the entire spectrum of apps where dates are intended.
>> Over
>> > > 600
>> > > >> entity fields are designated as date-time, 18 entity fields are
>> > > designated
>> > > >> as date and 8 as time.
>> > > >>
>> > > >> Regards,
>> > > >>
>> > > >> Pierre Smits
>> > > >>
>> > > >> *ORRTIZ.COM <http://www.orrtiz.com>*
>> > > >> Services & Solutions for Cloud-
>> > > >> Based Manufacturing, Professional
>> > > >> Services and Retail & Trade
>> > > >> http://www.orrtiz.com
>> > > >>
>> > > >>
>> > > >> On Tue, Apr 1, 2014 at 9:46 AM, Rupert Howell <
>> > > ruperthow...@provolve.com>wrote:
>> > > >>
>> > > >>> There's a definite problem with the way the dates are displayed
> in
>> > > OFBiz.
>> > > >>> If you enter a birthday with your local timezone set to UTC,
> then
>> > > change
>> > > >>> the timezone to -12, the birthday changes to the previous day.
> This
>> > is
>> > > >>> clearly wrong and is really apparent if you have your Server
>> Timezone
>> > > set
>> > > >>> to GB. If the birthday is within BST (April - October) and you
> are
>> in
>> > > GMT
>> > > >>> (Nov - March) they all appear incorrectly and vice versa.
>> > > >>>
>> > > >>> Ultimately this is caused by line 977 UtilDateTime
>> > > >>>
>> > > >>> f.setTimeZone(tz);
>> > > >>>
>> > > >>> Can anyone think of a legitimate reason why a date would have a
>> > > timezone
>> > > >>> applied? A date is a date. January 1st is January 1st no matter
>> where
>> > > in
>> > > >>> the world you are. I would have thought if you want a date to
be
>> > > timezone
>> > > >>> dependent you'd use a Timestamp.
>> > > >>>
>> > > >>> I could patch line 666 of ModelFormField but I think it would
be
>> > better
>> > > >>> to
>> > > >>> actually change the UtilDateTime method..
>> > > >>> --
>> > > >>> Rupert Howell
>> > > >>>
>> > > >>> Provolve Ltd
>> > > >>> Front Office, Deale House, 16 Lavant Street, Petersfield, GU32
> 3EW,
>> > UK
>> > > >>>
>> > > >>> t: 01730 267868 / m: 079 0968 5308
>> > > >>> e: ruperthow...@provolve.com
>> > > >>> w: http://www.provolve.com
>> > > >>>
>> > > >>
>> > > >>
>> > > >
>> > >
>> >
>> >
>> >
>> > --
>> > Rupert Howell
>> >
>> > Provolve Ltd
>> > Front Office, Deale House, 16 Lavant Street, Petersfield, GU32 3EW,
UK
>> >
>> > t: 01730 267868 / m: 079 0968 5308
>> > e: ruperthow...@provolve.com
>> > w: http://www.provolve.com
>> >
>> >
>> >
>> > This email is intended only for the above addressee. It may contain
>> > privileged information. If you are not the addressee you must not
> copy,
>> > distribute, disclose or use any of the information in it. If you have
>> > received it in error, please delete it and notify the sender.
>> >
>> > Stannah Lift Holdings Ltd registered No. 686996, Stannah Management
>> > Services Ltd registered No. 2483693, Stannah Lift Services Ltd
> registered
>> > No. 1189799, Stannah Microlifts Ltd registered No. 964804, Stannah
> Lifts
>> > Ltd registered No. 1189836, Stannah Stairlifts Ltd registered No.
>> 1401451.
>> >
>> > All registered offices at Watt Close, East Portway, Andover,
> Hampshire,
>> > SP10 3SD, England.
>> >
>> > All Registered in England and Wales.
>>
>>
>>
>>
>> --
>> Rupert Howell
>>
>> Provolve Ltd
>> Front Office, Deale House, 16 Lavant Street, Petersfield, GU32 3EW, UK
>>
>> t: 01730 267868 / m: 079 0968 5308
>> e: ruperthow...@provolve.com
>> w: http://www.provolve.com
>>
>
>
> This email is intended only for the above addressee. It may contain
> privileged information. If you are not the addressee you must not
> copy, distribute, disclose or use any of the information in it. If
> you have received it in error, please delete it and notify the sender.
>
> Stannah Lift Holdings Ltd registered No. 686996, Stannah Management
> Services Ltd registered No. 2483693, Stannah Lift Services Ltd
> registered No. 1189799, Stannah Microlifts Ltd registered No.
> 964804, Stannah Lifts Ltd registered No. 1189836, Stannah Stairlifts
> Ltd registered No. 1401451.
>
> All registered offices at Watt Close, East Portway, Andover,
> Hampshire, SP10 3SD, England.
>
> All Registered in England and Wales.
>
This email is intended only for the above addressee. It may contain privileged
information. If you are not the addressee you must not copy, distribute,
disclose or use any of the information in it. If you have received it in error,
please delete it and notify the sender.
Stannah Lift Holdings Ltd registered No. 686996, Stannah Management Services
Ltd registered No. 2483693, Stannah Lift Services Ltd registered No. 1189799,
Stannah Microlifts Ltd registered No. 964804, Stannah Lifts Ltd registered No.
1189836, Stannah Stairlifts Ltd registered No. 1401451.
All registered offices at Watt Close, East Portway, Andover, Hampshire, SP10
3SD, England.
All Registered in England and Wales.
