Skip to main content
A
Anonymous
June 28, 2012
Answered

Date not the same as database!

  • June 28, 2012
  • 2 replies
  • 4301 views

Hi all

I changed my database field type from timestamp to date in mysql as I wanted to get rid of the time after the date. I changed the cfqueryparam cfsqltype to cf_sql_date and so far so good it now updates the correct date into the database without any time: 2012-06-30

When I output the value in my form however it shows a different date for some strange reason:

Fri Jun 29 23:00:00 GMT-0500 2012

This is the code I am using to output the date which has always worked fine showing the correct date. I have taken care to remove anything in the rest of my code that could affect the database result:

<cfformitem type="html" width="90" label="Present" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['MEETING']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'MEETING', MEETING.text);">

UPDATED: I did a cfdump on the query and the record in question shows this value in the cfdump but not on the page:

{ts '2012-06-30 00:00:00'}

Why am I getting the wrong date, do I need to dateformat MEETING.text or something? Quite why the date would be changed I don't understand at all. Thanks for any pointers!

    This topic has been closed for replies.
    Correct answer

    goodychurro1 wrote:

    Hi Aaron

    Thanks for checking it out, it is strange it doesn't happen to your page, what does your page server show for this?

    Hi goodychurro1,

    No problem.  And it displays this: {ts '2012-07-04 01:35:03'} Time Jamaica: Jul 4, 2012 12:35:03 AM

    OS (local machine) tz is EST.  That's the correct time for me, as it was 1:35am EST when I ran that.  And that's the correct time in Jamaica, as they don't observe DST.

    Thanks,

    -Aaron


    Thanks to my hosts at hostek they found the solution!

    I don't have thelink to the original post but you can find it by googling CFGRID Date Display Issues. Here are the solutions!!

    1. When storing the date in the database, specify a time. In my case, setting the time to noon worked for me since my users are all located within the

    continental US (therefore the offset would never be greater then 12 hours - side note that i'm too lazy to look up - can the offset ever be greater than 12

    hours?). Here's an example:

    <cfqueryparam value="#arguments.theDate# #timeformat(createtime(12, 00, 00), "hh:mm")#"

    null="#not len(arguments.theDate)#"

    cfsqltype="cf_sql_timestamp">

    2. When querying the date, do a date add in your query to correct for the offset.

    3. After querying, loop over the query column and do a date add to correct for the offset.

    4. I suppose you could always convert the date to varchar in the query, but Flash still might recognize as a date? Also, sorting would probably be

    broken...this one's probably not a good idea anyways.

    2 replies

    itisdesign
    itisdesign
    Inspiring
    July 1, 2012

    Hi all,

    Just FYI: A ticket was filed to add setTimeZone() (and possibly a THIS.timeZone per-app setting) to CF.  This would permit setting time zone at app-level (instead of requiring server-level config).  If you like this idea, you can vote for #3035908 here.  It was noted that Railo has setTimeZone().  Adobe ColdFusion 10 does not yet.

    Thanks!,

    -Aaron

    A
    Anonymous
    July 1, 2012

    Thanks for all the input, I have been reading a lot about this on various sites including ben nadell's, he has some interesting info there, the main problem seems to be with the way that coldfusion interprets dates which is different to the way that java does. 

    I just tested the variables again on my (flash) cfform

      

    <cfoutput>#now()#</cfoutput>

    <cfscript>

    tzoneObj    = createObject("java","java.util.TimeZone");

    tzone       = tzoneObj.getTimeZone("Jamaica");

    date_format = createObject("java","java.text.DateFormat").getDateTimeInstance();

    date_format.setTimeZone(tzone);

    writeoutput("<strong>Time Jamaica: </strong>"&#date_format.format(now())#);

    </cfscript>

    Results:

    {ts '2012-07-01 14:01:57'}

    Time Jamaica: Jul 1, 2012 1:01:57 PM

    So I guess the answer must be as BKBK says, even though I thought my host's servers were in a different place that they appear to be!

    I finally changed my flash cfform and cfgrid to html and hey presto it now works with the proper date, so I think I can conclude that in this case it was flash playing havoc with everything again for a change. Note to self: never use flash with coldfusion ever again.  I'll also vote for the timezone requirement that Aaron suggests, quite why coldfusion doesn't have it already is beyond me.

    Also while we are on the subject why can't flash cfforms be seen in safari and opera? Yet another reason to ditch them. I haven't even tried looking on them in an ipad but I imagine they won't work there either due to the flash issue.

    itisdesign
    itisdesign
    Inspiring
    July 1, 2012

    Hi goodychurro1,

    Those results indicate (assuming you ran that code right before posting the results):

    1) the server's total UTC offset was -4

    2) Jamaica's total UTC offset was -5

    Possible reason for #1: Server's tz is EST (-5) and currently observes DST (+1) for a total offset of -4.

    Reason for #2 is b/c Jamaica does not observe DST.  It is always -5.

    So the result you saw for Jamaica appears to be correct.  I likely misunderstood the issue.  What were the results you were expecting?

    As for the Flash Player issue, there is an update: http://forums.adobe.com/message/4511145

    As for the <cfform format="flash" on iPad, currently <cfform format="flash" forms don't automatically fallback to HTML5.

    Thanks,

    -Aaron

    A
    Anonymous
    June 28, 2012

    I'm just asking my hosts if it could be due to locale issues or something as the date Fri Jun 29 23:00:00 GMT-0500 2012 maybe a timestamp from the server, I don't really know clutching at straws a bit on this one but at least I'm learning!

    A
    Adam Cameron.
    Inspiring
    June 28, 2012

    What TZ are you in?  Or, yeah, what TZ is the server in?

    --

    Adam

    A
    Anonymous
    June 28, 2012

    I'm in the Central Standard Time (CST) timezone and the server is in GMT.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices