SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    timestamp or date?

    Hi guys,

    Given that you can create a date out of a timestamp.. and vice versa.. and given that the sorting algorithms etc would return the same results with either type, what is the general guideline as to when to use a DATE or DATE TIME data type in a database, and a TIMESTAMP ?

    Any advice would be helpful.. !

    Many thanks,
    Alex

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    here is a general guideline

    if you want to store only dates, no times, use DATE

    if you want to store dates and times, use DATETIME

    this is as far as you can go without knowing which database system is involved

    if it's MySQL, and you want a column that the database will update every time you touch the row, use TIMESTAMP

    if it's SQL Server, and you want a column that guarantees uniqueness for version-stamping your rows, use TIMESTAMP (note that SQL Server's TIMESTAMP is an undecipherable binary number that bears no relation to the current datetime)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi r937, thanks for answering my questions (this as well as those on the other threads)

    in the context of the forum script I was doing, it may be required that a user can edit their posts.. but I would also need to know the date the post itself was created, so in this case would I use something like:

    creation_date : DATE TIME (which would never change and just be a representation of the date/time the post was first submitted)
    last_update : TIMESTAMP (which would change on UPDATE and record the last time this record was changed)

    and when the record (i.e. the post) is first created obviously these would have the same value (but obviously not the same format)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    be careful with using TIMESTAMP for "last updated"

    sometimes, you may want to make global changes to your data, e.g. instead of using <b>...</b> you want to replace these with <strong>...</strong> in every post where they occur

    this is an example of a trivial update, yes? at least, from the point of view of the users' data

    if you use DATETIME for last_updated, you can go ahead, and not disturb anything, but if you use TIMESTAMP, then every row you touch will have its last_updated value changed

    i personally would use DATETIME instead of TIMESTAMP for any column that users can see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    be careful with using TIMESTAMP for "last updated"

    sometimes, you may want to make global changes to your data, e.g. instead of using <b>...</b> you want to replace these with <strong>...</strong> in every post where they occur

    this is an example of a trivial update, yes? at least, from the point of view of the users' data

    if you use DATETIME for last_updated, you can go ahead, and not disturb anything, but if you use TIMESTAMP, then every row you touch will have its last_updated value changed

    i personally would use DATETIME instead of TIMESTAMP for any column that users can see
    Hmmmmm, that is a good point. But that will mean a manual query to update the last_updated column, yes?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by old_iron View Post
    But that will mean a manual query to update the last_updated column, yes?
    um.... what other kinds of update queries are there?



    the point is, when you are updating the actual contents of a row, all you have to do is add SET last_updated = CURRENT_TIMESTAMP

    interestingly enough, the standard sql word for the current datetime value is CURRENT_TIMESTAMP

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •