tracker issue : CF-3347145

Added a system property coldfusion.preserveTimeZoneOnPersist. It is false by default. When it is set to true, we will change the timestamp before inserting into database for UTC date/time values (as we used to do in CF 9).

Comment by Himavanth R.

Himavanth Rachamsetty, I'm not sure I understand your note. Are you saying that you will have to do something special to make CF10 work like CF9? Or will the original CF9 behavior be the DEFAULT in CF10?

Comment by External U.

To make CF 10 work as CF 9, the system property needs to be set in java.args within jvm.config as below. -Dcoldfusion.preserveTimeZoneOnPersist=true We did not make this the default behaviour of CF10 because the ideal way to deal with this issue is by letting the developers manage the TimeZone conversions. However, since this was a regression, we had to give a workaround which is the system property in this case.

Comment by Himavanth R.

What do you mean by "manage the TimeZone conversions"? You should be able to have a timestamp column and populate it with <cfqueryparam value="#DateConvert("local2Utc", now())#" cfsqltype="cf_sql_timestamp">? Cause that worked before. Now it's broken.

Comment by External U.

I agree with Chris that the DEFAULT behaviour should be to preserve BACKWARDS COMPATIBILITY, not break it. CF should give the *option* for divergent behaviour, but it should - by default - behave the way it usually does. I also disagree that the "ideal way" is to force the developer to deal with this. Why make the developer deal with it if it can be handled automatically (and that's the way it's always worked before)? And, also, I don't think this should be a JVM setting, because that makes it either globally on, or globally off. It should be a DB connection setting (in the DSN). Not an adequate solution, the way you've described it, Himavanth -- Adam

Comment by External U.

The current CF 10 behavior included in the update 4 is correct because when you move the time from one timezone to another, date.getTime() should not change at all. The way date object is defined in Java, the time elapsed since Epoch should not change at all no matter which timezone the date is in. CF 9 and the earlier versions were violating this definition of Java date and were changing the time elapsed since epoch. This caused few issues which we fixed in CF 10. When the date is persisted and retrieved from DB, the JVM automatically converts it back to local timezone. While retrieving a date, we don’t have any mechanism to find out that a date was converted to UTC when it was first inserted and therefore we would not be able to do any conversion ourselves. It is actually the application's responsibility to convert the date/time to appropriate timezone after it is retrieved. Another option to work with date and DB is - you always store date as string in DB and when it is retrieved, convert it back to date. Since this change of behavior in CF 10 could break a lot of applications, we have fixed this by introducing a flag so that the applications can run without any issues. Since this flag changes the way date is defined in Java, we will keep it disabled by default.

Comment by Himavanth R.

