ColdFusion 10.0  -  Feature 3309220

Created on Monday, August 13, 2012

Login for more options

Title

ColdFusion 10 fails to insert utc datetime in right format

Description

Problem: ColdFusion 10 can't convert "#'utc Datetime with ODBC ' & utcDate#' in to the correct format while inserting in to the database.

Method:
1. Install ColdFusion 10 on machine with built-in server
2. Place the scribble.cfm page in side the ColdFusion 10 root folder
3. Run this page in the browser

Result: The utc time value gets changed after inserting in to the database. The created utcDate (2012-08-10 11:20:37) will be inserted as 2012-08-10 13:20:37. It seems that there is an implicit conversion.

Images of have been attached.

Expected: It shouldn't change, as it was working fine in ColdFusion 9.

Workaround: NA

Test Configuration

App Language(s) English
OS Language(s) English
Platform(s) Win XP All, Mac 10 All
Browser(s)

Notes (3)

  • itisdesign

    3:49:19 PM GMT+00:00 Dec 15, 2012

    Hi Himavanth,

    (just copying this from: http://blogs.coldfusion.com/post.cfm/coldfusion-10-release-notes)

    In CF10's Release Notes, I didn't find mention this: CF10 changed the meaning of utc2local and local2utc.

    IMO, this is an important issue that deserves public input (bugbase doesn't facilitate this, since it doesn't notify commenters of new comments).

    The CF9 and CF10 documentation say:
    1) dateConvert() returns "UTC- or local-formatted time object"
    2) local2utc "Converts local time to UTC time"

    In Pre-CF10 and CF10, #1 is true. The value displayed on screen is UTC in both pre-CF10 and CF10.

    In Pre-CF10, #2 is also true. In CF10, #2 is ONLY true based on INTERPRETATION of the meaning of "Converts local time to UTC time".

    If reader interprets that as "Converts value from local time to UTC time", then #2 is untrue in CF10.

    Example:

    <cfscript>
    time1 = time2 = now();
    writeOutput(dateConvert("local2utc", time1) is dateAdd("s", getTimeZoneInfo().utcTotalOffset, time2));
    </cfscript>

    When server's time zone is not UTC:
    1) above code returns Yes in Pre-CF10 and No in CF10
    2) cfqueryparam sends value as UTC in Pre-CF10 but as local in CF10
    3) serializeJSON() outputs value as UTC in Pre-CF10 but as local in CF10

    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

    The proposed workaround (the JVM arg to restore CF9 behavior) doesn't seem to be of use to the users most affected by this: Shared hosting customers. Shared hosting servers are typically not in UTC time zone and shared hosting customers typically lack the required access for applying a JVM argument.

    Others have mentioned this before, but I don't see it documented yet. I just saw another bug (#3364479) filed over a month ago regarding this, but no feedback from Adobe on that ticket yet.

    Can this please be both blogged about and documented since it silently breaks migrated apps?

    Thanks,
    -Aaron

  • Himavanth Rachamsetty

    5:52:25 AM GMT+00:00 Oct 22, 2012

    Looking at the impact this had had on previous applications, we have decided to fix the second part of this bug as well.
    That will be tracked as part of bug # 3347145

  • Himavanth Rachamsetty

    11:53:25 PM GMT+00:00 Sep 12, 2012

    This has been fixed partially.
    The bug where datetime functions like dateformat, timeformat and createodbcdate (when used on a utc date) return a local date instead of utc date has been fixed.
    But the issue of getting the local date from db instead of utc date should not be fixed though it is a regression.
    This happens because the db stores only the timestamp and the timezone information is lost.
    It was working in earlier releases because of a bug in the implementation.

Duplicate ID
Reported By Swaraj Kumar Mitra

Status

State Closed
Status Fixed
Reason

Importance

Priority 0-Unknown
Frequency Unknown
Failure Type Unspecified
Product Area Database

Build

Found In Build 282462
Fixed In Build 282877, 282898

Attachments (3)

Votes (1)

  • redtopia

    10:26:27 PM GMT+00:00 Dec 26, 2012

    Just ran into this problem... trying to insert UTC date/time into sql server using this code:

    rightNow = Now();
    nowUTC = DateConvert("Local2UTC", rightNow);
    crDateODBC = CreateODBCDateTime (nowUTC);

    the variable nowUTC is correct, but crDateODBC show the local time.

Your session has expired! Click to login
Current form data will be preserved

Cancel