SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    Mars
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DateTime vs TimeStamp

    Hi

    I want to develop a web application that needs to store dates and times, as well as to doing some calculations such as the differenece between two dates and etc.

    I am not sure about the best design I can make.
    - should I have two different columns: Date and Time
    - should I have one column of type: DateTime
    - should I have one column of type: TimeStamp

    Could you please explain the best choice and why do we have these different types

    Thanks

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you should use datetime if you want to store a date and time for something. use time if you only want to store a time. use date if you only want to store a date.

    do not use timestamp unless you have read the mysql manual for timestamp and understand the behavior of the timestamp column.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    Mars
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    I've already read the manual for timestamp.
    I think this column type is suitable when you want to automatically mark INSERT or UPDATE operations with the current date and time, for example last login times.

    please confirm

  4. #4
    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)
    yes, that's confirmed, timestamp for automatic insert/update tagging

    i would always rule out the option with two columns (date and time)

    but there's another option -- integer, with values as unix times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2003
    Location
    Mars
    Posts
    89
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    i would always rule out the option with two columns (date and time)

    but there's another option -- integer, with values as unix times
    which one would be better choice in terms of performance, database size and the easiness of calculations

    Thanks

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    datetime. you will then be able to use the built-in mysql functions to do ate calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.

    my personal preference is to use datetime columns because you can do everything you can do with unix timestamps, plus more without the overhead of converting them first.

  7. #7
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, that's confirmed, timestamp for automatic insert/update tagging
    You learn something everyday huh

  8. #8
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    you should use datetime if you want to store a date and time for something. use time if you only want to store a time. use date if you only want to store a date.
    Stored information is not the only requirement, storage requirements should also be considered. For example DATETIME is 8 bytes when TIMESTAMP is only 4 bytes.

  9. #9
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    datetime. you will then be able to use the built-in mysql functions to do ate calculations. but if the only think you ever need to do with the columns is compare using =, > or < then unix timestamps are ok.
    I don't do much temporal calculations but what functions are supported by DATETIME and not by TIMESTAMP ? I couldn't find anything about that limitation on the Date and Time Functions manual page.

  10. #10
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Location
    Lyon, France
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bmbsa
    I want to develop a web application that needs to store dates and times, as well as to doing some calculations such as the differenece between two dates and etc.

    I am not sure about the best design I can make.
    - should I have two different columns: Date and Time
    - should I have one column of type: DateTime
    - should I have one column of type: TimeStamp

    Could you please explain the best choice and why do we have these different types
    I thought you guys might find the MySQL Date and Time Types matrix useful. So here "she" is .

  11. #11
    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)
    timestamp cannot store values before 1970 or after 2037
    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
  •