Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    17

    Unanswered: timestamp and datetime

    any special advantage of datetime except date range that a timestamp can't???

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    timestamp is the date and time the record was last updated
    datetime is a datatype that date and/or time data is stored in
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    13
    For TIMESTAMP column you can have DEFAULT CURRENT_TIMESTAMP that cannot be specified for DATETIME.

    Not a big issue, just another difference between data types. By the way, only one TIMESTAMP column can have this default.

    Dmitry

    --
    http://www.sqlines.com/online - Free SQL conversion between major databases

  4. #4
    Join Date
    Dec 2011
    Posts
    8
    You got the major difference. Storage size is different as a result:

    TIMESTAMP uses 4 bytes of storage and can store date from 1970 to 2037

    DATETIME uses 8 bytes of storage and can store any date/time from 0000-00-00 00:00:00 to 9999-12-31 23:59:59.

    If you are building a time machine, please be sure to use DATETIME.
    Last edited by robcarrol71; 12-08-11 at 15:43.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    timestamp is the date and time the record was last updated
    doesn't has to be, but this is a very common usage
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2011
    Posts
    13
    Quote Originally Posted by robcarrol71 View Post
    DATETIME uses 8 bytes of storage and can store any date/time from 0000-00-00 00:00:00 to 9999-12-31 23:59:59.
    Although DATETIME can store year 0000, the next allowed year is 1000

    Dmitry

    --
    http://www.sqlines.com/online - Free SQL conversion between major databases

  7. #7
    Join Date
    Dec 2011
    Posts
    8
    Quote Originally Posted by dm1 View Post
    Although DATETIME can store year 0000, the next allowed year is 1000
    Oh, very interesting. Thanks for the info

Posting Permissions

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