Skip navigation
goodychurro1
Currently Being Moderated

Date not the same as database!

Jun 28, 2012 7:53 AM

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!

 
Replies
  • Currently Being Moderated
    Jun 28, 2012 2:31 PM   in reply to goodychurro1

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

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 1, 2012 12:41 AM   in reply to goodychurro1

    The time, Fri Jun 29 23:00:00 GMT-0500 2012, is the time at your locale(local time = Fri Jun 29 11:00 PM). The time, {ts '2012-06-30 00:00:00'}, simply tells us your server has an offset of +5 hours away from you (server local time = Sat Jun 30 04:00 AM).

     

    These times are equivalent universally. However, you should be saving the server time, not the client time. Client times are arbitrary. For example, if I ran the same page from The Netherlands, I would have observed the time as Sat Jun 30 06:00 GMT+0200 2012.

     

    My advice is that you save your datetimes in the database as UTC, and be done with all the relativity. As your server is in the GMT zone, that is what it is doing anyway. To obtain a client's local time, calculate it from the saved UTC and the client's time zone.

     

     

     

     

     

     

     

     

    [Edited by BKBK]

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 1, 2012 10:46 AM   in reply to goodychurro1

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 1, 2012 12:18 PM   in reply to goodychurro1

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 1, 2012 9:32 PM   in reply to goodychurro1

    goodychurro1 wrote:

     

    Hi Aaron yes those were the results I was expecting, the problem is that the value in my database timestamp column is 2012-06-30 but when I show it in the flash cfform it is shown as 2012-06-29!

    It works fine if the cfform is html. Strange stuff.

    Hi goodychurro1,

     

    Sorry I'm unable to reproduce this.  Steps I took:

     

    1) In MSSQL Server 2008 R2, I created a new table named 'myTable' w/ a column named 'myDateField' of type 'date'

    2) I populated the 1st row w/ the value 2012-06-30

    3) I ran the following code:

     

    <cfquery name="q">

    SELECT myDateField FROM myTable

    </cfquery>

    <cfform>

      <cfinput type="text" name="myField" value="#q.myDateField[1]#" />

    </cfform>

    <cfform format="flash">

      <cfinput type="text" name="myField" value="#q.myDateField[1]#" />

    </cfform>

     

    Both input fields displayed {ts '2012-06-30 00:00:00'}

     

    Thanks,

    -Aaron

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 2, 2012 9:57 PM   in reply to goodychurro1

    Hi goodychurro1,

     

    Please see the attached image.  Here are the steps I took:

     

    1) Enabled the bind, by changing this:

     

    <cfinput type="DateField" name="ABUNDANCE" label="Action Date" width="100" >

      <!--- bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);"--->

     

    to this:

     

    <cfinput type="DateField" name="ABUNDANCE" label="Action Date" width="100" bind="{UsersGrid.dataProvider[UsersGrid.selectedIndex]['ABUNDANCE']}" onChange="UsersGrid.dataProvider.editField(UsersGrid.selectedIndex, 'ABUNDANCE', ABUNDANCE.text);">

     

    2) I loaded the page and selected the 1st row in the grid.  The value for ABUNDANCE was correctly displayed as 2012-07-24 in the grid and in the datefield.

    3) I reloaded the page and entered the following data: Company: "new two", Sector="cars", Department="Finance", Name="foo", Action Date=(I left this empty)

    4) The value for ABUNDANCE was correctly displayed as 2012-07-05 in the database, and in the grid, and in the datefield.

    5) I reloaded the page and entered the following data: Company: "new three", Sector="cars", Department="Finance", Name="bar", Action Date=(I selected July 5, 2012 from the date picker)

    6) The value for ABUNDANCE was correctly displayed as 2012-07-05 in the database, and in the grid, and in the datefield.

     

    Are these the expected results?

     

    goodychurro1_01.jpg

     

    Thanks,

    -Aaron

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 2, 2012 10:23 PM   in reply to goodychurro1

    goodychurro1 wrote:

     

    <cfquery name="qNames" datasource="tester">
        select * from company, industries, division
    where company.SECTORS1 = industries.SECTORSIDS
    <cfif structKeyExists(url,"sectorid")>
    and industries.SECTORSIDS = #url.sectorid#</cfif>
    and division.DEPARTMENTIDS = company.DEPARTMENT1
    and company.WHEN = 2
    ORDER BY company</cfquery>

     

    One more note.. I'd change this:

     

    <cfif structKeyExists(url,"sectorid")>and industries.SECTORSIDS = #url.sectorid#</cfif>

     

    to this:

     

    <cfif structKeyExists(url,"sectorid") and isNumeric(url.sectorid)>and industries.SECTORSIDS = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(url.sectorid)#" /></cfif>

     

    Thanks,

    -Aaron

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 3, 2012 12:10 AM   in reply to goodychurro1

    goodychurro1 wrote:

     

    <cfformitem type="html" width="90" label="Research" bind="<a href="?q={COMPANY.text}"">Google</a>">
       </cfformitem>

    Almost forgot..  I also had to remove that, since the syntax is wrong.  Example:

     

    <!---<cfformitem type="html" width="90" label="Research" bind="<a href="?q={COMPANY.text}"">Google</a>">

       </cfformitem>--->

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 3, 2012 10:39 PM   in reply to goodychurro1

    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

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points