tracker issue : CF-3347145

select a category, or use search below
(searches all categories and all time range)

Extension to 3309220 . Change of behaviour from CF 9 when persisting UTC date/time

| View in Tracker

Status/Resolution/Reason: Closed/Fixed/

Reporter/Name(from Bugbase): / ext-user (Himavanth Rachamsetty)

Created: 10/16/2012

Components: Language, Functions

Versions: 10.0

Failure Type:

Found In Build/Fixed In Build: Final / 283156, 285311, 286044

Priority/Frequency: Major / Some users will encounter

Locale/System: English / Mac 10 All,Win XP All

Vote Count: 2

UTC date/time values become local date/time values upon inserting in to database.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3347145


External Customer Info:
External Company:  
External Customer Name:  
External Customer Email:


  1. July 11, 2013 00:00:00:
  2. July 11, 2013 00:00:00:
  3. September 24, 2013 00:00:00: 3_utcBug.cfm


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.
17519 | October 16, 2012 02:15:54 AM GMT
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.
17520 | November 02, 2012 06:36:54 PM GMT
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.
17521 | November 05, 2012 01:13:25 AM GMT
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.
17522 | November 05, 2012 08:28:09 PM GMT
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.
17523 | November 06, 2012 04:06:08 AM GMT
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.
17524 | November 06, 2012 04:18:23 AM GMT
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.
17525 | November 06, 2012 12:37:58 PM GMT
"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: Thanks, -Aaron
Comment by External U.
17526 | December 15, 2012 06:54:30 PM GMT
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.
17543 | March 29, 2013 12:03:39 PM GMT
test case at : //depot/qa/cf10/regression/chfsuite10/cf10u11/CF-3347145/ perforce change: 285276
Comment by Piyush K.
17527 | June 18, 2013 09:38:28 AM GMT
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.
17528 | June 24, 2013 07:03:25 AM GMT
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.
17529 | June 24, 2013 10:06:55 AM GMT
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.
17530 | June 28, 2013 11:33:30 AM GMT
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.
17544 | June 28, 2013 11:37:20 AM GMT
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.
17531 | June 28, 2013 12:09:23 PM GMT
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.
17532 | July 01, 2013 10:11:41 AM GMT
Is there a time-frame for this fix? It is pretty important to me.
Comment by External U.
17533 | July 05, 2013 05:09:26 PM GMT
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 along with screenshots of the correct output running under CF8 and the incorrect results from CF10.
Comment by External U.
17534 | July 11, 2013 09:04:54 AM GMT
Please see 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.
17535 | July 11, 2013 09:36:31 AM GMT
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.
17536 | July 11, 2013 12:42:30 PM GMT
Happy to see that my issue ( 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.
17537 | July 15, 2013 11:49:52 AM GMT
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.
17538 | August 22, 2013 07:56:00 PM GMT
I attached utcBug.cfm, a cfm script that demonstrates the issue.
Comment by External U.
17539 | September 23, 2013 04:31:15 PM GMT
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.
17540 | October 05, 2015 02:38:17 AM GMT
Comment by External U.
17541 | December 04, 2015 04:46:21 AM GMT
Aaron, can you file an ER for this?
Comment by Vamseekrishna N.
17542 | October 03, 2016 12:53:18 AM GMT