Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2002
    Posts
    173

    Unanswered: getting record to automatically put in default date

    is there something similar to "timestamp" column type or a way of having a "date" field automatically insert today's date when the record is created. I want it to do something similar to "timestamp" but i don't want it to change everytime the record is edited/updated. It would just be something like a record creation date. any help would be appreciated. thanks.

  2. #2
    Join Date
    Apr 2002
    Posts
    56
    Add a date column to the table and assign a default value to it.
    Then you don't mention it in the INSERT statement, but it gets filled with that default.

    default value: SQL Server getdate()
    Oracle sysdate
    MySQL ??

    I'm more into SQL Server and Oracle, so I don't know het 'now'-function
    in MySQL.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unfortunately, in mysql the DEFAULT for a column cannot be a function

    however, the first TIMESTAMP column in a table will be updated automatically

    you can prevent it from being updated on every UPDATE by following the suggestions in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2002
    Posts
    173
    From what i've read in the mysql manual "timestamp" behaves differently whether or not mysql is in maxdb mode or not. if in maxdb mode the timestamp will not update to current date/time when the record is updated.

    i need to have two columns in my tables. one field that will have a record creation date/time that is defaulted to current date when record is created only. then the second date field will update to current date/time whenever the record is changed. is this possible? i need a solution that will work in maxdb mode or not. why would mysql change the way the 'timestamp' field type behaves depending on maxdb mode or not?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    make the second one the first one with a TIMESTAMP datatype

    it will get updated every row update

    make the first one the second one with DATETIME datatype

    assign the value explicitly when inserting

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2002
    Posts
    173
    thanks for your suggestions.

    did i understand the mysql manual correctly that the 'timestamp' field updates or does not update on a record change depending on whether maxdb is enabled?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    12.5.1 The DATETIME, DATE, and TIMESTAMP Types

    ?

    12.5.1.1 TIMESTAMP Properties Prior to MySQL 4.1

    ?

    12.5.1.2 TIMESTAMP Properties as of MySQL 4.1

    aha

    "In addition, if the MySQL server is running in MAXDB mode, TIMESTAMP is identical with DATETIME. That is, if the server is running in MAXDB mode at the time that a table is created, any TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and no automatic updating occurs. "

    so if you want automatic timestamp updating, don't run in maxdb mode, is the conclusion i would draw from that

    what's maxdb?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2002
    Posts
    173
    i don't know anything about maxdb mode but i'm searching. if you find anything out let me know. thanks.

  9. #9
    Join Date
    May 2004
    Posts
    1

    Ading Current Date with mysql automatically

    Use the function CURDATE()

    here is an example of using the above function. Remember to set the date field (`today` field in the following example) to type DATE

    INSERT INTO `table_name` ( `id` , `entry` , `today`)
    VALUES ('', 'dummy entry', CURDATE( ) );

Posting Permissions

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