Gareth, True. But if there is a misalignment of the db timezone and timezones applied/determined in OFBiz (general - from properties, tenant setting, organisation default or user specific) then overviews (displaying aspects related as previous month/week, yesterday, today, tomorrow and next week/month) relative to current date of the user might yield unexpected and unwanted results.
Regards, 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:46 PM, <gareth_car...@stannah.co.uk> wrote: > 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. >