That's all lovely, but pls read my first para from my previous post. It doesn't really matter what the Java approach to these things is because we're not working in Java, we're working in CF, and the way it "should work" is the way CF has always worked, unless there is specifically a bug in the way it works. That CF works differently from Java in this case is not "a bug": whether this is divergent from the way Java does it or not is neither here nor there, because CFML has always been abstracted from Java (this is the entire raison d'etre of CF). Bottom line: CFML code worked before, now it doesn't: this is because of a change you made. You've broken backwards compatibility. What's more, you've broken backwards compatibility when you have built in the option to NOT break backwards compatibility! That is TRULY backwards! I agree that it's good to introduce the OPTION to "do it the Java way", but to unilaterally decide that's what we're doing from now on and break existing code goes completely against the way CF is supposed to work. Remember that whole "backwards compatibility is king" mantra you guys trot out when it's convenient for you? Well yeah: it should apply here too. The whole mantra is in existence as a convenience for *us* (the CF developers) not for you (the developers of CF). What was the issue you were fixing when you made this change, btw (ie: which ticket)? -- Adam

Comment by External U.

"the application's responsibility to convert the date/time" Yes, via date"Convert"(). What else would we expect a function named "dateConvert()" to do? The function isn't named "dateMove()", it's named "dateConvert()". From the CF9 and CF10 docs: Returns: UTC- or local-formatted time object. local2Utc: Converts local time to UTC time. utc2Local: Converts UTC time to local time. Reader would naturally expect the string value and the underlying numeric value to BOTH be CONVERTed. The dateConvert() function is now broken b/c it only does half of its job. And even if CF10's behavior was correct (it's not - b/c now it only converts the toString and not the value), the JVM argument does not apply to the customers most affected by this: Shared hosting customers (these servers are typically not UTC and they typically lack the access for applying JVM args). Any applications that were migrated from Pre-CF10 to CF10 are currently experiencing silent data corruption if: 1) the server's time zone is not UTC 2) the app uses dateConvert() on date/time values before storing/sending them or after reading/receiving them This is a serious issue: #CF-3200965 (Open/ToTest/Fixed in build 283493) #CF-3309220 (Open/ToTest/Fixed - Himavanth says Pre-CF10 behavior was incorrect - so is #CF-3200965 really fixed?) #CF-3323800 (Withdrawn/Duplicate of #CF-3309220) #CF-3328875 (Withdrawn/Duplicate of #CF-3323800) #CF-3330732 (Withdrawn/Duplicate of #CF-3323800) #CF-3338974 (Withdrawn/Duplicate of #CF-3309220) #CF-3347145 (Open/ToTest/Fixed - by adding JVM arg coldfusion.preserveTimeZoneOnPersist) #CF-3364479 (Unverified) This change should _never_ have been made until setTimeZone()/getTimeZone() were added to the language. Related discussion: http://blogs.coldfusion.com/post.cfm/coldfusion-10-release-notes Thanks, -Aaron

Comment by External U.

I am adding a vote because I have an 8 year old legacy application that stores its date/time values as their UTC equivalents -- in SQL Server 2005 which has no concept of timezone, no less; the fact that the values are UTC rather than local New Zealand Time (UTC+12 or UTC+13 depending on the time of year) is just assumed by the code. We wanted to get our clients off CF8 since it's well past end-of-life and they are keen to make the upgrade, but this issue seriously breaks the application -- user enters 1-Sep-2010, code converts it to the 2010-08-31 11:00:00 it wants written to the database, CF says too bad and stores 2010-09-01 00:00:00, which the code with then assumes is a UTC value and interprets as 2010-09-01 12:00:00 NZT. I don't even want to think about the effort it would take to revisit the date-handling in this app to make it continue to work correctly with this change in behaviour, and this will seriously impact my clients' time frame for making the upgrade. Is there a time-frame for releasing some solution for this? Is it still being worked on? Is anybody out there? I've already had to tell my clients that an upgrade to CF10 is out of the question until this issue has been addressed and that Adobe is unresponsive on the subject.

Vote by External U.

test case at : //depot/qa/cf10/regression/chfsuite10/cf10u11/CF-3347145/ perforce change: 285276

Comment by Piyush K.

The JVM argument will not help applications in a shared environment. So we are adding an application setting here. <cfset this.preserveTimeZoneOnPersist=true> This overrides the JVM argument which is false by default. Please be advised, if you set this to true, the fix for bug #CF-3042405 will no longer work. Reason for setting the default to false even though it breaks backward compatibility:- There is no way we could maintain backward compatibility without breaking #CF-3042405 again. The funcionality of dateConvert() has changed as a result of the fix for bug #CF-3042405. Prior to that fix, dateConvert() was not doing the conversions right at DST change boundaries. For example, when user is in pacific time(UTC+8),and does dateConvert() on March 13th 6PM PST to UTC,it becomes March 14th 3AM instead of March 14th 2AM. This is because there was no timezone associated to the object. It simply added the required hours, and since the local timezone is PST, March 14th 2 AM automatically became March 14th 3 AM To fix this, we introduced timezones. Now when dateConvert() is used, the timezone is changed (instead of adding/substracting the required hours).

Comment by Himavanth R.

Can I just refer you back to my previous comment. Is there any chance you can just exert yourselves slightly and fix the two issues so they a) aren't inter-related; b) are fixed in a way that doesn't require bloody settings or switches? Rework the solution to CF-3042405 so it doesn't impact this. And rework this ticket simply go back to simply *working*. I would say that it's very tenuous to consider this situation "fixed". -- Adam

