SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Date functions results in 01/01/0001

    Hi

    Using MySQL 4.1.7, I have 1 timestamp column which is:
    Code:
    insert_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    I do an INSERT query using 'NULL', but when I go to view the date in Navicat, I see 01/01/0001. I thought this was supposed to give me the current time. Using just TIMESTAMP (no args) gives 01/01/0001 too.

    Same thing with DATETIME and using NOW() in the insert query - I just get 01/01/0001.

    Any ideas on why?

    Thanks

    Andy

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As of 4.1.2, you have more flexibility in deciding which TIMESTAMP column automatically is initialized and updated to the current timestamp. The rules are as follows:

    If a DEFAULT value is specified for the first TIMESTAMP column in a table, it is not ignored. The default can be CURRENT_TIMESTAMP or a constant date and time value.

    DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.

    Any single TIMESTAMP column in a table can be set to be the one that is initialized to the current timestamp and/or updated automatically.

    In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways:

    With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value, and is automatically updated.
    and

    Before MySQL 4.1.6 (and even as of 4.1.6 if the NULL attribute is not specified), setting the column to NULL sets it to the current timestamp.
    - but it doesn't (if using NULL or left blank).

  4. #4
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE query
    Code:
    CREATE TABLE article 
    (
    article_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    body MEDIUMTEXT NOT NULL,
    insert_date DATETIME,
    PRIMARY KEY (article_id)
    ) TYPE = MYISAM;
    INSERT Query:
    Code:
    INSERT INTO article (article_id, body, insert_date) 
    VALUES ('1','body','NOW()');
    Resulting insert date: 01/01/0001

  5. #5
    SitePoint Addict evilone's Avatar
    Join Date
    Oct 2004
    Location
    Estonia
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    Code:
    INSERT INTO article (article_id, body, insert_date) 
    VALUES ('1','body',NOW());

  6. #6
    SitePoint Member
    Join Date
    Sep 2004
    Location
    UK
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Lol, thanks for the tip evilone - of course that now works without the quotes.
    As for the TIMESTAMP DEFAULT CURRENT_TIMESTAMP issue, it works with using NULL, but doesn't appear to work if NULL isn't specified.

    Thanks for the replies folks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •