Simple SQL Problem

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by Twinkie, Jun 12, 2009.

  1. Twinkie

    Twinkie Banned

    Messages:
    1,389
    Likes Received:
    12
    Trophy Points:
    0
    Why is SQL saying now() is an invalid default value for a datetime field?
    Code:
    CREATE TABLE Catagories (
    ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL UNIQUE,
    Created DATETIME DEFAULT NOW(),
    Modified DATETIME DEFAULT NOW(),
    Notes VARCHAR(255)
    )
    
    ~Error: Invalid default value for 'Created'
    Looks fine to me?
     
    Last edited: Jun 12, 2009
  2. garrensilverwing

    garrensilverwing New Member

    Messages:
    148
    Likes Received:
    0
    Trophy Points:
    0
    im not sure but i dont think you can store functions inside of a sql database and have it run the function, however sql has a built in way to put timestamps in, try this:

    Code:
    CREATE TABLE IF NOT EXISTS `test` (
      `id` int(11) NOT NULL auto_increment,
      `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `created` timestamp NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
    
    you will need to add a way to put the timestamp for the created, such as the php function date()
    also, you can only have one field time be timestamped otherwise you will get this error
    Code:
    Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 
    
     
  3. mrfish

    mrfish New Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Yah, I just got back into MySQL and I'm pretty sure your cannot store NOW() as a property. Correct me if I'm wrong though. The only way you should use the now() function is in your php script when the 'whatever' if created and/or modified. Then you can add/rewrite the current value to now().
     
  4. Twinkie

    Twinkie Banned

    Messages:
    1,389
    Likes Received:
    12
    Trophy Points:
    0
    Ok, thanks for your help :)
     
  5. gomarc

    gomarc Member

    Messages:
    516
    Likes Received:
    18
    Trophy Points:
    18
    Source: http://dev.mysql.com/doc/refman/5.0/en/create-table.html
     
  6. vol7ron

    vol7ron New Member

    Messages:
    434
    Likes Received:
    0
    Trophy Points:
    0
    MySQL over PostgreSQL? tsk tsk
     
  7. batman1

    batman1 New Member

    Messages:
    92
    Likes Received:
    0
    Trophy Points:
    0
    default value cannot be a function. put some time. 0000-00-00 00:00:00
     
  8. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    I usually allow null values in my timestamp fields since some programs and scripts have trouble processing 0000-00-00 00:00:00.

    Or you could setup the database field to hold an integer and directly store the number of seconds since the epoch, as a lot of php scripts are doing.
     
    Last edited: Jun 13, 2009
  9. Twinkie

    Twinkie Banned

    Messages:
    1,389
    Likes Received:
    12
    Trophy Points:
    0
    I understand the reasoning for using a timestamp rather than a datetime field, but for the sake of maintainability for a small project, I chose the field that would be easier to read. If this was a bigger project I would be concerned of the speed of turning datetime into a timespamp value for processing.

    I am making a little Ebay for me and my mom to sell some of our old junk.
     
  10. cmstheme

    cmstheme New Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Cms theme

    :drool: I guess everyone is welcome here lol. :cool:
     
  11. xav0989

    xav0989 Community Public Relation Community Support

    Messages:
    4,467
    Likes Received:
    95
    Trophy Points:
    0
    Re: Cms theme

    cmstheme, unless you are willing to add some valuable information, or actually give your opinion, could you please refrain from posting just to post. Oh and by the way, there is an introduction section it's just around the corner:

    Ok I admit it, I was pretty harsh, so, yes everyone is welcome, but have a little bit of retenue, please!
     
    Last edited: Jun 15, 2009

Share This Page