Skip to main content
A
Anonymous
November 5, 2009
Question

MySql timestamp data type issue

  • November 5, 2009
  • 2 replies
  • 5372 views

Using ColdFusion 8 and MySql 5.0.77

For any of my innoDB tables with columns of data type "timestamp" with or without default values, I get a .0 concatenated to the end. This does not display when viewing data from within MySql Administrator only once it's in ColdFusion.

Example:

2009-11-04 17:39:35.0

it should be:

2009-11-04 17:39:35

I've done some googling but can't seem to find what is causing this.

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    November 5, 2009

    sing ColdFusion 8 and MySql 5.0.77

    For
    any of my innoDB tables with columns of data type "timestamp" with or
    without default values, I get a .0 concatenated to the end. This does
    not display when viewing data from within MySql Administrator only once
    it's in ColdFusion.

    Example:

    2009-11-04 17:39:35.0

    it should be:

    2009-11-04 17:39:35

    I've done some googling but can't seem to find what is causing this.

    Nicora, hang on to your instincts. I do believe you are right. It looks fishy. Coldfusion does something similar with

    <cfset x.y=1>
    <cfdump var="#serializeJSON(x)#">

    where it returns the value 1.0. I would call it a Coldfusion bug. If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.

    ilssac
    ilssac
    Inspiring
    November 5, 2009

    BKBK wrote:

    If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.

    But I have never seen a database store a timestamp data type as '2009-11-04 17:39:35'  I have only seen that when date and|or time values are stored as a string, which is usually a bad practice.  For date, time and timestamp date types, the actual data is usally something akin to 235798454.  This is then cast into a string like "2009-11-04 17:39:35" when humans are looking at it.

    A
    Adam Cameron.
    Inspiring
    November 5, 2009

    ianskinner wrote:

    BKBK wrote:

    If the database stores the timestamp as 2009-11-04 17:39:35, Coldfusion has no business casting the value to 2009-11-04 17:39:35.0.

    But I have never seen a database store a timestamp data type as '2009-11-04 17:39:35'  I have only seen that when date and|or time values are stored as a string, which is usually a bad practice.  For date, time and timestamp date types, the actual data is usally something akin to 235798454.  This is then cast into a string like "2009-11-04 17:39:35" when humans are looking at it.

    Yup.  It seems people are not distinguishing between "what's being displayed on the screen" and "what's being stored under the hood".

    For argument's sake, I run this code from CF:

    <cfquery datasource="travel">
        INSERT INTO tbl_ts (
            dt
        ) VALUES (
            GETDATE()
        )
    </cfquery>

    <cfquery name="qTs" datasource="travel" maxrows="1">
        SELECT        top 1 dt
        FROM        tbl_ts
        ORDER BY    ts desc
    </cfquery>

    <cfoutput>
    DT<br />
    raw: #qTs.dt[1]#<br />
    ms: #timeFormat(qTs.dt[1], "lll")#<br />
    </cfoutput>

    This outputs:

    DT
    raw: 2009-11-06 09:51:45.527
    ms: 527

    However if I look at the table data using MS SQL Server Management Studio as my client, I see this:

    6/11/2009 9:51:45 a.m.

    This doesn't mean that SQL Server is storing the data as "6/11/2009 9:51:45 a.m." and CF is somehow a) getting it wrong; b) making up the millseconds out of thin air, it just means thatManagement Studio is choosing to display the data like that, and CF is chosing to display it differently.

    This is a non-issue.

    --

    Adam

    D
    Dan_Bracuk
    Inspiring
    November 5, 2009

    Either use dateformat and timeformat to display it,

    or

    if mySql has the functionality, select a string version of that field with your desired format in your select query.

    C
    craigkaminsky
    Inspiring
    November 5, 2009

    To add onto Dan's post regarding MySQL. If you want to convert in the DB, you can use the following:

    select concat(
         year(your_date_field), '-', month(your_date_field), '-', day(your_date_field, ' ', 
         hour(your_date_field), ':', minute(your_date_field), ':', second(your_date_field) 
    )
    from your_table

    I'm partial to Date/Time formation functions in CF but thought have, on occasion, opted to do this stuff in the DB and thought I'd post the suggestion in case it helps you.

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