Comment by External U.

Setting the JVM argument -Dcoldfusion.preserveTimeZoneOnPersist=true does not do anything. Dates inserted into the database as shown below are still being changed back to local. <cfqueryparam cf_sql_type="timestamp" value="#dateconvert("local2utc", now())#"> Converting the date to a string "fixes" the problem but this is not an acceptable solution. I need to be able to use dateconvert() as it operated in CF9. Functions like this should not be changed by default in new versions. People rely on these kind of functions throughout their applications and changing them can cause serious issues. By all means change the functionality, but have it disabled by default so that existing applications are not affected and then if needs be add an attribute to the function to turn it on. For me this issue is NOT FIXED and I need it to be fixed urgently. -- Michael.

Comment by External U.

I need this to work as it did in CF9. -Dcoldfusion.preserveTimeZoneOnPersist=true does not seem to have any effect and UTC times are still being converted to local upon insertion into the database.

Vote by External U.

Thanks all for your feedback/comments. We are making the change so that we will have the previous behavior. We are also getting rid of the JVM argument that we had added.

Comment by Rupesh K.

Very happy to see this issue will finally be resolved. We still have not upgraded to ColdFusion 10 because of this issue. Do you have an idea when this update will be released?

Comment by External U.

Is there a time-frame for this fix? It is pretty important to me.

Comment by External U.

I was very pleased to see this listed as having been fixed in Update 11 (and my clients were ecstatic). Unfortunately, the issue is not completely fixed as it does not correctly handle daylight savings time. New Zealand is not currently in daylight savings time. If I store a UTC date/time value for a date when daylight savings time is not in effect, the value is stored correctly. But if I store a UTC date/time value for a date when daylight savings time *was* in effect, the resulting value in the database is off by an hour. Presumably the current offset was applied, rather than the offset in effect for the particular date. I have adapted the code sample from CF-3309220 to demonstrate this (assuming you run it on a machine set to a timezone which has daylight savings time rules, e.g. Pacific/Auckland or US/Eastern). Please find this attached in CF10_UTC_DST_Bug.zip along with screenshots of the correct output running under CF8 and the incorrect results from CF10.

Comment by External U.

Please see CF10_UTC_DST_Bug-Corrected.zip as the original had the DST and non-DST values reversed (I'm in the US and my clients in NZ are the opposite of what we are here)

Comment by External U.

Thank you for fixing this. Unfortunately I am seeing the DST problem also. We are still unable to complete the move to CF10 because of this.

Comment by External U.

Happy to see that my issue (https://bugbase.adobe.com/index.cfm?event=bug&id=CF-3338974) that was withdrawn and never fixed is finally being fixed under a different bug ID. Looking forward to it being FULLY fixed instead of almost fixed.

Comment by External U.

Just upgraded to CF10 on an eCommerce site and this bug breaks the Authorize.NET interface. Authorize.NET requires a UTC timestamp and their sample code <cfset timestamp=DateDiff("s", "January 1 1970 00:00", DateConvert('local2UTC', Now())) > returns local time instead.

Comment by External U.

I attached utcBug.cfm, a cfm script that demonstrates the issue.

Comment by External U.

Hi Rupesh, Regarding "We are making the change so that we will have the previous behavior. We are also getting rid of the JVM argument that we had added.": When will the previous behavior be restored and the JVM argument removed? Then, the current behavior could be allowed by adding a myDate.timeZone(newTZ[, adjustDT]) member function. The newTZ parameter would be the new time zone designation to apply. The adjustDT boolean parameter would signify if the date/time should also be adjusted. And datePart()/myDate.datePart() could also accept "tz" for the datepart parameter in order to return the time zone. And we should be able to set the app's local time zone via a THIS.timeZone per-app setting (instead of CF having to get TZ from the JVM). And myDate.timeZone("local" would set myDate's time zone to the one set in THIS.timeZone. Thanks!, -Aaron

Comment by External U.

*bump*

Comment by External U.

Aaron, can you file an ER for this?

Comment by Vamseekrishna N.