Skip navigation
goodychurro1
goodychurro1
Currently Being Moderated

Timestamp in database for new row

May 23, 2012 3:43 PM

I have a timestamp field in my db.
In my mysql program I clicked on the box that says "On Updated Current_Timestamp" which works fine for updating rows in the database and adds the time.
Now I want it to add the time for NEW rows, so in the default box I put now();  but I get an sql error. I am doing it in the database to lessen the load on coldfusion but maybe something like this is better in my cfquery insert into sql?:

 

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#" />

 

What do you think I should do? Thanks

894 Views   7 Replies   Latest reply: goodychurro1, May 25, 2012 7:15 AM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    May 23, 2012 5:57 PM   in reply to goodychurro1
    Report

    What is the code you are running and what is the error?

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    2. goodychurro1,
    May 24, 2012 8:50 AM   in reply to Dan Bracuk
    Report

    Hi Dan

    The field UPDATED is a timestamp field, it has length 0 because it doesn't let me change it, decimals 0 and allow null.

    The Default value I put in the mysql program for the field is: now();

    It has the checkbox for On Update Current_Timestamp selected.

     

    I get this error: 1067 'Invalid default value for value updated'

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    3. Dan Bracuk,
    May 24, 2012 9:13 AM   in reply to goodychurro1
    Report

    I did a google search on "mysql now" and found this page.  http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

     

    I noticed one synonym for now is current_timestamp().  I'd try that with a 0 in the brackets.

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    4. goodychurro1,
    May 24, 2012 9:30 AM   in reply to Dan Bracuk
    Report

    Hi Dan I tried deleting the field UPDATED and then adding it again, I used the following three options:

    1) current_timestamp()

    2) current_timestamp(0)

    3) current_timestamp(0);

     

    But I get this error in mysql: '1067 Invalid default value for UPDATED'

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    5. Dan Bracuk,
    May 24, 2012 9:51 AM   in reply to goodychurro1
    Report

    Since I don't actually use mysql, and nobody else has answered, you might have better luck on a mysql forum.

     
    |
    Mark as:
  • tclaremont
    Currently Being Moderated
    6. tclaremont,
    May 24, 2012 10:28 AM   in reply to Dan Bracuk
    Report

    It it is truly a timestamp field, there should be no need for a default value... no?

     
    |
    Mark as:
  • goodychurro1
    Currently Being Moderated
    7. goodychurro1,
    May 25, 2012 7:15 AM   in reply to tclaremont
    Report

    Thanka for all the input I played around a lot with it and I made it work, this is what I did to the timestamp mysql field:

    Field length 0.

    Decimals 0

    Allow null NO

    Checkbox for On Update Current_Timestamp selected

    Default value CURRENT_TIMESTAMP

     

     

    It now works fine adding/updating the date for both a new and an exisiting record automatically

    Thanks for all the help about dates

     
    |
    Mark as:
Actions

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